SQL Server Q&A

Symptoms
A standalone computer (without any network protocols installed) fails to connect locally to the Microsoft SQL Server 7.0 Desktop Edition. If you attempt to connect by using either Query Analyzer or SQL Enterprise Manager, the following error message occurs:

Unable to connect to server \\SRVNAME:
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied. Attempting to connect by using ODBCPING causes this error message to occur:

COULD NOT CONNECT TO SQL SERVER
SQLState: 08001 Native Error: 17
Info. Message: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does not exist or access denied.
SQLState: 01000 Native Error: 53
Info. Message: [Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionOpen(Connect()). ISQL can still connect, when you specify the SQL Server machine name. ISQL makes a DB Library connection and uses shared memory.
Resolution
The inability to connect locally, by using the machine name, occurs after Microsoft Data Access Components (MDAC) 2.6 has been applied to the desktop computer and the connection attempts to make the connection by using the Shared Memory protocol. Shared Memory is used when you specify the machine name to connect and no network protocols are installed on the desktop computer.

Symptoms
An ODBC application may fail to connect to SQL Server 6.x with the following error message returned:

[28000][Microsoft][ODBC SQL Server Driver][SQL Server] Login failed (4002)This problem occurs when applications use the Microsoft Data Access Components (MDAC) 2.1 version of the SQL Server driver (3.70.623) to make trusted connections to SQL Server 6.x and an older version (version 6.x or earlier) of the SQL Server client-side Net-Library DLL is the first on the path.
If all client-side Net-Library DLLs are updated to the SQL Server 7.0 version, this problem does not occur. Applications will not experience this problem when connecting to SQL Server 7.0. Finally, if the ODBC application is started from a directory that contains the updated client-side Net-Library DLL, the problem does not occur either.
Resolution
The MDAC 2.1 SQL Server driver is designed to automatically load the correct version of Net-Library from the <Winntroot>\System32 directory if an older version of Net-Library is initially loaded. However, when loading the Net-Library DLL from the System32 directory for the second time, the driver incorrectly assumes it is making a trusted connection to SQL Server 7.0, resulting an invalid login packet sent. This causes the connection to fail and the error to be returned.

Symptoms
The following error message may appear in the SQL Server Log Shipping Monitor View Restore History window:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3154: [Microsoft][ODBC SQL Server Driver][SQL Server]The backup set holds a backup of a database other than the existing ‘pubs_test’ database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormallyThe preceding error message occurs if: You configure multiple databases for log shipping.You back up the transaction logs for the databases to the same folder.The database names only differ by “_tlog” in the name. For example, pubs_test and pubs_test_tlog.
Resolution
The RESTORE job that runs on the secondary server incorrectly parses the transaction log backup file names and attempts to restore transaction logs that belong to a different database. This behavior does not cause any other problems on the primary or the secondary servers.

Symptoms
In Microsoft SQL Server 2008, the syspolicy_purge_history SQL Server Agent job may fail when you run the syspolicy_purge_history job on a clustered instance. You may receive an error message that resembles the following in the history log file for the syspolicy_purge_history job:

Date datetime
Log Job History (syspolicy_purge_history)
Step ID 3
Server SQLVirtualName\instancename
Job Name syspolicy_purge_history
Step Name Erase Phantom System Health Records.
Duration 00:00:33
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: user. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()’.
Correct the script and reschedule the job. The error information returned by PowerShell is: ‘SQL Server PowerShell provider error: Could not connect to ‘SQLVirtualName\instancename’. [Failed to connect to server SQLVirtualName\instancename. --> An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] Failed to connect to server SQLVirtualName\instancename. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) ‘
A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot find path ‘SQLSERVER:\SQLPolicy\SQLVirtualName\instancename’ because it does not exist. ‘ A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘You cannot call a method on a null-valued expression. ‘. Process Exit Code -1. The step failed.
Resolution
This problem may occur if the syspolicy_purge_history job uses the computer node name instead of the virtual server name for the cluster instance.

Symptoms
Consider the following scenario. You install Microsoft SQL Server 2008 Express or Microsoft SQL Server 2008 Express with Advanced Services.You upgrade to Microsoft SQL Server 2008 Developer, to Microsoft SQL Server 2008 Enterprise, or to Microsoft SQL Server 2008 Standard by using the following command-line command:
setup.exe /action=editionupgrade /instancename=instance name /pid=appropriate edition product IDOr, you upgrade to SQL Server 2008 Developer, to SQL Server 2008 Enterprise, or to SQL Server 2008 Standard by using the Setup Graphical User Interface (GUI).You start the Service Control Manager.
Note To do this, click Start, click Run, and then type services.msc.You locate the SQL Server Agent service and then try to start the service.In this scenario, the SQL Server Agent service does not start successfully. Additionally, the following error message is recorded in the event log:

SQLServerAgent could not be started (reason: Error creating a new session)
Resolution
This issue occurs because in SQL Server 2008 Express and in SQL Server 2008 Express with Advanced Services, the SQL Server Agent service is created but not enabled. This condition is by design.

Symptoms
When you use a Sybase Adaptive Server Enterprise OLE DB provider, the Microsoft SQL Server 2008 Integrated Services (SSIS) OLE DB Source adapter may obtain only the first row of a source table. This problem may occureven if the following conditions are true:More rowsare stored in the source table on the Sybase server.The Integrated Services package seems to execute successfully.
Resolution
This problem may occur when the DBPROP_MAXROWS property is used to specify the maximum size of a rowset. In the SQL Server validation phase, the DBPROP_MAXROWS property is setto help performance. This is because the whole dataset is not needed for validation. However, the DBPROP_MAXROWS property may not be supported, or it may cause unintended consequences in SQL Server statement execution for certain versions of Sybase OLE DB providers. The unintended consequences in SQL Server statement execution may cause data corruption even though an Integrated Services package seems to execute successfully. For more information about when this can be a problem, see the “More Information” section.
For more information about the DBPROP_MAXROWSproperty, visit the following Microsoft Web site:
http://technet.microsoft.com/en-us/library/ms131271(SQL.100).aspx(http://technet.microsoft.com/en-us/library/ms131271(SQL.100).aspx)