Faster SQLServer causes application connection to crash.?

[SQLSquirrel]

My users have a VB6 application which calculates premium payments (to be requested via letter) for approximately 1000 life insurance policies.

This app was recompiled for XP/SQL 2005 and runs okay without any modification on our test XP pc (single processor) against our test SQLServer 2005 database server (single processor).

For each policy the application does calculation step which involving a select from the policy tables followed by an insert into the premium due tables (later used for printing letters). During the process the current policy number is displayed along with a progress bar.

When we run this application in the LIVE environment on a dual processor PC against a quad processor SQLServer, it crashes at approximately 38% through the progress bar - over many retries it has consistently crashed at around the same time (38%) and while processing either SELECT statements or INSERT statements and on many different policy numbers.

It seems to be timing out at roughly the same time interval but the error message is bizarrely "SQL Server does not exist or access denied". This seems ridiculous. The SQLServer properties are set to default values on both TEST and LIVE servers.

Later today I'm going to try switching parallelism off while the app runs (I'll try anything) but do you have any ideas too?

 

[SQLSquirrel]

Limiting the process to one rocessor made no difference.

 

[LFCfan]

I take it it's not a permissions issue, for whatever logon the app uses to connect?

Does every single run of the application result in the "does not exist or access denied" error, or just some runs?



~LFCfan

 

[gmmastros]

I've run in to this problem before.

In my app, it would happen when I was looping a lot and hitting the database every time through the loop. Luckily, I fixed most of these problems.

However, I encourage you to try changing your connection string so that you don't use computer name to connect to the server. Use the IP Address instead. This worked for my customers and it might work for you too.

It's worth a try.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSquirrel]

Yes it does crash every time.

I'll try using the IP address instead - it's not a method I like because IP's can change more often than a server name - but then again I'll try anything under the circumstances.

Thanks

 

[gmmastros]

You may also want to take a look at this:

http://support.microsoft.com/kb/945977

It suggests that a certain combination of networks cards (NIC's) and Windows Server 2003 SP2 can cause this problem. It also suggests that upgrading the NIC driver may resolve the problem.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSquirrel]

Sadly the IP address didn't work, and niether did "Netsh int ip set chimney DISABLED" as mentioned as a workaround in the MS article (although my error message didn't quite match the ones in the text I was optomistic that this would be a fix - damn!).

 

[JoeAtWork]

You are talking about INSERT and SELECT statements - but did you ever make a connection in the first place? Your error message suggests not.

Can you connect to your production SQL Server, for instance through the ODBC window, using the same connection parameters as your VB app?

 

[mrdenny]

Change your application so that it doesn't disconnect after each statement. Open the connection, then fire off the commands reusing the open connection. Once all the commands are done close the connection.

Before running each SQL statement check that the connection is open, and reopen it if it has failed.

This will fix the problem, and should also speed up the processing of your application.

Odds are the problem is that SQL Server is running out of TCP ports that can be used to connect to it with because you are opening and closing them so quickly.

You probably see this in production and not test because other users and processes are using production, but not test.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog