[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist

[Warlocks]

Hi,

We have just migrated an application from SQL Server 2000 to SQL Server 2005 and are recording a load of errors as in the subject.
Doing a search of the web reveals that this error is not descriptive in any sense, however, here are our specific details:

No ASP code changes have been made since coming across from SQL 2000 and, although I can reproduce the error, it isn't limited to a certain set of pages.
On the part where I can reproduce the error is a function call to record some data in a temporary table. As part of the routine that fails, this function is hit anywhere up to 3000 times (and above!) and it doesn't fail on the first hit, but it may fail at a different number of records so for instance, the first time the routine is run, it may record 2461 records and the next time 3184 records created even though the exact same code is being run.

This is beginning to annoy me and I thought maybe number of concurrent connections but this is set to zero (unlimited). Any help would be appreciated.

Cheers,

Woody.

 

[gmmastros]

As a first step, I would modify the connection string so that OLEDB is used instead of ODBC.

You can go to

http://www.connectionstrings.com

to see what a valid connection string would be for a SQL 2005 database that uses OLEDB.

The reason I mention this is because OLEDB is generally faster than ODBC and it sounds like you may be running in to a timeout condition.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

More specifically, go to:

http://www.connectionstrings.com/?carrier=sqlserver2005

Then, scroll down to:
SQL Native Client OLE DB Provider



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Warlocks]

Thanks for the info.

I tried to use as you say the following connection string:

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

but then the message came back that it didn't recognise the provider.
I therefore found a different one for SQLOLEDB and that was recognised.
However, it didn't fix the problem but is now reporting a OpenConnection problem "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

I have also tried enabling MARS but the error still occurs. I don't think I mentioned but the application is written in standard ASP and, whether it matters or not, the computer name has a hyphen in it... (I read somewhere that underscores aren't supported somewhere in SQL Analysis services and wondered whether the HYPHEN was the same!!)

This is fast becoming a major hassle for us as the customer grows increasingly frustrated at all these error messages.

Thanks for any assistance you can further offer...

Woody.

 

[gmmastros]

On the part where I can reproduce the error is a function call to record some data in a temporary table. As part of the routine that fails, this function is hit anywhere up to 3000 times (and above!) and it doesn't fail on the first hit, but it may fail at a different number of records so for instance, the first time the routine is run, it may record 2461 records and the next time 3184 records created even though the exact same code is being run.

Can you please explain this a little better? I have a some experience with standard ASP and a LOT of experience with SQL Server. If you show a snippet of the ASP code and/or the stored procedure you're using, I may be able to help better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[onpnt]

This is fast becoming a major hassle for us as the customer grows increasingly frustrated at all these error messages.

Didn't you test this before upgrading???

You may want to consider rolling the database back to a 2K instance until you can properly test the upgrade effects and proper configuration changes required on 2005 for your application to function correctly.

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]