SQL Server Q&A

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
When running the Version Upgrade Wizard to upgrade SQL Server 6.x to SQL Server 7.0, if a database whose name is 30 characters long is excluded from the upgrade, the wizard generates an unhandled exception and stops running.
If Dr. Watson is set as the default debugger, an error message similar to the following will be displayed immediately after clicking the EXCLUDE button:

An application error has occurred
and an application error log is being generated.
upgrade.exe
Exception: privileged instruction (0xc0000065), Address: 0×007f1a80
Resolution
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 7.0 Service Pack 1. For more information, contact your primary support provider.

Symptoms
You cannot restore to a point in time within the first transaction log backup for a database when you use SQL Server Enterprise Manager to perform the RESTORE operation.
In SQL Server 7.0, despite the value entered by the user, the GUI always defaults to the time at the end of the first transaction log.
In SQL Server 2000, the following error message occurs if you enter a point in time within the first transaction log:

The time specified is less than the minimum point in time allowed. Time corrected. After the user clicks OK to close the message box, you find that the end time of the transaction log has replaced the time entered earlier.
The subsequent execution of the RESTORE command restores the database to the end of first transaction log backup.
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 the Microsoft 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 the Microsoft Knowledge Base:
301511?(http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

Symptoms
If the Certificate Server is installed on a Microsoft Windows 2000 computer and the Certificate Authority (CA) name is the same as or starts with the machine host name, an attempt to connect to SQL Server 2000 fails.
The following error message appears when you try to connect from SQL Server Query Analyzer:

Unable to connect to server. Server msg 18, level 16, state 1. [Microsoft] [ODBC SQL Server Driver} [Shared Memory] SSL security error The following error message appears when you try to connect from SQL Enterprise Manager:

Unable to connect to server. Reason: SSL security error. ConnectionOpen (SECDoClientHandShake())… The SQLServerAgent service also fails with the following error message:

Could not start SQLserverAgent Service on local computer. The service did not return an error. This could be an internal Windows error or an internal service error. If this error persists, contact your system administrator. Due to the same connectivity errors indicated previously, an attempt to install Microsoft SQL Server 2000 may also fail.
If you attempt the Microsoft SQL Server installation on a computer on which the Certificate Server and the Certificate Authority(CA) name is the same name as or starts with the machine host name, the SQL Server installation on that computer may fail at the configuration stage. During the configuration stage of the installation process, the setup program makes a connection to SQL Server.
This problem applies to any edition of Microsoft SQL Server, including the Microsoft Desktop Engine (MSDE) installation.
The Microsoft SQL Server installation may fail with the following error message:

Setup failed to configure the server. Refer to the server error logs and C:\WINNT\sqlstp.log for more information. If the error message occurs, SQL Server writes the following information in the Cnfgsvr.out file located in the SQL Server Install subfolder:

###############################################################################Starting Service …SQL_Latin1_General_CP1_CI_AS-m -Q -T4022 -T3659Connecting to Server …driver={sql server};server=Instance_Name;UID=<username>;PWD=<strong password>;database=master[Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (SECDoClientHandshake()).driver={sql server};server=ORLANDO;UID=<username>;PWD=<strong password>;database=master[Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (SECDoClientHandshake()).driver={sql server};server=ORLANDO;UID=<username>;PWD=<strong password>;database=master[Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (SECDoClientHandshake()).SQL Server configuration failed.###############################################################################
By default, the Microsoft SQL Server Desktop Engine setup does not generate a Cnfgsvr.out file. The setup program may fail to configure SQL Server and the following error message may appear:

Setup failed to configure the server. Refer to the server error logs and setup error logs for more information. However, note that a configuration failure can occur due to reasons other than the one described in this article.
Resolution
The Certificate Authority (CA) name of the Certificate Server is critical when you have SQL Server 2000 on the same host. When SQL Server 2000 starts and finds a certificate with the same machine name as the subject, SQL Server tries to use it for encryption, even though it is not a valid SQL Server encryption certificate. Therefore, any attempt to connect to that SQL Server computer fails with the error shown in the “Symptoms” section.
The removal of the Certificate Server from the Microsoft Windows 2000 server does not remove the Certification Authority keys. The previously installed keys still affect the computer that is running SQL Server.

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
Consider the following scenario:In Microsoft SQL Server 2005 Integration Services (SSIS), you run the Dtexec.exe utility in one terminal session.In another terminal session, you connect to the server that is running SSIS. In this terminal session, you use Performance Monitor and add some performance counters of the SQLServer:SSIS Pipeline object.In this scenario, you may find that the values of these counters are always 0. Additionally, thefollowing error messages may be logged in the Application event log.

Event Source: Perflib
Event ID: 1010
Description:
The Collect Procedure for the “DTSPipeline” service in DLL “<SQL Install Directory>\90\DTS\Binn\DTSPipelinePerf.dll” generated an exception or returned an invalid status. Performance data returned by counter DLL will not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data. For more information, see Help and Support Center at <http://go.microsoft.com/fwlink/events.asp>.
Data:
0000: 57 00 00 00 00 00 00 00 W…….

Event Source: SysmonLog
Event ID: 2028
Description:
The service was unable to add the counter ‘<Server Name>\SQLServer:SSIS Pipeline\BLOB bytes read’ to the SSIS SCI Monitoring log or alert. This log or alert will continue, but data for that counter will not be collected. The error returned is: The specified object is not found on the system.
Resolution
This problem occurs because SSIS pipeline counters are created in the session thatDtexec.exe first runs. They are visible by that session only. Therefore, you cannot see the values in another session.