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()
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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VikingOnlineAuthor Commented:
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.
VikingOnlineAuthor Commented:
I made a small change. I added the port number (so Data,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.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Jacques Bourgeois (James Burger)PresidentCommented:
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.
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.
VikingOnlineAuthor Commented:
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.
VikingOnlineAuthor Commented:
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?
Jacques Bourgeois (James Burger)PresidentCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.