have a web site and using SQL Server 2000 as my back end DB. The ASP page connect to SQL Server through ODBC connection using DSN configured using TCP/IP protocol. and after couple of windows update website page was showing network errror when it was trying to get the infomation from the SQL Server.
Below is the error message getting displayed
[Microsoft][ODBC SQL Server Driver][DBMSSOCN] General Network error. Check your documentation.
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][DBMSSOCN]ConnectionOpen(connect())
Connection failed:
SQLState: 08001
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
Connection failed:
SQLState: 01000
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect())
cause: exceeded the default WinsockListenBacklog setting on the computer that is running SQL Server.
A Winsock application accepts connections on a port by calling a Listen() function, which has a backlog parameter specifying the maximum length of the pending-connection queue. The Winsock specification defines the maximum Listen() backlog at five and when it exceeds five, TCP/IP issues a reset.
and due to this we see the below error message
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
Solution:
Start Registry Editor (REGEDT32.EXE) and locate the following subkey:
For a default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
For a named instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\SuperSocketNetLib
select the SuperSocketNetLib for SQL Server 2000. On the Edit menu, click Add Value.
Enter the following:
Value Name: WinsockListenBacklog
Data Type: REG_DWORD
Data: Range is 1 to 0xFFFFFFFF
in my case i had set the value of Data:15, that resolved the issue.
Note: The suggested method of testing with this value is to set the value in increments of five and observe the results until the connection reset stops.
once value is set, Restart SQL Server.
Thursday, May 7, 2009
Accessing SQL Server Databases with PHP
Applies to: SQL Server 2005 and SQL Server 2008 – all editions
http://msdn.microsoft.com/en-us/library/cc793139.aspx
http://msdn.microsoft.com/en-us/library/cc793139.aspx
Troubleshooting Performance Problems in SQL Server 2008
http://msdn.microsoft.com/en-us/library/dd672789.aspx
A new whitepaper covering the topic of performance troubleshooting in SQL Server 2008 has now been released. You can download the paper from the above link.
also u can check the below link
http://blogs.msdn.com/psssql/archive/2009/03/03/troubleshooting-sql-server-2005-2008-performance-counter-collection-problems.aspx
A new whitepaper covering the topic of performance troubleshooting in SQL Server 2008 has now been released. You can download the paper from the above link.
also u can check the below link
http://blogs.msdn.com/psssql/archive/2009/03/03/troubleshooting-sql-server-2005-2008-performance-counter-collection-problems.aspx
Wednesday, May 6, 2009
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Named Pipes Provider
Some of solutions which can help you resolve above issue
**********************************************************************************************************************
This is the solutions perscribed in the MS Knowledge Base: http://support.microsoft.com/kb/914277/en-us
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.
**********************************************************************************************************************
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/sql-server-2005-connectivity-issue-troubleshoot-part-i.aspx
This error message means that the server was not found or not running or can not make Named Pipe connection through the pipe name that client specified. To verify this:
1) From the command line, do "sc query mssqlserver" or "sc query mssql$" to check whether sql instance present. Then open sql server configuration manager -> check the state of the sql service, if it is not running, start it. If server started fail, check event log or server error log, see what happened there.
2) if you are sure the service is running and shared memory/Named Pipe enabled, please try connection if it is local default instance "osql /Snp:\\.\pipe\sql\query"; or try connection "osql /Snp:\\.\pipe\mssql$\sql\query" if it is local named instance. if you still get error 2, then go to step 3).
3) Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:
Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$\sql\query]
Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ].
4) See your connection string, whether you explicitly specify the pipe name and does it match the pipe that server is listening on? Or whether you just specify server name( like ".","(local)", etc), but you specify the wrong pipe name on client side Named Pipe configuration.eg, go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.
Note: For remote connection, you need to verify step 2) and 3).
**********************************************************************************************************************
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/5d9e84c2-1c05-449a-aae5-503e2ad6f9ca/
http://groups.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/773da4fece704a57
Named Pipes was not enabled.
**********************************************************************************************************************
When attempting to connect to a SQL Server 2005 Express database with sqlcmd, such as:
sqlcmd -s\SQLEXPRESS
where '' is the name of the pc/server with SQL Server 2005 Express installed. The following error is generated:
HResult 0x2, Level16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL native Client : Login timeout expired
Cause:
When first installed SQL Server 2005 Express Edition does not allow any connections other than via the Shared Memory protocol on the local machine.
This error can also be caused by firewall settings blocking access to SQL Server.
Possible Remedies:
Enabled Named Pipes and TCP/IP protocols on the database server.
Start ? All Programs ? Microsoft SQL Server 2005 ? SQL Server Configuration Manager
In the left hand pane, expand "SQL Server Configuration Manager (Local) ? SQL Server 2005 Network Configuration"
In the left hand pane, highlight "Protocols for SQLEXPRESS"
In the right hand pane, right click "Named Pipes" and select "Enable"
In the right hand pane, right click "TCP/IP", select "Enable" and then select "Properties"
On the "IP Addresses" tab ensure that "Enabled" is set to "Yes" for each network adapter listed.
Click [OK] to close the TCP/IP Properties dialog.
In the left hand pane select "SQL Server 2005 Services"
Right click "SQL Server (SQL EXPRESS)" and select "Restart"
Whilst it is not required for this process, it can make the task of configuring remote access to SQL Server Express easier if you also start the process "SQL Server Browser". You may need to open the properties and on the "Service" tab change the Start Mode from Disabled to Automatic, before you can start the process.
Add SQL Server 2005 Express as an exception to the windows firewall.
You will need to add SQL Server 2005 Express as an exception to any firewall software that is running locally. The following sequence assumes the Windows XP Firewall:
Start ? Control Panel (classic view) ? Windows Firewall
On the Exceptions tab, click "Add Program..."
Browse to "sqlserver.exe" and click [OK]. This is normally located in the folder "Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
Repeat for "sqlbrowser.exe" if you have set the "SQL Server Browser" service to run. This is normally located in the folder "Program Files\Microsoft SQL Server\90\Shared"
Click [OK] to close the Windows Firewall dialog.
If you still find that you cannot connect, then try opening TCP Port 1666 in the Windows Firewall:
Start ? Control Panel (classic view) ? Windows Firewall
On the Exceptions tab, click "Add Port..."
The "Name" can be anything, but I suggest something like "TCP Port 1666 for SQL Server". For the "Port number" enter 1666, and ensure that TCP is selected. Click [OK]
Click [OK] to close the Windows Firewall dialog.
For those that are interested, the port number 1666 comes from the "TCP Dynamic Ports" displayed on the "IP Addresses" tab of the "TCP/IP Properties" of the TCP/IP Protocol listed by SQL Server Configuration Manager.
If you get this error when trying to connect using Microsoft SQL Server Management Studio then try opening UDP port 1434.
Ensure that the SQL Server 2005 Express server process is running.
Check this by:
Start ? Control Panel (classic view) ? Administrative Tools ? Services
Scroll down and check that "SQL Server (SQLEXPRESS)" has the status of "Started". Start it if it is not already started.
**********************************************************************************************************************
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125210
The following error is displayed during the setup of SQL Server Database Services:
"SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online."
There is a retry and cancel button.
After checking the registry value of ProtocolOrder under \HKEY_LOCAL_MACHINE\SOFTWARE\Microsost\MSSQLServer\Client\SNI9.0 the value is correct, and no changes are required: "sm tcp np" or under SQL Server configuration manager "Shared Manager", "TCP/IP" and "Named Pipes" are all enabled.
Installation FailsCommentsWhen SQL Native Client copies over the legacy registry hive it doesn't enable shared memory. During installation of SQL Server 2005 the SQL Setup program uses the SQL Native Client to connect to the newly installed database instance using shared memory. Since shared memory is not enabled setup fails to connect.
Work Around
When setup encounters this error the error dialog contains a "retry" button.
1) Open Regedit
2) Navigate to \HKEY_LOCAL_MACHINE\SOFTWARE\Microsost\MSSQLServer\Client\SNI9.0
3) Open the key ProtocolOrder, you should see the values tcp and np
4) Add "sm" to the begining to the reg key value. The reg key should look like this after editing:
sm
tcp
np
5) Save the changes to the reg key
6) Go back to SQL setup and click "Retry". Setup should finish installing
**********************************************************************************************************************
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48653
Problem is that named pipes are not enabled by default.
Try this way..
First install the workstation components alone. Then go to sql configuration tool (under configuration tools) and then under sql native client configuration -> Client Protocols, enable all protocols.
**********************************************************************************************************************
http://my.opera.com/yoroshiku/blog/2007/11/06/sql-server-troubleshooting-named-pipes-provider-could-not-open-a-connection
http://www.mydigitallife.info/2007/10/31/error-has-occurred-while-establishing-a-connection-to-sql-server-2005-which-does-not-allow-local-and-remote-connections/
**********************************************************************************************************************
This is the solutions perscribed in the MS Knowledge Base: http://support.microsoft.com/kb/914277/en-us
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.
**********************************************************************************************************************
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/sql-server-2005-connectivity-issue-troubleshoot-part-i.aspx
This error message means that the server was not found or not running or can not make Named Pipe connection through the pipe name that client specified. To verify this:
1) From the command line, do "sc query mssqlserver" or "sc query mssql$
2) if you are sure the service is running and shared memory/Named Pipe enabled, please try connection if it is local default instance "osql /Snp:\\.\pipe\sql\query"; or try connection "osql /Snp:\\.\pipe\mssql$
3) Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:
Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$
Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ].
4) See your connection string, whether you explicitly specify the pipe name and does it match the pipe that server is listening on? Or whether you just specify server name( like ".","(local)", etc), but you specify the wrong pipe name on client side Named Pipe configuration.eg, go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.
Note: For remote connection, you need to verify step 2) and 3).
**********************************************************************************************************************
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/5d9e84c2-1c05-449a-aae5-503e2ad6f9ca/
http://groups.google.com/group/microsoft.public.sqlserver.odbc/browse_thread/thread/773da4fece704a57
Named Pipes was not enabled.
**********************************************************************************************************************
When attempting to connect to a SQL Server 2005 Express database with sqlcmd, such as:
sqlcmd -s
where '
HResult 0x2, Level16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : 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..
Sqlcmd: Error: Microsoft SQL native Client : Login timeout expired
Cause:
When first installed SQL Server 2005 Express Edition does not allow any connections other than via the Shared Memory protocol on the local machine.
This error can also be caused by firewall settings blocking access to SQL Server.
Possible Remedies:
Enabled Named Pipes and TCP/IP protocols on the database server.
Start ? All Programs ? Microsoft SQL Server 2005 ? SQL Server Configuration Manager
In the left hand pane, expand "SQL Server Configuration Manager (Local) ? SQL Server 2005 Network Configuration"
In the left hand pane, highlight "Protocols for SQLEXPRESS"
In the right hand pane, right click "Named Pipes" and select "Enable"
In the right hand pane, right click "TCP/IP", select "Enable" and then select "Properties"
On the "IP Addresses" tab ensure that "Enabled" is set to "Yes" for each network adapter listed.
Click [OK] to close the TCP/IP Properties dialog.
In the left hand pane select "SQL Server 2005 Services"
Right click "SQL Server (SQL EXPRESS)" and select "Restart"
Whilst it is not required for this process, it can make the task of configuring remote access to SQL Server Express easier if you also start the process "SQL Server Browser". You may need to open the properties and on the "Service" tab change the Start Mode from Disabled to Automatic, before you can start the process.
Add SQL Server 2005 Express as an exception to the windows firewall.
You will need to add SQL Server 2005 Express as an exception to any firewall software that is running locally. The following sequence assumes the Windows XP Firewall:
Start ? Control Panel (classic view) ? Windows Firewall
On the Exceptions tab, click "Add Program..."
Browse to "sqlserver.exe" and click [OK]. This is normally located in the folder "Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
Repeat for "sqlbrowser.exe" if you have set the "SQL Server Browser" service to run. This is normally located in the folder "Program Files\Microsoft SQL Server\90\Shared"
Click [OK] to close the Windows Firewall dialog.
If you still find that you cannot connect, then try opening TCP Port 1666 in the Windows Firewall:
Start ? Control Panel (classic view) ? Windows Firewall
On the Exceptions tab, click "Add Port..."
The "Name" can be anything, but I suggest something like "TCP Port 1666 for SQL Server". For the "Port number" enter 1666, and ensure that TCP is selected. Click [OK]
Click [OK] to close the Windows Firewall dialog.
For those that are interested, the port number 1666 comes from the "TCP Dynamic Ports" displayed on the "IP Addresses" tab of the "TCP/IP Properties" of the TCP/IP Protocol listed by SQL Server Configuration Manager.
If you get this error when trying to connect using Microsoft SQL Server Management Studio then try opening UDP port 1434.
Ensure that the SQL Server 2005 Express server process is running.
Check this by:
Start ? Control Panel (classic view) ? Administrative Tools ? Services
Scroll down and check that "SQL Server (SQLEXPRESS)" has the status of "Started". Start it if it is not already started.
**********************************************************************************************************************
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125210
The following error is displayed during the setup of SQL Server Database Services:
"SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online."
There is a retry and cancel button.
After checking the registry value of ProtocolOrder under \HKEY_LOCAL_MACHINE\SOFTWARE\Microsost\MSSQLServer\Client\SNI9.0 the value is correct, and no changes are required: "sm tcp np" or under SQL Server configuration manager "Shared Manager", "TCP/IP" and "Named Pipes" are all enabled.
Installation FailsCommentsWhen SQL Native Client copies over the legacy registry hive it doesn't enable shared memory. During installation of SQL Server 2005 the SQL Setup program uses the SQL Native Client to connect to the newly installed database instance using shared memory. Since shared memory is not enabled setup fails to connect.
Work Around
When setup encounters this error the error dialog contains a "retry" button.
1) Open Regedit
2) Navigate to \HKEY_LOCAL_MACHINE\SOFTWARE\Microsost\MSSQLServer\Client\SNI9.0
3) Open the key ProtocolOrder, you should see the values tcp and np
4) Add "sm" to the begining to the reg key value. The reg key should look like this after editing:
sm
tcp
np
5) Save the changes to the reg key
6) Go back to SQL setup and click "Retry". Setup should finish installing
**********************************************************************************************************************
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48653
Problem is that named pipes are not enabled by default.
Try this way..
First install the workstation components alone. Then go to sql configuration tool (under configuration tools) and then under sql native client configuration -> Client Protocols, enable all protocols.
**********************************************************************************************************************
http://my.opera.com/yoroshiku/blog/2007/11/06/sql-server-troubleshooting-named-pipes-provider-could-not-open-a-connection
http://www.mydigitallife.info/2007/10/31/error-has-occurred-while-establishing-a-connection-to-sql-server-2005-which-does-not-allow-local-and-remote-connections/
Subscribe to:
Posts (Atom)