When I try to connect to a SQL instance from SQL server Management studio I get the following error :
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001)
· I get the same error irrespective of Windows /SQL authentication mode
· The detailed error from management studio was as follows :
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
· Although I was not able to expand the databases ,I Was able to open a new query window (from management studio) and run select @@version
· I checked to see if I had adequate privileges on the registry and found something very peculiar :
· The registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names was grayed out
· Since it was locked nothing else, including Management Studio, could read it
· I tried to view it in regedit and I got the error :Cannot open Instance Names: Error while opening key.
Resolution
Restarting the "WMI" (Windows Management Instrumentation) service fixes this issue.
(Understandably a reboot of the Server and Failover in case of a clustered instance will also fix the issue)
INFORMATION
This issue is very specific to Windows Server 2000
This occurs when the number of handles to SQL becomes exceedingly large . To confirm if I am running into this issue I can use process explorer
In the Menu I need to go to "Find Handle or DLL" and Type in the word "Instance Names" and hit "search"
This would yield the number of handles on our instance. Once you have this you can find out which process owns these handles and troubleshoot further. Based on our experience with such issues I have seen in the past, the WMIPRVSE.exe (Windows Management Instrumentation aka WMI) was one of the services that had a lot of handles open causing this issue to occur. It is possible that in your case it is another service than WMI. To identify this follow steps given above using Process Explorer.
SQL Management Studio requires to query the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names
If for some reason it cannot do this using xp_regread you will see this error message.
When some application/service has too many handles open for a single object, then it is possible that new requests fail. Now handles are windows objects, and there is no hard limit as such but purely dependent on the resources available to the Operation System. But specifically for Win2K, the maximum number of handles for a program is 10,000. (Reference: http://support.microsoft.com/kb/326591
However, you can increase this number if you have the fix in this KB applied http://support.microsoft.com/kb/326591. After you install this hotfix, follow the instructions in the KB to create the registry keys.
No comments:
Post a Comment