SQL Server Q&A

Symptoms
If you use the Transform Data Task (the data pump engine in Data Transformation Services) to import data to a SQL Server database from a text file that has rows with an invalid format, you may observe the following behavior:The Transform Data Task might stop responding (hang).CPU utilization might increase to almost 100 percent.The only way to cancel the task is to end the Mmc.exe process (if the task was run manually) or to end the Dtsrun.exe process (if the task was scheduled) by using the Windows Task Manager.
Resolution
SQL Server 2000To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack
SQL Server 7.0To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
301511?(http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

Symptoms
When you try to back up a Microsoft SQL Sever database on a computer that is running Microsoft Windows Server 2003, you may receive an error message that is similar to the following:

Processed <Number of Pages> pages for database ‘<databaseName>’, file ‘<databaseName>’ on file 1.
100 percent backed up.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 1 pages for database ‘<databaseName>’, file ‘<fileName>’ on file 1. However, the backup device file is created successfully and the backup data is valid.
A similar problem may also occur when try to restore a SQL Server database on a computer that is running Microsoft Windows Server 2003, and you may receive an error message that is similar to the following:

Processed <Number of Pages> pages for database ‘<databaseName>’, file ‘<fileName>’ on file 1.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 1 pages for database ‘<databaseName>’, file ‘<fileName>’ on file 1.
ODBC: Msg 0, Level 16, State 1
Communication link failure Generally, the backup device file is still created. However, the file may not be valid depending on when the failure occurred. Also, when this problem occurs, an error such as the following is typically logged in the SQL Server error log:

2004-05-26 09:44:16.77 backup BACKUP failed to complete the command backup database testdb to disk=’c:\temp\testdb.bak’ with init, stats=10Note This problem may occur when the SQL Server connection uses the Named Pipes Net-Library.
When the Sqlmaint.exe utility runs the RESTORE VERIFYONLY command, you may notice an error message that is similar to the following in the SQL Server error log:

BackupDiskFile::OpenMedia: Backup device ‘C:\MSSQL\db_200401261900.BAK’ failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).Also, you may notice an error message that is similar to the following in the SQL Server maintenance plan output file:

Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device ‘C:\MSSQL\db_200401261900.BAK’. Device error or device off-line. See the SQL Server error log for more details. [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally. End of maintenance plan ‘ABC Maintenance Plan’ on 1/26/2004 7:00:08 PM SQLMAINT.EXE Process Exit Code: 1 (Failed)”
Resolution
A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.
If the hotfix is available for download, there is a “Hotfix download available” section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.
Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or tocreate a separate service request, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support(http://support.microsoft.com/contactus/?ws=support)Note The “Hotfix download available” form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language. The English version of this hotfix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

DateTimeVersionSizeFile name———————————————-09-17-20032000.85.1022.42 28,672 bytes dbnmpntw.dll Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.
Note For a list of all the hotfixes available for MDAC 2.8, click the following article number to view the article in the Microsoft Knowledge Base:
839801?(http://support.microsoft.com/kb/839801/) FIX: Hotfixes are available for MDAC 2.8

Symptoms
This article discusses the Microsoft Application Block for .NET topic: “Data Access Application Block Overview.”
The Data Access Application Block is a Microsoft .NET Framework component that contains optimized data access code that can help you do the following:Call stored procedures. Issue SQL text commands against a SQL Server database. The Data Access Application Block returns the SqlDataReader object, the DataSet object, and the XmlReader object. You can use the Data Access Application Block as a building block in your own .NET Framework-based application to reduce the quantity of custom code that you must create, test, and maintain.
Resolution
“Data Access Application Block Overview”contains the following sections: Introduction
The Data Access Application Block encapsulates performance best practices and resource management best practices for how to gain access to Microsoft SQL Server databases. You can use the Data Access Application Block as a building block in your own .NET Framework-based application. The Data Access Application Block reduces the quantity of custom code that you must create, test, and maintain. The Data Access Application Block helps you to do the following: Call stored procedures or SQL text commands.Specify parameter details.Return the SqlDataReader object, the DataSet object, and the XmlReader object.Use strongly typed datasets.What Does the Data Access Application Block Include?
The Data Access Application Block component includes the Microsoft Visual Basic .NET source code or the Microsoft Visual Basic 2005 source code and the Microsoft Visual C# .NET source code or the Microsoft Visual C# 2005 source code for the Data Access Application Block. It also includes a Quick Start Samples client application in each language that you can use to test common scenarios. The sample can help you to better understand how the Data Access Application Block works. You can also customize the source code to fit your requirements.Downloading and Installing the Data Access Application Block
This section discusses how to download and to install the Data Access Application Block. A Microsoft Windows Installer file that contains the signed Data Access Application Block assembly and the comprehensive documentation is available. The install process creates a Microsoft Application Block for .NET menu on your Programs menu. The Data Access menu that appears on the Microsoft Application Block for .NET menu includes options to start the documentation and to start the Data Access Application Block Visual Studio .NET or Visual Studio 2005 solution.Using the Data Access Application Block
This section discusses how to use the Data Access Application Block to run database commands and to manage parameters.Internal Design
This section discusses the internal design of the Data Access Application Block. The Data Access Application Block includes the full source code and a comprehensive guide to its design. This section describes the main implementation details.

Symptoms
Consider the following scenario: You restore a Microsoft SQL Server 2008 or SQL Server 2005 database from a backup. You receiveerrors during the restore process that prevent you from restoring the database.You successfully restore the database from the same backup by using the CONTINUE_AFTER_ERRORoption. In this scenario, when you run the DBCC CHECKDB command on the computerthat contains the SQL Server database, you receive an error message that resembles the following:

Msg 8967, Level 16, State 216, Server <server name>, Line 2
An internal error occurred in DBCC which prevented further processing. Please contact Customer Support.
DBCC results for ‘<database name>’.
Msg 8921, Level 16, State 1, Server <server name>, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.Additionally, a message that resembles the following may be displayed in the SQL Server error log:

2007-05-26 07:13:49.21 spid58DBCC encountered a page with an LSN greater than the current end of log LSN (<LSN>) for its internal database snapshot. Could not read page (file id:page id), database ‘<database name’ (database ID database id>), LSN = (<LSN>), type = 32, isInSparseFile = 1.Please re-runthis DBCC command.
Resolution
This problem occurs if the DBCC CHECKDB command cannot perform the necessary checks to confirm the consistency of the database.These checks could not be performed for many reasons. For example, this behavior may occur if there are fundamental inconsistencies in the database, such as metadata inconsistencies ordatabase snapshot corruption. More information about the specific cause of this error can be determined by examining the different state that is displayed in the error message.In the scenario that is described in the “Symptoms” section, the state 216 message indicates that the DBCC CHECKDB command read a page from the internal snapshot that has a larger log sequence number (LSN) than the end of log LSN. This behavior might occur if you restore databases by using the CONTINUE_AFTER_ERROR option.