Works in Debug, fails in Release

Posted on 2014-04-26
Medium Priority
Last Modified: 2014-04-28
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()
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?

Question by:VikingOnline
  • 4
  • 3
  • 2
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 2000 total points
ID: 40024696
First of all, the code is strange. If the connection is already opened, do not close it and reopen it, simply use it as it is. I would replace your 4 lines of code with

If _sqlConnection.State <> ConnectionState.Open Then _sqlConnection.Open()

I am not sure it would solve your problem, but closing and reopening right back might possibly be the culprit. The server as well as the framework have to perform some work when you Close, and maybe Open comes before that has been done.

Why the difference between Release and Debug. As long as the application does exactly the same thing (no #If DEBUG ... #End If blocks), what I have seen most often as the cause of problems that in Release that do not appear in Debug is a question of timing.
The application is a bit slower in Debug than in Release, and that can have an impact is some situations, such as yours where you Close and Open in a very fast sequence.

It's quite easy to diagnose if this is the problem. Simply put a pause between operations with something Thread.Sleep(1000) that pauses for 1 second.

If this is not the problem, then there is another difference between Release and Debug that sometimes cause differences and it's the optimizer. By default, the compiler does not optimize the code in Debug because the source code would not match the compiled code and that would make the debugger almost useless. But the code is optimized in Release so that the application goes faster.

You can also easily check for that by disabling optimization in the Advanced Compiler Settings of the Compile tab of the project's Properties window.

If this solves the problem, then you can leave the option disabled, but it will slow down the application everywhere. Sometimes the difference is not noticeable. If performance is a must however, this is not a good solution. In such a case, all you can do is rewrite the code in another way, hoping that the optimizer won't trigger on it.

Author Comment

ID: 40024830
Thanks for the reply, JamesBurger.

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.

Author Comment

ID: 40025584
I made a small change. I added the port number (so Data Source=xxx.xx.xxx.xxx,1433). I also added Network Library=DBMSSOCN since I noticed the first error message was mentioning provider to be named pipes.

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.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 40
ID: 40025826
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(_sqlConnection) would send the string to the Output window. Things can happen sometimes that changes things in unexpected ways.

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.
LVL 13

Expert Comment

ID: 40027839
Open up your SQL server configuration manager and make sure that TCP/IP protocol is enabled and that SQL Browser is Running automatically
LVL 13

Expert Comment

ID: 40027840
Also if you're connection to a SQL Server on another PC you must have rights to access that SQL Instance.

Author Comment

ID: 40028590
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.

Author Comment

ID: 40028600
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?
LVL 40
ID: 40028607
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question