?
Solved

Works in Debug, fails in Release

Posted on 2014-04-26
9
Medium Priority
?
775 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 40

Accepted Solution

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

Author Comment

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

Author Comment

by:VikingOnline
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
LVL 12

Expert Comment

by:ktaczala
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
0
 
LVL 12

Expert Comment

by:ktaczala
ID: 40027840
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
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.
0
 

Author Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

764 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