SQL Server Q&A

Symptoms
When you use Server Network Utility to disable Named Pipes as a communication protocol, the following Named Pipes-based features are not disabled: Backup … To Pipe …Restore … From Pipe …Console.exeThis problem may also occur if the DISABLENETWORKPROTOCOLS parameter is used by SQL Server Desktop Engine(also known as MSDE 2000) Service Pack 3 (SP3).
Resolution
Service pack informationTo 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 the Microsoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/) How to obtain the latest SQL Server 2000 service pack
Hotfix informationThe English version of this fix has the file attributes (or later file attributes) 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——————————————————————-06-May-200303:162000.80.818.078,400Console.exe08-May-200304:182000.80.818.029,244Dbmslpcn.dll25-Apr-200302:12786,432Distmdl.ldf25-Apr-200302:122,359,296Distmdl.mdf30-Jan-200301:55180Drop_repl_hotfix.sql07-Apr-200319:152000.80.801.01,557,052Dtsui.dll24-Apr-200302:51747,927Instdist.sql03-May-200301:561,581Inst_repl_hotfix.sql08-Feb-200306:402000.80.765.090,692Msgprox.dll01-Apr-200302:071,873Odsole.sql05-Apr-200301:462000.80.800.062,024Odsole70.dll02-Apr-200321:482000.80.796.057,904Osql.exe02-Apr-200323:152000.80.797.0279,104Pfutil80.dll04-Apr-200321:271,083,467Replmerg.sql04-Apr-200321:532000.80.798.0221,768Replprov.dll08-Feb-200306:402000.80.765.0307,784Replrec.dll05-May-200300:051,085,874Replsys.sql08-May-200304:182000.80.818.0492,096Semobj.dll05-May-200300:242000.80.818.0172,032Semobj.rll16-Apr-200322:39115,892Sp3_serv_uni.sql08-May-200304:182000.80.818.04,215,360Sqldmo.dll07-Apr-200317:4425,172Sqldumper.exe19-Mar-200318:202000.80.789.028,672Sqlevn70.rll24-Apr-200305:392000.80.811.0176,696Sqlmap70.dll08-Feb-200306:402000.80.765.057,920Sqlrepss.dll08-May-200304:192000.80.818.07,540,817Sqlservr.exe08-Feb-200306:402000.80.765.045,644Sqlvdi.dll08-May-200304:182000.80.818.029,244Ssmslpcn.dll08-May-200304:182000.80.818.082,492Ssnetlib.dll08-May-200304:182000.80.818.025,148Ssnmpn70.dll08-May-200304:182000.80.818.0158,240Svrnetcn.dll05-May-200300:092000.80.818.076,416Svrnetcn.exe30-Apr-200323:522000.80.816.045,132Ums.dll28-Feb-200301:342000.80.778.098,872Xpweb70.dllNote Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

Symptoms
When you use SQL Server Enterprise Manager to backup and verify the backup of a database, the verification assessment of the backup is not reliable if the backup set is appended to either a previously existing file or device.
Furthermore, if you attempt to establish a Database Maintenance Plan through the Database Maintenance Wizard and you select Verify the Backup to a tape that contains a Microsoft Windows NT file backup that is File number 1, you will see this error message:

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 3143: [Microsoft][ODBC SQL Server Driver][SQL Server]
The data set on device ‘\\.\Tape0′ is not a SQL Server backup set.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore
operation terminating.Also, if you put a SQL Server database backup on the tape first and you then attempt to perform a Windows NT backup, a Dr. Watson error (0xc000005) occurs.
Resolution
Here are a few ways to work around this problem:Run this line of code from Query Analyzer to the backup device or file to identify the last backup set in the file:

RESTORE HEADERONLY Next, run this code:

RESTORE VERIFYONLY FROMDISK = N’c:\BACKUP\pubsbk.jnk’ WITHFILE = X ,NOUNLOAD where X is the maximum file id identified by the Position output column from RESTORE HEADERONLY. If you use the Database Maintenance Wizard, alter the previous RESTORE statement in the Schedule Task.
-or-
Consider selecting the Overwrite Existing Media option to make sure that the backed up file is always the first backup set in the file.
-or-
Consider backing up the SQL Server databases to a tape on which only SQL Server database backups exist.
-or-
Consider scripting the commands and then run the commands from a command prompt or batch file.For example:

DECLARE @DBNameVARCHAR(200),@TargetLocation VARCHAR(1000),@BackupNameVARCHAR(200),@FileNumberVARCHAR(10),@xpcmdStringVARCHAR(2000),@PrintStringVARCHAR(200)SELECT @DBName= ‘[pubs]‘,@TargetLocation = ‘c:\BACKUP\pubsbk.jnk’ ,@BackupName= ‘pubs backup’SELECT @xpcmdString = ‘BACKUP DATABASE ‘+@DBName+’ TO DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)+’ WITHNOINIT ,NOUNLOAD ,NAME = N’ + CHAR(39)+@BackupName+CHAR(39)+’,NOSKIP ,STATS = 10,NOFORMAT ‘EXEC (@xpcmdString)CREATE TABLE #TempBackupResults(BackupName nvarchar(128),BackupDescriptionnvarchar(255),BackupType smallint,ExpirationDate datetime,Compressed tinyint,Position smallint,DeviceType tinyint,UserName nvarchar(128),ServerName nvarchar(128),DatabaseName nvarchar(128),DatabaseVersionint,DatabaseCreationDatedatetime,BackupSize numeric(20,0),FirstLSN numeric(25,0),LastLSN numeric(25,0),CheckpointLSNnumeric(25,0),DatabaseBackupLSNnumeric(25,0),BackupStartDatedatetime,BackupFinishDatedatetime,SortOrder smallint, CodePage smallint,UnicodeLocaleId int,UnicodeComparisonStyle int,CompatibilityLeveltinyint,SoftwareVendorIdint,SoftwareVersionMajorint,SoftwareVersionMinorint,SoftwareVersionBuildint,MachineName nvarchar(128))SELECT @xpcmdString = ‘RESTORE HEADERONLY FROM DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)INSERT#TempBackupResults EXEC (@xpcmdString)SELECT @FileNumber = CONVERT(VARCHAR(20), MAX(Position))FROM #TempBackupResults WHERE BackupName=@BackupNameSELECT @xpcmdString = ‘RESTORE VERIFYONLY FROM DISK = N’ + CHAR(39)+@TargetLocation+CHAR(39)+’ WITHFILE = ‘ + @FileNumberSELECT @PrintString = ‘Verifying Integrity of Backup Set Number ‘ + @FileNumberPRINT @PrintStringEXEC (@xpcmdString)DROP TABLE #TempBackupResults

Symptoms
A SELECT statement may generate an exception that causes the SQL Server instance to terminate unexpectedly if all of the following conditions are true: The SELECT statement retrieves a computed column.The computed column references a user defined function (UDF).The UDF accesses table data.An implicit data type conversion is necessary for one of the UDF parameters. No stack dump or other message related to the exception is written to the SQL Server error log.
Resolution
To 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 the Microsoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How To Obtain the Latest SQL Server 2000 Service PackNOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.
The English version of this fix should have the following file attributes or later:
SQL Server 2000 Service Pack 1
This hotfix build is designed to be applied on a server that is running SQL Server 2000 Service Pack 1:

VersionFile nameDescription————————————————8.00.0469Sqlservr.exeSP1-based hotfix SQL Server 2000 Service Pack 2
This hotfix build is designed to be applied on a server running SQL Server 2000 Service Pack 2:

VersionFile nameDescription————————————————8.00.0552Sqlservr.exeSP2-based hotfix NOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

Symptoms
Executing a SELECT * FROM SYSINDEXES query against a database that has been upgraded from Microsoft SQL Server 7.0 may cause an Access Violation. If an Access Violation occurs, an error message similar to the following displays in the query window:

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Server: Msg 1203, Level 20, State 1, Line 1
Process ID 51 attempting to unlock unowned resource KEY: 7:2:1 (790023da5d09).
Resolution
The maximum length defined for the keys column in the sysindexes system table in SQL Server 2000 is 1088, while in SQL Server 7.0, it is only 816. When a database is upgraded from SQL Server 7.0, the maximum length defined for the keys column is not updated. As a result, when an index whose keys column exceeds 816 bytes is created and the index is subsequently fetched, as in a SELECT * FROM SYSINDEXES query, the prefetch buffer is written past the 816 bytes that have been allocated, which causes the Access Violation.

Symptoms
In Microsoft SQL Server 2000 Service Pack 3 (SP3), when you try to restore a database backup, the restoration may fail, and a retail assertion with error 3624 may occur. You may see an error message that is similar to the following in the SQL Server error log files as a result of the assertion error:

SQL Server Assertion: File: <xdes.cpp>, line=9860
Failed Assertion = ‘IS_OFF_SAFE (XDES_OUTCOME_ONLY, m_status)’
This problem occurs when all the following conditions are true: You take the database backup from a Microsoft SQL Server 7.0 database.While you take the database backup, transactions that involve multiple databases are logged in the transaction logs.You restore the database backup in Microsoft SQL Server 2000.
Resolution
Service pack informationTo resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/) How to obtain the latest SQL Server 2000 service pack
Hotfix informationThe English version of this fix 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————————————————————————–07-Feb-200316:14786,432 bytesDistmdl.ldf07-Feb-200316:142,359,296 bytesDistmdl.mdf29-Jan-200312:25180 bytesDrop_repl_hotfix.sql31-Jan-200311:022000.80.763.01,557,052 bytesDtsui.dll29-Jan-200312:251,402 bytesInst_repl_hotfix.sql07-Feb-200317:102000.80.765.090,692 bytesMsgprox.dll29-Jan-200311:487.0.9466.0344,064 bytesMsvcr70.dll29-Jan-200311:482000.80.722.057,904 bytesOsql.exe29-Jan-200315:48746,470 bytesInstdist.sql07-Feb-200314:231,065,895 bytesReplmerg.sql07-Feb-200317:102000.80.765.0221,768 bytesReplprov.dll07-Feb-200317:102000.80.765.0307,784 bytesReplrec.dll29-Jan-200315:481,084,318 bytesReplsys.sql29-Jan-200311:482000.80.534.0127,548 bytesSqdedev.dll29-Jan-200311:482000.80.194.0180,290 bytesSqlunirl.dll07-Feb-200317:102000.80.765.0176,696 bytesSqlmap70.dll07-Feb-200317:102000.80.765.057,920 bytesSqlrepss.dll07-Feb-200317:112000.80.765.07,528,529 bytesSqlservr.exe07-Feb-200317:102000.80.765.045,644 bytesSqlvdi.dll07-Feb-200317:102000.80.765.082,492 bytesSsnetlib.dllNote Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.
Important This hotfix build is designed for a server that is running Microsoft SQL Server 2000 Service Pack 3.

Symptoms
When a database is backed up to multiple disk files, and one of the files in the backup set is deleted and then used again in the same backup set to back up a database, a restore of the database using this backup set may fail with the following message:

Server: Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 1 on device ‘d:\north2.bak’.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
Microsoft has confirmed this to be a problem in SQL Server7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799?(http://support.microsoft.com/kb/274799/) INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0For more information, contact your primary support provider.