Application disconnects when updating data on 2005 SQL databas

[RobertNitsch]

We are running a scheduled task that will update a database each day. The update can have anywhere between 2000 and 8000 records. When we run this process, it is stopping part way through. This process was initially running on an Informix DB and has been moved to a 2005SQL DB. Since we have moved the database, we have not been able to get this process to work on a regular basis.

When the task errors we are seeing the following information:
Event Log:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Wire Shark
Transmission Control Protocol
Acknowledgment Number: 0x54a217f9 [should be 0x00000000 because ACK flag is not set]
Acknowledgment number: Broken TCP. The acknowledge filed is nonzero while the ACK flag is not set]
SMB Header
NT Status: STATUS_PIPE_DISCONNECTED (0xc00000b0)

It looks to me that the application server that executes the task is sending a request to the database server to disconnect the communication pipe between the two systems.

This process can be run again after removing the data that was successfully updated, sometimes it may take up to 8 times before it completes. The data in the update file is clean and accurate, void of any errors.

SYSTEM INFORMATION:
OS: Windows 2003 Standard 64 bit Server SP2
Database: 2005 SQL
DB Connectors: ODBC
Program: VB-6

 

[gmmastros]

Can you show the connection string you are using in the VB6 app? Change sensitive things like passwords to XXXX.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertNitsch]

Set conn = New ADODB.Connection

conn.ConnectionString = "DSN=xxxxxxxx-xxx;" & _ "DATABASE=xxxxxxxx;uid=xxxxxxxx;pwd=xxxxxxxx;"

sqlServer = 1
conn.Open

We use the same connect statement for our bulk loads and select statements and they work without issues. We have only
been having problems with updates and inserts into the databases.

 

[gmmastros]

As a test, I would recommend that you slightly alter the connection string.

conn.ConnectionString = "Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;"

Since you are using an ODBC data source, I cannot tell what some of the settings look like. But here's my thinking....

If the server is using SERVERNAME, then you are using DNS under the covers to convert a server name to it's IP Address. Instead, you can just put the IP Address in to the connection string so that you bypass DNS problems. Also note the "Data Source" part of the connection string. There is a ",1433" in there. The 1433 is the TCP/IP port that the connection is using. Your SQL Server may not be using this port, especially if you are using SQL Express. To determine the port that SQL Server is using, do this:

On the SQL Server computer.
Click Start -> Programs -> Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager
Expand SQL Server Network Configuration
Click on "Protocols".
Make sure TCP/IP is enabled. If not, enable it.
Right click on TCP/IP, then click Properties.
Click the IP Addresses tab.
Scroll down to "IP All"
Take note of the TCP Dynamic Ports value.

There is a service that normally runs on the server named "SQL Browser". This service converts a SQL Server instance name to it's port. By putting the port number in to the connection string, you bypass potential problems with the SQL Browser service.

Also notice this part of the connection string: "Network Library=DBMSSOCN;" This forces the network protocol to sockets (TCP/IP).

By using this connection string, you will be able to test multiple things, specifically DNS problems, SQL Browser problems, and Named Pipes problems. This is, in my opinion, the most stable way to connect to a SQL Server database.




Switching gears for a moment. There's something else I would like for you to check.

On the server, log in to SQL Server Management Studio and run the following command:

Use [YourDatabaseNameHere]
GO
sp_helpfile

Can you please post back here the size, maxsize, and growth values?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertNitsch]

We tried to replace the server name with the IP address, but we were not able to get that to work. I checked the protocols for the TCP/IP and it is enabled and the Dynamic Port value is empty.

The results of the query you wanted are in the file below

We have not been able to test the re-write of the code you sent. I hope we will have this tested soon.

Thanks again for your help.

 

[gmmastros]

Robert,

You cannot attach files to posts like that. The attachment is really more like a link where the "attachment" must be accessible on the internet. Your Z drive is not accessible.

Since the port value is empty, I'm gonna guess that you originally did not have TCP/IP enabled and that you just enabled it. Unfortunately, you need to restart the SQL Server service after enabling TCP/IP. The easiest way to do this is to open SQL Server Management Studio, right click on the server node in the Object Explorer window, and then click "Restart". You should make sure that there are no connections to the database at the time you restart it because restarting the service will disconnect your users and they may get errors in whatever application they are using.

Since TCP/IP was probably not enabled already, I feel pretty good about the advice I gave you earlier regarding the connection string. So... restart the sql server service, change the connection string the way that I mention above, and then cross your fingers.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertNitsch]

George,

Sorry, I thought the attachment would be uploaded. I have attached what the data that was in that file:

FIELDS RECORD1 RECORD2
Name: giftcard-test giftcard-test_log
Fileid: 1 2
Filename: ...\giftcard-test.mdf ...\giftcard-test_log.ldf
Filegroup: PRIMARY NULL
Size: 9168064 KB 1341184 KB
Maxsize: Unlimited 2147483648 KB
Growth: 1024 KB 10%
Usage: data only log only

As for the TCP/IP port, it was already enabled when I first looked at it.

 

[gmmastros]

I see an issue here.

Your data file (the .mdf) is 9 gigs (which is fine... it is what it is...) The problem I see that that the growth setting is for 1 meg.

My guess on what's happening....

When you add data, the mdf is probably full. So you add some rows, and the SQL determines that it needs to make the database bigger. Since the growth setting is 1 meg, it probably needs to grow several times during the data load process.

I have a couple suggestions...

1. change the auto grow setting to something bigger. In this situation, I would probably recommend something like 100 megs. It can be a little tricky to determine the correct amount. The actual amount you use should equal the amount of file size growth you see within a couple weeks. What I mean is... if your database grows by 100 megs in a week, then you should set your growth setting to 200 megs.

2. Try very hard to not let the database auto grow. Growing a database, at the size of your DB, can be a relatively slow process. As such, you should probably grow the database manually. You'll want to do this during a slow period to minimize the impact on your system.

3. Set up an alert for your database size. Specifically, if you start running low on available space, you can get an alert so that you can grow the database manually. To set up an alert, you can follow the instructions here:

http://www.techrepublic.com/blog/datacenter/monitor-database-file-sizes-with-sql-server-jobs/296

All of the advice I just gave you should help to minimize the problem with your existing process, but it's not really fixing anything either.

I still think the problem is that you are using named pipes instead of TCP/IP for the database connection. Can you check the TCP/IP properties again? There should be a port value in "TCP Dynamic Ports" or "TCP Port". In fact, there has to be. If you have TCP/IP enabled and you cannot connect, then there must be a firewall issue somewhere. It could be an outgoing firewall on the computer you are running your process on, and/or an incoming firewall issue on the SQL Server machine.

Whether you get the TCP/IP issue sorted, you should still follow my recommendation regarding the database size.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertNitsch]

George,

In the TCP/IP Properties - under IP1; IP2; & IPAll
The TCP Dynamic Ports is empty
The TCP Port has 1433

I will see if we can get the program to work with the IP address.
I will also look into the sizing of the DB next, and update this thread.

Thank you,

Robert

 

[gmmastros]

1433 is that default TCP/IP port when you install SQL Server standard (or higher). As such, you shouldn't need to include it in your connection string. You can, you just shouldn't have to.

conn.ConnectionString = "Provider=sqloledb;" _
                        & "Data Source=190.190.200.100,1433;" _
                        & "Network Library=DBMSSOCN;" _
                        & "Initial Catalog=myDataBase;" _
                        & "User ID=myUsername;" _
                        & "Password=myPassword;"

There is a "trick" that I occasionally use to test connectivity. Here's how it works....

On the computer you are running the VB6 application...

Right click on the desktop, then click New -> Text document.
Rename the file to "Test.udl". Make sure you remove the .txt extension.
double click Test.udl. You should see a "Data Link Properties" window.
On the provider tab, select "Microsoft OLEDB Provider for SQL Server"
On the Connection tab, select or enter or server name, user name and password.
On the ALL tab, scroll down to "Network Library" and then double click it.
Type "DBMSSOCN" and click OK.
On the Connection tab, click "Test Connection".

If you get any errors, please let me know what they are.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertNitsch]

George,

The "Microsoft OLEDB Provider for SQL Server" is not on the list. There is a "Microsoft OLEDB Provider for ODBC Drivers"
A few other options are: SQL Native Client & SQL Server Replication OLE DB Provider for DTS.

Robert

 

[gmmastros]

use SQL Native Client.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should have continued.....

Once you get the "Test Connection" to succeed, you can then re-name the file to test.txt. After renaming, you can open the file with notepad. The connection string you need will be in the notepad document. The only part that won't be in the connection string is the password (for security reasons). You can copy/paste this connection string to VB6, add on the password part, and then use it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertNitsch]

George,

I also am not seeing the Network Library.
I have :
Connect Timeout
Data Source
Extended Properties
General Timeout
Initial Catalog
Locale Identifier
Location
Mode
Password
Persist Security Info
User ID

Robert

 

[gmmastros]

For the server (on the connection tab), use the IP Address comma 1433, and then don't worry about the network library.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RobertNitsch]

George,

You were correct the connection string seems to have fixed our issue. We are now using this string:
conn.ConnectionString = "Provider=SQLNCLI.1;Persist Security Info=False;User ID=XX;Password=XXXXXXX;Initial Catalog=giftcard;Data Source=XX.X.X.XX,1433"

Thank you very much for your time!

Robert

 

[gmmastros]

That's great news.

Just remember, you no longer need to configure ODBC, but your app now requires the SQL Server native client. You can download and install it for free.

http://www.microsoft.com/en-us/download/details.aspx?id=11988

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom