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

It appears your Trusted_Connection parameter is terminating the connection string improperly.

Consider removing the Trusted_Connection altogether, or ensuring that you don't put the NO in quotes.

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

Answer 3

Trusted_Connection="NO";)

It looks like surrounding the value there with double quotes would throw things off.

Instead of:

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

It looks like you should have:

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

Answer 4

As others have said, the quotes are mismatched. But, you shouldn't need the trusted connection bit. You either use UID= and PWD=, or Trusted_Connection=yes. You don't need all attributes at the same time.

This should work fine:

myConn.Open ("Driver=SQLNCLI10; 
              Server=myNetwrkServer\SQLServ2008; 
              Database=myDB; 
              UID=myID;
              PWD=myPWD;")