Connecting to a named instance of SQL Server 2008 from classic ASP

Question

My ASP application connects to the network server where SQL Server 2000 is installed with no problem. The old code that works:

myConn.Open ("Driver={SQL Server};
              Server=myNetwrkServer;
              Database=myDB;
              UID=myID;PWD=myPWD;Trusted_Connection=NO;")

An instance of SQL server 2008 was installed on the same network server. The new code doesn't work:

myConn.Open ("Driver={SQL Server Native Client 10.0};
                      Server=myNetwrkServer\SQLServ2008;
                      Database=myDB;
                      UID=myID;PWD=myPWD;Trusted_Connection="NO";)

Please help!

Answer 1

You have mismatching quotes near the end of the line.

Should look like this

myConn.Open ("Driver={SQL Server Native Client 10.0};
                      Server=myNetwrkServer\SQLServ2008;
                      Database=myDB;
                      UID=myID;PWD=myPWD;Trusted_Connection=NO;")

Answer 2

Named instances require the SQL Server Browser Service to be enabled and started. If this service is not started on your myNetwrkServer machine then the connect from network will fail as they will not be able to resolve the instance name to an actual listening port.

A second problem is that you changed the driver to {SQL Server Native Client 10.0}. This requires that you install the SQL Server 2008 native driver on your ASP machine. There is no reason to change the driver, you should leave the driver to the old {SQL Server} and let OleDB resolve the low level connecting driver for you. By specifying not only the native driver, but even the version number, you are dictating the low level connectivity stack and even if it would work, it would break at when you upgrade to SQL Server 2008 R2. Just leave it at the generic {SQL Server} and let the driver manager figure out the details.

And finally, you need to make sure the login/password is defined on the new server.

As a generic rule, 'new code doesn't work' is never a something you should put in a request for help. Always put how it doesn't work. Do you get an exception, or an error? What exception, what message? Trying to help you doesn't have to be a mystery novel.