?
Solved

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

Posted on 2005-03-03
7
Medium Priority
?
360 Views
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"
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
0
Comment
Question by:willcheck
  • 4
  • 2
7 Comments
 
LVL 14

Expert Comment

by:huji
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"
0
 
LVL 14

Expert Comment

by:huji
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
Huji
0
 

Author Comment

by:willcheck
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...

thanks
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Assisted Solution

by:fozylet
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
0
 
LVL 14

Accepted Solution

by:
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
Huji
0
 

Author Comment

by:willcheck
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?
0
 
LVL 14

Expert Comment

by:huji
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
Huji
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

621 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