March 15, 2010 at 9:06 am
I have SQL/Server 2005 running on a Windows 2003 server. We have a very small shop, with about 20 WinXP PCs connecting to the SQL/Server server using MDAC 2.8 and TCP/IP Sockets. Users run custom written VB6 applications for their business activities. The intermittent error does not occur during normal day to day operations.
One of the tables in the database is what I call the Message Log. This contains various messages inserted by the applications. Things like DB error messages, or the specific SQL command used to update a record, etc. This is just informational for me, so that I can go back and figure out what happened if a user has a question or problem.
From time to time, we'll need to make a lot of updates to one of the tables in the DB. For example, the last time was when we had a price change, so I ran an application that reads the prices from a spreadsheet and updates the price in the item table. This particular update was for about 40,000 records.
For each item price updated, I also inserted a record into the Message Log. This record just shows the update statement for informational purposes.
When I ran this application, about every 3,500 or so item table updates and message log inserts, I received the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
I was running VB6 out of the Designer, so I could trap the error in the debugger when it occurred. When the error occurred, it always occurred on the INSERT statement to the Message Log table. When I then executed the INSERT statement from the debugger, it was always successful, and I could continue running the program, which would run fine for another 3,500 or so items.
After 3 or 4 errors, I got tired of having to watch the program all of the time, so I ran it from the server where SQL/Server 2005 is installed. The program ran fine to completion, without a single instance of this error.
I could not find anything referencing this error in the SQL logs, which I guess makes sense, since the client couldn't find the SQL Server. Also, the Event Viewer on the client did not report any errors.
Any idea what may be causing this intermittent error when many updates are done to the DB? Does it seem to point to DNS, or a network issue? This error has occurred on two different client PCs.
Thanks for the help.
Joel
March 15, 2010 at 9:29 am
Maybe this is an ODBC error. Try to use OLE DB
March 15, 2010 at 9:56 am
it does seem to me to be the WINS\DNS, wher ethe WINS service might not be returning the IP address connected to the name on th network;
i assume your SQL server has a static IP or reservation ont he network, so I'd change the connection string to use the IP of the server instead of the name;
for example:
"data source=MySQLServer;user id=myApplicationLogin;password=NotARealPassword;initial catalog=SandBox;Trusted_Connection=False;Application Name=HaxxorProgram.exe;"
--change to
"data source=192.168.1.100;user id=myApplicationLogin;password=NotARealPassword;initial catalog=SandBox;Trusted_Connection=False;Application Name=HaxxorProgram.exe;"
Lowell
May 6, 2010 at 7:06 am
I had this error occur again during heavy usage, so I changed the client's connection type from TCP to Named Pipes, and haven't seen the problem again.
Any idea why Named Pipes would work while TCP fails with this internmittent error?
Thanks,
Joel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply