Duplicate SQL Server database on same server cannot be accessed

Question

I am using SQL Server Workgroup Edition on Windows Server 2003 R2

My classic ASP pages access my production database using a system DSN. All working here.

Code like this...

<%
dbName= "ProdDB"  
userID = "PublicUser"  
pwd = "PublicUserPW"

Set objConn = Server.createObject("ADODB.Connection")  
objConn.connectionString = "DSN=MySystemDSN"  
objConn.open dbName, userID, pwd  
%>  

For development and testing, I created a copy of ProdDB in Enterprise Manager by

• Backing up ProdDB
• Restoring from the ProdDB backup set to a new database called TestDB

My understanding was that the restored database would contain an exact copy of the data as well as users, roles etc. Comparing both databases in Enterprise Manager seemed to back up this assumption.

So... I assumed I can access the test copy using the same credentials and only change the dbName, like so...

<%
dbName= "TestDB"  
userID = "PublicUser"  
pwd = "PublicUserPW"

Set objConn = Server.createObject("ADODB.Connection")  
objConn.connectionString = "DSN=MySystemDSN"  
objConn.open dbName, userID, pwd  
%>  

However, now my page returns

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I have even tried creating a new System DSN, with a default database pointing at TestDB. Still no joy.

I'm sure I'm doing something simple and silly. Any assistance gratefully received.

Answer 1

The documentation for the open method says it's declared:

connection.Open ConnectionString, UserID, Password, Options

So it looks like you're passing in TestDB as the connection string. I usually call open without specifying any arguments. Grab a connection string from connectionstrings dot com, and:

objConn.ConnectionString = "Data Source=localhost;Initial Catalog=testdb;" & _
    "User Id=myUsername;Password=myPassword;"
objConn.Open