SQL Server Q&A

Symptoms
When you use ActiveX Data Objects (ADO) to connect to a SQL Server 7.0 or SQL Server 2000 server that is behind a firewall, consider the following:The firewall must be configured to permit port 1433 incoming (or the port numbers that SQL Server listens to on TCP/IP), and ports 1024 to 65535 outgoing.The connection string must specify the SQL Server address: the IP address, the server DNS name, or a name inside the hosts file.The connection string should specify the Network Library type, in this case “dbmssocn” (without the quotes) for TCP/IP Sockets Net-Library.WARNING: Opening up the ports on the firewall may pose security issues; consult with your System Administrator or Security Administrator to configure the firewall.
Resolution
In the following sample code, servername should be the server DNS name, IP address, or a name inside the hosts file:

Set Conn = CreateObject(“ADODB.Connection”)Set Rs = CreateObject(“ADODB.Recordset”)Set Cmd = Createobject(“ADODB.Command“) Conn.Open “Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=username;Initial Catalog=DBNAME;Data Source=servername;Network Library=dbmssocn”Cmd.ActiveConnection=ConnSQL = “Select * from TABLE”Cmd.CommandText = SQLSet Rs = Cmd.Execute

Symptoms
When the Configuring Server portion of a SQL Server 7.0 installation is in progress, this series of error messages may appear:

{acmsetup.EXE – Application Error
The application failed to initialize properly (0xc0000006).Click on OK to terminate the application.}Followed by:

sqlservr.exe – Unable To Locate DLL
The dynamic link library MSWSTR10.DLL could not be found in the specified path:
d:\MSSQL7\binn;C:\WINNT;C:\WINNT\system32Followed by:

Setup
Setup failed to configure the server.Refer to the server error logs and cnfgsvr.out for more information.If you run just the Mdac_typ.exe executable file that is located in the x86\Other folder of the SQL Server 7.0 CD, you may see:This error message:

acmsetup.EXE – Application Error
The application failed to initialize properly (0xc0000006).Click on OK to terminate the application.An entry similar to this in the Cnfgsvr.out file:

Starting Service…An error occurred while attempting to start the service (1053)SQL Server Configuration failed. An entry similar to this in the Sqlstp.log file:

08:56:46 Path successfully updated. 08:56:46 %SystemRoot%\system32;%SystemRoot%;D:\MSSQL7\BINN 08:56:46 D:\MSSQL7\binn\cnfgsvr.exe-Silent 2 -NoSampleDB 0 -SQLPath”D:\MSSQL7\” -SQLDataPath “E:\MSSQL7\” -SortID 52 -LCIDCompStyle “1033-196609″ -HWND 27001094 -User sa -Pwd 08:58:58 Process Exit Code: (-1)08:59:00 Setup failed to configure the server.Refer to the server error logs and cnfgsvr.out for more information. 08:59:00 Action CleanUpInstall: 08:59:00 C:\TEMP\_ISTMP10.DIR\scm.exe-Silent 1 -Action 4 -Service SQLServerAgent 08:59:00 Process Exit Code: (1060) The specified service does not exist as an installed service. 08:59:00 C:\TEMP\_ISTMP10.DIR\scm.exe-Silent 1 -Action 4 -Service MSSQLServer 08:59:05 Process Exit Code: (0)08:59:05 Installation Failed.
Resolution
An 0xc000006 exception error occurs when the system attempts to retrieve a swapped-out page and fails.

Symptoms
A query that contains a large number of predicates that are AND-ed together may cause a stack overflow exception. Several thousand conditions are typically required for this problem to occur, and you may reach a limit on the maximum batch size of your query tool before encountering this situation. Many query tools limit batches to 128 KB, whereas SQL Server 7.0 now accepts a batch size as large as (65536 * network packet size).
Resolution
During optimization, memory for selectivity calculation was being allocated from the stack. This allocation depends on the number of nodes being AND-ed together and can result in a stack overflow.

Symptoms
If you back up a database with SQL Enterprise Manager (SEM) and you check Verify backup upon completion under the Option tab, SQL Server issues the following commands: BACKUP DATABASE and RESTORE VERIFYONLY. RESTORE VERIFYONLY checks whether the backup set is complete and whether all volumes are readable.
For example, if you back up the Pubs database to a backup device called Test, SQL server runs the following two SQL commands:

BACKUP DATABASE [pubs] TO [test] WITHINIT ,NOUNLOAD ,NAME = N’pubs backup’,NOSKIP ,STATS = 10,NOFORMAT goRESTORE VERIFYONLY FROM [test] WITHFILE = 1 ,NOUNLOAD However, if you schedule a job through the SQL Enterprise Manager to do the same database back up, SQL Server only schedules BACKUP DATABASE without the RESTORE VERIFYONLY option.
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 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
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
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.