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

Posted on 2005-03-03
Medium Priority
Last Modified: 2008-01-09
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"
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>"

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?

Question by:willcheck
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 14

Expert Comment

ID: 13456424
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"
LVL 14

Expert Comment

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

Author Comment

ID: 13456570
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...

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Assisted Solution

fozylet earned 500 total points
ID: 13456869
I don't think those ISP's will allow you to connect directly to a DB there. Also, the DSN settings are in the individual server mahines of the ISPs and is not available from your machine! Even if you make those DSN in ur system and map it to the ISP systems, the afore said security issues will prevent your connection from being made.

What you can do...

- Have a asp page in GoDaddy
- Export all data to CSV/XML (or an identical Access table)
- Download this file
- Upload to DiscountASP
- Run another script to upload the data

i.e. assuming u want to move from GoDaddy to DiscountASP
LVL 14

Accepted Solution

huji earned 500 total points
ID: 13457674
I agree with fozylet generally. The overal process shows you can not connect to the mother DB (let's call the databases mother and daughter) from YOUR machine, while you can connect to it from a page on THAT server. If this is true, then check this:
If you can connect from first server (and no other machine) to the mother DB, and you can connect from the same server to the daughter DB too, then run the codes on that server, not "your" machine. I.e. a page on the first server connects to the mother DB, read tables, then connects to the daugher DB, and stores the data.
Hope it helps

Author Comment

ID: 13461801
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?
LVL 14

Expert Comment

ID: 13462734
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question