Freigeben über

Unable to connect to SQL , “Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001”

  • Artikel

Issue

When we try to connect to a SQL instance from SQL server Management studio we get the following error :

Error executing extended stored procedure: Invalid Parameter

Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001)

Environment

Windows : Windows Server 2000

Service Pack :SP4

SQL Version: SQL Server 2005

· When we try connecting to the instance using management studio ( locally /remotely ) we 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)

· We 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 we were not able to expand the databases ,We were able to open a new query window (from management studio) and run select @@version

· We checked to see if we 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

· We tried to view it in regedit and we 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 we are running into this issue we can use process explorer

https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

In the Menu we 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 we 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: https://support.microsoft.com/kb/326591

However, you can increase this number if you have the fix in this KB applied https://support.microsoft.com/kb/326591. After you install this hotfix, follow the instructions in the KB to create the registry keys.

Associated forum link : https://social.msdn.microsoft.com/forums/en-us/sqlgetstarted/thread/193EC2F0-125D-4141-B867-8A3349BB0606

Amrutha Varshini J
Support Engineer ,Microsoft SQL Server Support

 

Reviewed by

Sudarshan Narasimhan & Shamik Ghosh
TL, Microsoft SQL Server Support