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

Posted on 2004-11-19
Last Modified: 2013-12-03
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
        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.
Question by:JeffStone
    LVL 16

    Expert Comment

    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.

    Author Comment

    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;"

    LVL 18

    Expert Comment

    try this:

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


    Author Comment

    Thanks ...
    It looks like the only diference is () around LOCAL, did it with the same result ...
    LVL 18

    Expert Comment

    Same error msg ?

    Author Comment

    yup ... same error message
    LVL 33

    Accepted Solution


    Author Comment

    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;" & _

    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.
    LVL 33

    Expert Comment

    You're welcome.

    Is it simply the trailing semi-colon?

    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now