asked on

Error when creating multiple adodb.connections: "Data source name not found and no default driver specified"

Dilemma: I just found out that GoDaddy.com won't let me use Enterprise Manager to migrate my existing SQL db over to the new server-- so I need a new method to copy the data.

Uploading CSVs not working for me...

So I was going to write an .asp page to pull the info from the table on one db and insert it all into the same table in the new db -- that should work, right?

So here's my test code so far (note, it only pulls data from one of the connections, but that is where I am stuck.)

Set connDiscountASP = Server.CreateObject("ADODB.Connection")
connDiscountASP .Open "DSN=myconn1;uid=myuid1;pwd=mypwd1"

Set connGoDaddy = Server.CreateObject("ADODB.Connection")
connGoDaddy .Open "DSN=myconn2;uid=myuid2;pwd=mypwd2"

strSQL="SELECT * FROM articles"
i=0
set rs=Server.CreateObject("ADODB.recordset")
rs.Open strSQL,connDiscountASP
while not rs.eof
      sHeadline = rs.Fields("headline")
      response.Write "<tr><td>"&sHeadline&"</td></tr>"
      rs.movenext()
wend

But I get this error:

"Data source name not found and no default driver specified"

Now, if I take out one of the connections above and try it with just one connection -- the code works fine... can I not have 2 concurrent adodb connections?

What am I missing here?

Thanks

huji

Yes you can have two. Although I'm not sure what the cause is, I've got a solution:
Instead of using DSN for your connections, use full connection strings. For example:

Set connDiscountASP = Server.CreateObject("ADODB.Connection")
connDiscountASP.Open "Driver={SQL Server};Server=Server1;Database=pubs;Uid=myuid1;pPwd=mypwd1"

Set connGoDaddy = Server.CreateObject("ADODB.Connection")
connGoDaddy.Open "Driver={SQL Server};Server=Server2;Database=pubs;Uid=myuid1;pPwd=mypwd1"

huji

If you got stock with a DSN-less connetcion string, have a look at http://www.connectionstrings.com/ for help
Wish I can help
Huji

willcheck

ASKER

Still no luck...

I think one of my problems is that I am trying to connect to these external SQL db's through my local machine, and I am not using the correct kind of connection. I am thinking I need to use a connection with an "absolute URL path" to the SQL server, like this type of string:

Set connGoDaddy = Server.CreateObject("ADODB.Connection")
connGoDaddy.Open "Provider=sqloledb;Data Source=whsql-v02.prod.mesa1.secureserver.net;Initial Catalog=DB_1234;User ID=myuserid;Password=mypassword"

But that returns this error:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

If I use this connection:

Set connGoDaddy = Server.CreateObject("ADODB.Connection")
connGoDaddy.Open "DSN=mssql_DB_1234;Uid=myuserid;pwd=mypassword"

I get this error:

"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified."

And if I use this connection:

Set connGoDaddy = Server.CreateObject("ADODB.Connection")
connGoDaddy.Open "Driver={SQL Server};Server=mssql_DB_1234;Database=DB_1234;Uid=myuserid;pwd=mypassword"

I get this error:
"Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied."

So, I'm guessing I need to use that "whsql-v02.prod.mesa1.secureserver.net" string to connect to their db from an offsite machine (like mine)?

...I'm pretty new at this, let me know if you have any more tips...

thanks

SOLUTION

fozylet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

huji

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

willcheck

ASKER

OK... I had a new idea -- is there a way for me to generate an insert script from Enterprise Manager on my machine for my entire table, then cut-and-paste that script into the My Little Admin SQL web interface on godaddy.com in order to transfer my data?

huji

The rules are the same: Any tries to connect to the SQL database from your local machine will fail (based on what you reported above.) The only choice is, the codes which connect to the mother DB are run on a server which has permission to logon the mother SQL server, i.e. your first server. If you can connect to your new DB from the current server, then it doesn't differ if you connect to it with ASP or anything else.
Wish I can help
Huji

Error when creating multiple adodb.connections: &quot;Data source name not found and no default driver specified&quot;

Error when creating multiple adodb.connections: "Data source name not found and no default driver specified"