Solved

Works in Debug, fails in Release

Posted on 2014-04-26
9
566 Views
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()
     .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
0
Comment
Question by:VikingOnline
  • 4
  • 3
  • 2
9 Comments
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
Comment Utility
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.
0
 

Author Comment

by:VikingOnline
Comment Utility
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.
0
 

Author Comment

by:VikingOnline
Comment Utility
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.
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 12

Expert Comment

by:ktaczala
Comment Utility
Open up your SQL server configuration manager and make sure that TCP/IP protocol is enabled and that SQL Browser is Running automatically
0
 
LVL 12

Expert Comment

by:ktaczala
Comment Utility
Also if you're connection to a SQL Server on another PC you must have rights to access that SQL Instance.
0
 

Author Comment

by:VikingOnline
Comment Utility
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.
0
 

Author Comment

by:VikingOnline
Comment Utility
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?
0
 
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
Comment Utility
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now