SQL Server Q&A

Symptoms
In Microsoft SQL Server 2005 and in Microsoft SQL Server 2008, the CPU usage and the memory usage increase gradually. Additionally, you find many orphaned session IDs when you run a query against the sys.sysprocesses view. The status of those orphaned session IDs is dormant. The value of the last_batch column for these session IDs is five minutes earlier than the current time.
Note In SQL Server 2005, the maximum retention period of the internal connection pooling is five minutes.
Resolution
Cumulative update information for SQL Server 2005 Service Pack 2 The fix for this issue was first released in Cumulative Update13 for SQL Server 2005 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
967908?(http://support.microsoft.com/kb/967908/LN/) Cumulative update package 13 for SQL Server 2005 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
937137?(http://support.microsoft.com/kb/937137/LN/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was releasedMicrosoft SQL Server 2005hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
Cumulative update information for SQL Server 2005 Service Pack 3 The fix for this issue was also released in Cumulative Update3 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
967909?(http://support.microsoft.com/kb/967909/LN/) Cumulative update package 3 for SQL Server 2005 Service Pack 3Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
960598?(http://support.microsoft.com/kb/960598/LN/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was releasedMicrosoft SQL Server 2005hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
Cumulative update information for SQL Server 2008The fix for this issue was first released in Cumulative Update 5 for SQL Server 2008. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
969531?(http://support.microsoft.com/kb/969531/) Cumulative update package 5 for SQL Server 2008Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
956909?(http://support.microsoft.com/kb/956909/) The SQL Server 2008 builds that were released after SQL Server 2008 was released
Cumulative update information for SQL Server 2008 Service Pack 1The fix for this issue was first released in Cumulative Update 2 for SQL Server 2008 Service Pack 1. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:
970315?(http://support.microsoft.com/kb/970315/) Cumulative update package 2 for SQL Server 2008 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
970365?(http://support.microsoft.com/kb/970365/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released

Symptoms
Consider the following scenario:In Microsoft SQL Server 2008, you use the Microsoft ADO.NET Connection Manager and the Oracle Client data provider to create a new SQL Server 2008 Integration Services (SSIS) package.You add a data flow to the SSIS package.You add an ADO.NET data source to the data flow.You add an SQL command to the package. The SQL command queries a table in an Oracle database.You run the SSIS package.In this scenario, you may receive an error message that resembles the following:

SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “component “name” (1)” failed because error code 0×80004002 occurred, and the error row disposition on “output column “column” (103)” 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.
Resolution
This problem occurs if the following conditions are true:A queried field in the Oracle table has the numeric data type.The precision value is not defined for the numeric data type.

Symptoms
Consider the following scenario.In Microsoft SQL Server 2008, you create a SQL Server Integration Services (SSIS) package.You use the SQL Server configuration type to create a configuration table for the package. In the configuration table, there is an invalid package path in the PackagePath column. For example, a package path does not exist. The package path can be for a connection manager, for an event handler, or for a log provider.In this scenario, when you run the package in SQL Server Business Intelligence Development Studio (BIDS), you receive the following error message:

Error at Package: The connection “Connection Name” is not found. This error is thrown by Connections collection when the specific connection element is not found.
(Microsoft.DataTransformationServices.VsIntegration)
This issue is likely to occur when you use a shared configuration table in multiple packages. In these packages, the configuration filter values in the configuration table are the same for each package. For example, an object, such as a connection manager, does not exist in all packages. Therefore, you experience this issue when you run packages that do not have this object in SQL Server BIDS.
Note This issue does not occur in SQL Server 2005 Integration Services.
Resolution
SQL Server 2008 Service Pack 1Important You must install this fix if you are running SQL Server 2008 Service Pack 1.
The fix for this issue was first released in Cumulative Update5 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
975977?(http://support.microsoft.com/kb/975977/LN/) Cumulative update package 5 for SQL Server 2008 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
970365?(http://support.microsoft.com/kb/970365/LN/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was releasedMicrosoft SQL Server 2008hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Symptoms
Consider the following scenario. On a client computer that is running Windows Vista orWindows Server 2008, you connect to a named instance of Microsoft SQL Server. The named instance is located on a remote server. In this scenario, the connection may fail.
Note This problem occurs when you connect to one of the following versions of SQL Server:Microsoft SQL Server 2000Microsoft SQL Server 2005Microsoft SQL Server 2008If you use Windows Data Access Components (Windows DAC) 6.0 to connect to the named instance, you receive the following error message:

[DBNETLIB]Specified SQL server not found.
[DBNETLIB]ConnectionOpen (Connect()).If you use SQL Native Client to connect to the named instance, you receive the following error message:

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired.This problem occurs when the named instance is a failover cluster instance. Additionally, this problem may occur if the remote server has multiple IP addresses.
Resolution
When you connect to the named instance, the client network library sends a User Datagram Protocol (UDP) request packet to the IP address of the named instance. Then, SQL Server Browser returns a UDP response packet that contains the information about the connection endpoints.
However, in the UDP response packet, the source IP address may not be the IP address to which the UDP request packet was sent. If the named instance is a failover cluster instance, the source IP address is the IP address of the physical computer instead of the virtual IP address of the remote server. If the remote server has multiple IP addresses, the source IP address may be any of the IP addresses that are assigned to the remote server.
In Windows Vista, Windows Firewall does not allow for loose source mapping. Therefore, Windows Firewall drops the UDP response packet.
For more information about loose source mapping, see the “UDP connections” section of the following Microsoft Web site:
http://technet2.microsoft.com/WindowsServer/en/library/3ccb6af5-d960-4a8d-b12b-70692dc47bf41033.mspx?mfr=true(http://technet2.microsoft.com/WindowsServer/en/library/3ccb6af5-d960-4a8d-b12b-70692dc47bf41033.mspx?mfr=true)

Symptoms
You use Microsoft SQL Server 2008 MSOLAP10 provider to connect to a SQL Server 2008 R2 Analysis Services server. However, when you set the value of the SubQueries property to 2 in the connection string, a validation error is returned on the client-side.
For example, you use Microsoft Excel to connect to an Analysis Services data source. You set the value of the SubQueries property to 2 in the connection string, and you receive the following error message:

Initialization of the data source failed.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database.
Resolution
The fix for this issue was first released in Cumulative Update4 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
973602?(http://support.microsoft.com/kb/973602/LN/) Cumulative update package 4 for SQL Server 2008 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
970365?(http://support.microsoft.com/kb/970365/LN/) The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was releasedMicrosoft SQL Server 2008hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Symptoms
When you try to upgrade to Microsoft SQL Server 2008, you may receive the following error message:

The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.After you click OK to continue the upgrade process, the Upgrade Progress page is displayed. The list of the installed features may list several features that have a status of Failed.
When you click OK, you can select to open the Summary log. The following error message is at the top of the Summary log:

Final result: SQL Server upgrade failed. To continue, investigate the reason for the failure, correct the problem, and then repair your installation.If you view the error log that is created, you may find error code 912 and the following error message at the bottom of the error log:

Script level upgrade for database ‘master’ failed because upgrade step ’sqlagent100_msdb_upgrade.sql’ encountered error 515, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Resolution
This issue occurs if you renamed the sa account to avoid login attacks on the sa account. Error code 912 indicates that the Sqlagent100_msdb_upgrade.sql upgrade script did not run. This upgrade script includes hard-coded references to the sa account. If you renamed the sa account, the script fails and prevents you from upgrading to SQL Server 2008.