Works in Debug, fails in Release
Hey guys,
I have a problem that I don't even know how to start finding the solution to.
I have a windows form application written in VB.NET that is connecting to a SQL server. While I've been working on it in Visual Studio in Debug mode it's been working perfectly. When I change to "Release mode" it does not work. And of course, if trying to install it and run outside Visual Studio it also does not work.
The problem is that I can't connect to the SQL server.
The error I get is:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).
Like I said, if I run in Debug mode in Visual Studio I connect without any problems.
So I've been assuming the problem is not on the SQL Server side, since it does accept the incoming remote connection, just as long as I stay in Debug mode.
While trying to eliminate errors (like it does not read the app.config file or something) I've even tried hard coding the connection string so there are no external references that it can't find etc. without any luck.
I import the System.Data.SqlClient
Dim _sqlConnection As New SqlConnection("Data Source=xxx; Initial Catalog=xxx; User ID=xxx; Password=xxx;")
The xxx is replaced by IP address, catalog, username and password.
With _sqlConnection
If .State = ConnectionState.Open Then .Close()
.Open()
End With
Try run that .Open() and it runs for a long time and then gives the above error message.
Where do I even start searching for what is the problem?
Ronny
I have a problem that I don't even know how to start finding the solution to.
I have a windows form application written in VB.NET that is connecting to a SQL server. While I've been working on it in Visual Studio in Debug mode it's been working perfectly. When I change to "Release mode" it does not work. And of course, if trying to install it and run outside Visual Studio it also does not work.
The problem is that I can't connect to the SQL server.
The error I get is:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).
Like I said, if I run in Debug mode in Visual Studio I connect without any problems.
So I've been assuming the problem is not on the SQL Server side, since it does accept the incoming remote connection, just as long as I stay in Debug mode.
While trying to eliminate errors (like it does not read the app.config file or something) I've even tried hard coding the connection string so there are no external references that it can't find etc. without any luck.
I import the System.Data.SqlClient
Dim _sqlConnection As New SqlConnection("Data Source=xxx; Initial Catalog=xxx; User ID=xxx; Password=xxx;")
The xxx is replaced by IP address, catalog, username and password.
With _sqlConnection
If .State = ConnectionState.Open Then .Close()
.Open()
End With
Try run that .Open() and it runs for a long time and then gives the above error message.
Where do I even start searching for what is the problem?
Ronny
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I made a small change. I added the port number (so Data Source=xxx.xx.xxx.xxx,1433
Both with or without the port number specified and with or without specifying DBMSSOCN it works while in Debug mode.
In Release mode the error message changed. With port specified I get this error message
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - An attempt was made to access a socket in a way forbidden by its access permissions.
I have double checked the SQL server settings, and it is allowing incoming connections. I even disabled the firewall for a short period to test it and no change.
I also setup a SQL server on another physical server so that I could try two different servers, but no change.
And at the end of the day, it works while in Debug mode it's only when in release that it does not work.
Both with or without the port number specified and with or without specifying DBMSSOCN it works while in Debug mode.
In Release mode the error message changed. With port specified I get this error message
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - An attempt was made to access a socket in a way forbidden by its access permissions.
I have double checked the SQL server settings, and it is allowing incoming connections. I even disabled the firewall for a short period to test it and no change.
I also setup a SQL server on another physical server so that I could try two different servers, but no change.
And at the end of the day, it works while in Debug mode it's only when in release that it does not work.
Are you absolutely sure that you end up with the same connection string for both configurations?
Do not look at where the string is defined, look at what ends up in the code where you use it. A Debug.WriteLine(_sqlConnec
Trigger a solution wide search for #If DEBUG . You might find what makes the difference between the 2 configurations.
If possible, try to connect with the server name instead of the address.
Do not look at where the string is defined, look at what ends up in the code where you use it. A Debug.WriteLine(_sqlConnec
Trigger a solution wide search for #If DEBUG . You might find what makes the difference between the 2 configurations.
If possible, try to connect with the server name instead of the address.
Open up your SQL server configuration manager and make sure that TCP/IP protocol is enabled and that SQL Browser is Running automatically
Also if you're connection to a SQL Server on another PC you must have rights to access that SQL Instance.
ASKER
I honestly don't know what happened, but it's actually working now.
Like I said in the first post I've been stripping away the code until the connection string is hard coded - to make sure it's not a problem in how the connection string is pulled from settings or something. Then while trying to test and print the connection string used etc. like what JamesBurger said, I put it back into a variable. Still hard coded into the variable. Then suddenly it ran, and I had to even look again to double check that it was actually in "Release" configuration.
I've re-built in the original connection string logic and it's still working.
Like I said in the first post I've been stripping away the code until the connection string is hard coded - to make sure it's not a problem in how the connection string is pulled from settings or something. Then while trying to test and print the connection string used etc. like what JamesBurger said, I put it back into a variable. Still hard coded into the variable. Then suddenly it ran, and I had to even look again to double check that it was actually in "Release" configuration.
I've re-built in the original connection string logic and it's still working.
ASKER
ktaczala, TCP/IP is enabled and I have access rights tot he SQL instance, but SQL Browser is not running.
Could that have meant that the first time when I did not specify the port number that was the reason? And that's what started making me chase this to start with?
Can I use it with specified IP and port number without starting the SQL Browser service? Or do I run a risk I'll have a problem again without the SQL Browser?
Could that have meant that the first time when I did not specify the port number that was the reason? And that's what started making me chase this to start with?
Can I use it with specified IP and port number without starting the SQL Browser service? Or do I run a risk I'll have a problem again without the SQL Browser?
The SQL Browser is only a tool to enable you to see the server in lists such as the one that is used to create a connection string in Visual Studio. It makes things easier if you work in an environment where many servers are available and you might not remember their names. But it is not used for the actual connection. So if your code works with it, it will also work without it.
In fact, if everybody that needs to use the server knows its name, it is best to stop the browser service. This prevent unwanted intrusions, because somebody who would try to enter your system needs to explicitly know the name of the server.
In fact, if everybody that needs to use the server knows its name, it is best to stop the browser service. This prevent unwanted intrusions, because somebody who would try to enter your system needs to explicitly know the name of the server.
ASKER
I understand what you mean about the closing and open again. I've read some tutorials that had the code that way, one even had a long select case thing with closing and opening. So I just did the same, without really seeing the point of it.
I first tried to do the thread.sleep(1000), and it did not change anything.
I then replaced the code with the if <> open, then open like suggested. I could probably just write _sqlConnection.Open() since there is no way it could already be open.
These changes did not help, and it still works in debug but not in release.
So I went into the advanced compiler settings and un-ticked the code optimization.
This also did not change anything, and the connection error is still there when run as release (and still works perfectly in debug).
I tried to double click the .exe in the bin/debug folder. This gives the same connection error, so it has to be run from within Visual Studio to work. Not sure if that makes any difference.