Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I am currently writing a script in vbs, that will eventually run via the scheduler.
The script reads in some data, plays with it and then needs to insert some records into a SQL Server database.

I am getting the error: Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Now for the connection string, I am using the same one that is used for a website that is accessing the server:
Conn_SQLConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=LocalServer;Initial Catalog=BatchImport;User Id=AAAA;PASSWORD=ZZZZZ;"

The DataSource is called LocalServer and is a system DSN, in which I was able to test the connection successfully.
Someone had mentioned to me that they didn't like the name LocalServer, even though this is working flawlessly with the website, I did create another DSN with a different name which also successfully connected to the database, but failed w/ the same error from the script.

The code is below and is failing on :  oCONN.Open Conn_SQLConnectionString
  Private Sub DBRealConnect()
    if (ErrNum = 0) then
      gb_IsConnected = false

      Set oCONN = CreateObject("ADODB.Connection")
      oCONN.ConnectionTimeout = 15
      oCONN.CommandTimeout = 30
      oCONN.Open Conn_SQLConnectionString
     
      ErrNum = Err.Number
      ErrStr = "Open: " & Err.Description

      If ErrNum = 0 Then
        gb_IsConnected = True
      Else
        ErrStr = "Database unavailable"
      End If
    end if
  End Sub

not sure if this matters but here is some other info:
OS: Windows 2000 Server (latest packs / patches)
SQL Server: 2000
Any ideas?  All help appreciated.
Jeff
JeffStoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
Why not use Provider=SQLOLEDB;? You are trying to use the ODBC driver, which requires an ODBC DSN to be set up. Just change provider to SQLOLEDB, and Data Source to the name of the SQL Server, and you should be up and running.
0
JeffStoneAuthor Commented:
thanks ...
but no closer same error: Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

The connectionstring now looks like:
Conn_SQLConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;Data Source=LOCAL;Initial Catalog=BenefitsPortal;User id=AAAA;PASSWORD=ZZZZZ;"


0
ShogunWadeCommented:
try this:

Conn_SQLConnectionString = "Provider=SQLOLEDB;Persist Security Info=False;Data Source=(LOCAL);Initial Catalog=BenefitsPortal;User id=AAAA;PASSWORD=ZZZZZ;"


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JeffStoneAuthor Commented:
Thanks ...
It looks like the only diference is () around LOCAL, did it with the same result ...
0
ShogunWadeCommented:
Same error msg ?
0
JeffStoneAuthor Commented:
yup ... same error message
0
CarlWarnerCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JeffStoneAuthor Commented:
Thanks to CarlWarner his link worked ... why is another question ... so this has transformed into this

This works:
oConn.Open "Provider=sqloledb;" & _
           "Data Source=(local);" & _
           "Initial Catalog=qqqqq" & _
           "User ID=aaaa;" & _
           "Password=zzzzz"

but this did not ...
Conn_SQLConnectionString = "Provider=sqloledb;Data Source=(local);Initial Catalog=qqqqq;User Id=aaaa;Password=zzzzz;"
oCONN.Open Conn_SQLConnectionString

Curious and curiousor ....
Thanks to all whom replied.  Very much appreciated.
0
CarlWarnerCommented:
You're welcome.

Is it simply the trailing semi-colon?
0
JeffStoneAuthor Commented:
nice catch, but I just tried to removed it and it still gave the same error ...
so this shall remain a mystery i guess as I have spent far too much time on this.

Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Fonts Typography

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.