February 27, 2006 at 6:47 pm
We have a custom written VB Application that worked fine with SQL 2000. Now that we have migrated to SQL 2005 Express, we get the error "SQL Server does not exist or access denied" WHILE the database is open.
The basic code looks like:
OpenDB
OpenRecordset
Loop until EOF
ProcessRecord
CloseRecordset
CloseDB
Pretty simple. At various times we have it processing 0 records, 100 records, 500 records and then giving us the error "SQL Server does not exist or access denied" on a MoveNext(). Has anyone else experienced this issue? The SQL 2005 instance is on a different machine, no firewalls in the way (software or hardware), connection string is:
Provider=SQLOLEDB.1;Network Library=dbmssocn;Initial Catalog=myDatabase;Data Source=192.168.3.108\SQLEXPRESS;UID=myDatabaseUser;PWD=myDatabasePassword;
We have 7 databases on the server and this occurs on two of them. It seems to be recordset size related (in that it occurs on the larger databases), but the error appears at different times in the processing loop.
Protocol is TCP/IP. I have enabled named pipes as well (and restarted SQL 2005), but this has had no effect.
I've checked the error logs on the SQL Server box - nothing apart from the usual "database backed up" and also ...
Server is listening on [ 'any' <ipv4> 1433]...
Server local connection provider is ready to accept connection...
Server named pipe provider is ready to accept connection...
SQL Server is now ready for client connections...
Any ideas?
February 27, 2006 at 8:03 pm
After some further debugging, it appears that there is a 15 second delay between the start of the MoveNext call and the subsequent failure. I don't know whether this is significant or not, but 15 seconds seems like a magic number.
February 27, 2006 at 9:03 pm
Problem solved - this really is a trap for young players.
The server in question had Dell's IT Assistant which installs SQL2000 Desktop version to store it's information. For some (strange) reason at times the client was attempting to re-establish a connection to the server and was connecting to the SQL2000 instance instead of the SQL2005 instance.
The MoveNext was followed by a call to another function that did some SQL to check the status of things - this was where it was falling over. The system decided (for some unknown reason) to re-connect to the server - hence why we had the error popping up. The re-connect ALWAYS occurred around 15-17 seconds into the job - depending on server load we had varying numbers of records being processed (and at times it was still db server bound and had not managed to get any records before this was triggered).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply