Sql Server connection [modified]
-
Not sure where to ask this question, so I will start here since I am using C#. I finally got an upgrade to Visual Studio 2005. I converted one of my projects over and cleaned up a few things that VS2005 was complaining about. However, not I can not connect to the SQL server database. If I compile the application with VS2003, the connection works fine. But on VS2005 the connection times out on open. Is there anything different about the SqlConnection object between VS2003 and VS2005? The connection string is a standard: Server=...;User ID=...;Password=...;Database=... (Of course where the ... is filled in with the appropriate data). It seems to work when the database is on the local computer. I just can't connect to a remote computer... -- modified at 13:56 Friday 27th October, 2006
-
Not sure where to ask this question, so I will start here since I am using C#. I finally got an upgrade to Visual Studio 2005. I converted one of my projects over and cleaned up a few things that VS2005 was complaining about. However, not I can not connect to the SQL server database. If I compile the application with VS2003, the connection works fine. But on VS2005 the connection times out on open. Is there anything different about the SqlConnection object between VS2003 and VS2005? The connection string is a standard: Server=...;User ID=...;Password=...;Database=... (Of course where the ... is filled in with the appropriate data). It seems to work when the database is on the local computer. I just can't connect to a remote computer... -- modified at 13:56 Friday 27th October, 2006
What things did you clean up exactly? Did you move the connection string to a different place in the app config and change the code retrieving the connection string?
-
What things did you clean up exactly? Did you move the connection string to a different place in the app config and change the code retrieving the connection string?
Nothing changed around the SQL connection code. Really only 2 things change: 1) had some classes that needed to override Dispose(). I guess they added Dispose() to the base class. 2) Had to change from .Handle to using the safe handle stuff. The odd thing is that the connection string works when the application is run on the machine with the database. It just won't connect over the network. And that is using the exact same connection string with the Server name specified. Right now the connection string is hardcoded. Later on it will be stored in the registry.
-
Nothing changed around the SQL connection code. Really only 2 things change: 1) had some classes that needed to override Dispose(). I guess they added Dispose() to the base class. 2) Had to change from .Handle to using the safe handle stuff. The odd thing is that the connection string works when the application is run on the machine with the database. It just won't connect over the network. And that is using the exact same connection string with the Server name specified. Right now the connection string is hardcoded. Later on it will be stored in the registry.
This just sounds odd. I'm assuming that the 1.1 code works on a remote machine but the 2.0 code does not. Which rules out the possibility that there's a problem with the network setup. But the 2.0 code works on a local SQL server. If it works locally and not remotely, then the first thing to check is that the network and firewall are all set up correctly. Faced with the problems you have, I would try to reduce everything to the least common denominator. Create a small console app that just runs simple code like this:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);
Console.WriteLine("State: {0}", connection.State);
}Then just run it both locally and remotely for 1.1 and 2.0. Then I'd add little pieces from the SQL connection code of your app until I found what broke. I'm not sure that any of this is helping, but good luck.
-
This just sounds odd. I'm assuming that the 1.1 code works on a remote machine but the 2.0 code does not. Which rules out the possibility that there's a problem with the network setup. But the 2.0 code works on a local SQL server. If it works locally and not remotely, then the first thing to check is that the network and firewall are all set up correctly. Faced with the problems you have, I would try to reduce everything to the least common denominator. Create a small console app that just runs simple code like this:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);
Console.WriteLine("State: {0}", connection.State);
}Then just run it both locally and remotely for 1.1 and 2.0. Then I'd add little pieces from the SQL connection code of your app until I found what broke. I'm not sure that any of this is helping, but good luck.
I'm starting to wonder if the Visual Studio 2005 install is bad. This is really weird. I made the small console app you suggested and ran it on the local machine with the database. Here are the results for the 2003 and 2005 version:
C:\>"test sql 2003" ServerVersion: 08.00.0194 State: Open C:\>"test sql 2005" ServerVersion: 08.00.0194 State: OpenThen I ran it on a remote machine and got:C:\>"test sql 2003" ServerVersion: 08.00.0194 State: Open C:\>"test sql 2005" Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owning Object) at System.Data.ProviderBase.DbConn -
I'm starting to wonder if the Visual Studio 2005 install is bad. This is really weird. I made the small console app you suggested and ran it on the local machine with the database. Here are the results for the 2003 and 2005 version:
C:\>"test sql 2003" ServerVersion: 08.00.0194 State: Open C:\>"test sql 2005" ServerVersion: 08.00.0194 State: OpenThen I ran it on a remote machine and got:C:\>"test sql 2003" ServerVersion: 08.00.0194 State: Open C:\>"test sql 2005" Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owning Object) at System.Data.ProviderBase.DbConnWell, just for grins, try using integrated security instead of a sql identity. I doubt it will make a difference. The connection string would look like this:
Server=(local);Database=Northwind;Integrated Security=SSPI
-
I'm starting to wonder if the Visual Studio 2005 install is bad. This is really weird. I made the small console app you suggested and ran it on the local machine with the database. Here are the results for the 2003 and 2005 version:
C:\>"test sql 2003" ServerVersion: 08.00.0194 State: Open C:\>"test sql 2005" ServerVersion: 08.00.0194 State: OpenThen I ran it on a remote machine and got:C:\>"test sql 2003" ServerVersion: 08.00.0194 State: Open C:\>"test sql 2005" Unhandled Exception: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owning Object) at System.Data.ProviderBase.DbConnAfter reading some other forums on this, there are other people complaining about the exact same thing and the typical response is to increase the timeout. You could set it to something obscene like 5 minutes and see if it eventually connects. I suspect it's not the network, so maybe you could try an OLE or ODBC connection to see if anything is working at all.
-
Not sure where to ask this question, so I will start here since I am using C#. I finally got an upgrade to Visual Studio 2005. I converted one of my projects over and cleaned up a few things that VS2005 was complaining about. However, not I can not connect to the SQL server database. If I compile the application with VS2003, the connection works fine. But on VS2005 the connection times out on open. Is there anything different about the SqlConnection object between VS2003 and VS2005? The connection string is a standard: Server=...;User ID=...;Password=...;Database=... (Of course where the ... is filled in with the appropriate data). It seems to work when the database is on the local computer. I just can't connect to a remote computer... -- modified at 13:56 Friday 27th October, 2006
Are you using SQL Server 2005? If so, I believe only the local protocols (named pipe, shared mem) are enabled by default (unlike SQL 2000 where TCP is enabled as well). You may have to enable the desired transport protocol (and possibly unblock it in your firewall) to connect remotely.
-
After reading some other forums on this, there are other people complaining about the exact same thing and the typical response is to increase the timeout. You could set it to something obscene like 5 minutes and see if it eventually connects. I suspect it's not the network, so maybe you could try an OLE or ODBC connection to see if anything is working at all.
That actually worked. Thanks. The odd thing is that it seems to connect in less than 30 seconds, which was the old timeout value. I set the timeout to 600 seconds. After the first connection, it connects immediately. I guess I will need to connect to the database while the program is initializing to hide the long connect time from the user.
-
That actually worked. Thanks. The odd thing is that it seems to connect in less than 30 seconds, which was the old timeout value. I set the timeout to 600 seconds. After the first connection, it connects immediately. I guess I will need to connect to the database while the program is initializing to hide the long connect time from the user.
Weird. Well, I'm glad it works. I guess the good part is that you can open a connection in another thread while you're initializing and .Net will keep a connection pool open for you while your program's still open even if you close the SqlConnection.