Posts tagged sql server 2008
Transferring logins and passwords between SQL Server 2005 and SQL Server 2008
Jan 15th
This link is very useful and details how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, and Microsoft SQL Server 2008, on different servers. – Microsoft Knowledge Base: #246133
Microsoft SQL Server, Error: 15401
Jan 15th
Got this error a lot when trying to add NT user accounts on a SQL Server 2008 installation that was connected to a Windows 2008R2 server running Active Directory 2008 at Windows 2000 functional level. Same error message appeared when troubleshooting this from a SQL Server 2005 instance in the same server environment.
You’ll keep getting “Windows NT User or group ‘MyDomain\MyUser’ not found. Check the name again. Microsoft SQL Server, Error: 15401″
Azure sample database setup….
Dec 29th
Notes on setting up the sample database – adventureworks [click here] for Microsoft’s cloud computing sql server called Azure:
- you need an account – get one from sql.azure.com - it takes some time for your access information to arrive by email.
- download the sample database using link above
- extract/unzip to a known location
- make sure you add your home/work/workstation actual external facing ip address to the firewall settings
- wait for 5-10 mins for your ip address to be accepted by the azure online firewall system
- run the script commands below
See commands/reference pictures below:
Server Name: serverx serverx.database.windows.net
Administrator Username: userx
Server Location: South Central US
db name: ankh
cd dw
buildawdwaz.cmd tcp:serverx.database.windows.net userx@serverx passwordx
cd lt
buildawltaz.cmd tcp:serverx.database.windows.net userx@serverx passwordx
Business Intelligence w/SQL 2008
Dec 27th
Currently reading “Delivering Business Intelligence with Microsoft SQL Server 2008
Interesting quotes/definitions:
“Would you tell me please, which way I ought to go from here?” asked Alice.
“That depends a good deal on where you want to get to,” said the Cat.
“I don’t much care where,” said Alice.
“Then, it doesn’t matter which way you go,” said the Cat.
Alice’s Adventures in Wonderland
- Lewis Carroll
Planning ahead is a good idea. It wasn’t raining when Noah built the ark.
- Anonymous
He who has not first laid his foundations may be able with great ability to lay them afterwards, but they will be laid with trouble to the architect and danger to the building.
The Prince
- Niccolò Machiavelli
Business intelligence is the delivery of accurate, useful information to the appropriate decision makers within the necessary timeframe to support effective decision making.
Data mining uses a complex mathematical algorithm to sift through detail data to identify patterns, correlations, and clustering within the data.
Online analytical processing (OLAP) systems enable users to quickly and easily retrieve information from data, usually in a data mart, for analysis. OLAP systems present data using measures, dimensions, hierarchies, and cubes.
Azure SQL….
Dec 24th
Adventure works sample databases are now available for Microsoft’s SQL Azure at codeplex – click here.
collection_set_3_upload job keeps failing on uploading Query Statistics – SQL Server 2008
Oct 13th
Keep getting the error messages below from the logs:
To view the detailed logs in SSMS 2008:
Drill down on
Management -> data collection -> query statistics -> view logs
====== Error Messages Below=====
Event
Data Code
Message
SSIS error. Component name: DFT – Upload collection snapshot, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “ODS – Upload snapshots into active_sessions_and_requests table” (2686) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (2699). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
.
============================
Message
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0″ Hresult: 0×80004005 Description: “Invalid character value for cast specification”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0″ Hresult: 0×80004005 Description: “Invalid character value for cast specification”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0″ Hresult: 0×80004005 Description: “Invalid character value for cast specification”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0″ Hresult: 0×80004005 Description: “Invalid character value for cast specification”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0″ Hresult: 0×80004005 Description: “Invalid character value for cast specification”.
==========
Event OnError
Data Code -1071607780
Message
There was an error with input column “collection_time” (2778) on input “OLE DB Destination Input” (2699). The column status returned was: “Conversion failed because the data value overflowed the specified type.”.
==============
Package Execution ID
Event OnError
Data Code -1071607767
Message
SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “input “OLE DB Destination Input” (2699)” failed because error code 0xC020907A occurred, and the error row disposition on “input “OLE DB Destination Input” (2699)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
================
Package Execution ID
Event OnError
Data Code -1073450974
Message
SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “ODS – Upload snapshots into active_sessions_and_requests table” (2686) failed with error code 0xC0209029 while processing input “OLE DB Destination Input” (2699). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Read the article on this link.
Solution above essentially worked for me, l changed my retries to 4 from 2 and re-ran the job. It ran okay. Still investigating this in depth.
Microsoft Sql Server Administration…
May 24th
Useful commands I’ve found from various sources for Microsoft Sql Server 2005/2008 include:
EXEC sp_configure
GO
–Instance level principals.
SELECT * FROM sys.asymmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.credentials
SELECT * FROM sys.linked_logins
SELECT * FROM sys.remote_logins
SELECT * FROM sys.server_principals
SELECT * FROM sys.server_role_members
SELECT * FROM sys.sql_logins
SELECT * FROM sys.endpoints
GO
–Database level principals.
SELECT * FROM sys.database_principals
SELECT * FROM sys.database_role_members
GO
ALTER LOGIN sa WITH NAME = hiddenSaAccount
GO
–Check your user execution context
SELECT SUSER_SNAME(), USER_NAME()
GO
–View the list of objects in the database
SELECT * FROM sys.objects
GO
–Change user context
EXECUTE AS USER = ‘AnotherUserName’
GO
–Return to your login
REVERT
GO
–explore database files
select * from AdventureWorks.sys.database_files
select * from master.sys.database_files
select * from msdb.sys.database_files
select * from tempdb.sys.database_files
–explore endpoints
select * from sys.endpoints
select * from sys.tcp_endpoints
select * from sys.http_endpoints
select * from sys.database_mirroring_endpoints
select * from sys.service_broker_endpoints
–Security
SELECT * FROM sys.symmetric_keys
GO
SELECT * FROM sys.certificates
GO
Error: Connect to SSIS Service on machine "" failed:
Mar 11th
On a new instalaltion of SQL Server 2008, running on Windows Server 2008, l cannot connect to SSIS from SSMS (Sql server management console) 2008 running on a client workstation. I can connect from the local server end.
I tried adding the users Group to the permissions option on Component Services/MsdtsServer100 as suggested by many sites since this problem is common also in SSIS/Sql Server 2005. This didnt work.
Solution was to add the user group to the Windows Server Distributed COM Users group.
See also: Technet link
Render SSRS 2008 Report from Website to Adobe PDF
Jan 15th
This is a test project to see if l can successfully render to PDF from an asp.net website, using Report Control from a Visual Studio 2008 project.
First l created a simple sample table called MyContact from some files from the AdventureWorks database. Heres the code:
SELECT
[ContactID] ,[Title] ,[FirstName] ,[LastName]
,[EmailAddress] ,[Phone] ,[ModifiedDate]
into MyContacts
FROM [AdventureWorks].[Person].[Contact]
GO
If you wish to create the table directly, heres the code:
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[MyContacts] Script Date: 01/15/2009 22:34:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyContacts](
[ContactID] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] NOT NULL,
[EmailAddress] [nvarchar](50) NULL,
[Phone] [dbo].[Phone] NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
I’ll attach a link to a text file with data for populating this with over 19,000 rows, as well as a link to the entire source code.
Heres a link to the Report Webpage. (should be active soon)
Currently l’m trying to debug the error below:
Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
Parser Error Message: Could not load file or assembly ‘Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.
Source Error:
|
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].