none
SQL Linked Server using ODBC System DSN

    Question

  • Hi All,

    I'm trying to add a Linked Server using Microsoft OLE DB Provider for ODBC Drivers, but all I've got is the following error message:
    "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "HEDDV1".
    OLE DB provider "MSDASQL" for linked server "HEDDV1" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)"

    The system DSN connection succeeds upon tests, as well as data extractions from either Excel or Access through the same DSN.
    Facts:
    - OS: MS Windows Server 2003 R2 Standard x64 Edition SP2
    - SQL: MS SQL Server Standard Edition (64-bit) 9.00.3042.00
    - ODBC Driver: MERANT 3.60 32-BIT Progress SQL92 v9.1D
    - I've tried all possible Linked Server configurations (leaving both Provider String and Catalog, blank or not, etc), though that should not impact the connection as the dsn provides all information needed.
    - I've informed the security context by providing remote login and password.
    Please note the System DSN Data Source was added to the 32-bit version of ODBC Data Administrator. Even though MS SQL Integration Services was able to access data from the remote server with no problems or difficulties.
    It seems to be an issue specific to SQL Server or MSDASQL. Any ideas?

    Thanks.
    • Edited by FabioAntunes Tuesday, May 5, 2009 7:27 AM missed important detail
    Tuesday, May 5, 2009 7:24 AM

Answers

All replies


  • Could you try to install 64-bit OLEDB provider for ODBC (MSDASQL) on your machine?
    http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
    Tuesday, May 5, 2009 8:17 AM
    Moderator
  • It seems that 64-bit SQL Server cannot connect to a linked server via a 32-bit ODBC driver. Please read the following article which discusses how to connect to a PostgreSQL server on 64-bit SQL Server in detail:
    http://www.postgresonline.com/journal/index.php?/archives/46-Setting-up-PostgreSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.html

    Therefore, what you have to do are:
    - Find a 64-bit ODBC driver for the "Merant Progress database" and connect via 64-bit MSDASQL
    - Find a 64-bit OLEDB provider for "Merant Progress database" and connect directly via its native OleDB provider.

    Thanks,
    Ming.
    WDAC Team, Microsoft.



    Pak-Ming Cheung - MSFT
    Tuesday, May 5, 2009 10:01 AM
    Answerer
  • Hi Ronggang Yu,

    Actualy it's already installed, otherwise I wouldn't be able to "see" the ODBC drivers installed.

    Thanks anyway for you quick reply.
    Tuesday, May 5, 2009 9:45 PM
  • Hi Pak-Ming,

    Thanks for your reply. I think you're right, but now I'm frustrated because as far as I know there's no 64-bit version of Progress ODBC drivers :(
    After reading your answer I've found some cases very similar to this one, where the solution adopted was to run a 32-bit instance of SQL in 32-bit emulation mode on the x64 platform.
    I'll keep trying to find a 64-bit Progress ODBC Driver. If I have any success I'll come back to post it here.

    Thanks
    Tuesday, May 5, 2009 10:23 PM
  • Actually, there are 64-bit ODBC drivers for Progress, but you have to be on OE10 - and I'm sorry but I can't remember if there's a specific version of OE10 you need.  Ask on http://www.PEG.com, that's the best resource for Progress information.

    We're having the same problem.  We have the 32-bit drivers (9.1D) working when you do a test connection from the 32-bit DSN control, but get an error trying to use that DSN.  I've got an email into Progress to see if they have any answers, I hope there is a way to use the 32-bit drivers since we won't be upgrading to OE10 for 2-4 months and need a solution now.

    Hope that helps!  :)

    Rich

    Tuesday, June 9, 2009 12:51 PM
  • Not sure if this helps but we have faced the same problem in our 64-bit datawarehousing environment on SQL Server 2008 and getting data from a Progress 10.1B database.

    We managed to build a workaround in the following manner.

    1) created a separate instance that only does the extraction through a linked server from the Progress database. This is a x86 instance!. This was the only way we managed to get connection.

    2) We installed the Progress OpenEdge 10.1B client on this x64 server.

    3) To configure the system DSN goto to %windir%\wowsys64\odbcad32.exe and configure the Progress DSN with the right portnumber, database, hostname etc.

    4) Double check if your SQL Server instance is really 32-bit. You can do this by selection the properties of the instance in SSMS. If the SQL Server description does not contain (64-bit) behind it, then it's ok.

    5) Now you can create your linked server to the Progress database by selecting the MSDASQL driver (and productname) and type in your System DSN name, do not forget to add the remote login and password in the security tab.

    In our architecture, we transfer this data highly parallel to a new 64-bit SQL Server instance and do further processing.

    I am interested in the 64-bit ODBC drivers though, that will take a away a lot of pain here.

    Ron
    Monday, August 31, 2009 8:36 AM
  • Thank you for this.  I have been going crazy trying to figure this out.  We recently upgraded to SQL 2008 and I can create a System DSN to the Progress Database using the 32 bit driver and creating it through the Windows\syswow64\odbcad32 file, can see the data to map my data transfer, set up a data conversion and dealt with the data type invalidity between unicode and non-unicode,  but it keeps failing with errors because of the ODBC connection drivers.  Unfortunately, we have our Progress database packages with some software from another company.  They keep telling me that they can't get the updated ODBC drivers and won't be upgrading from Progress 9.1d until the summer.  So at least now I can go with a 32 bit version of SQL and go from there.  It's been interesting learning SQL 2008 since we are upgrading from SQL 2000 and this was just adding to my frustration.
    Wednesday, November 24, 2010 5:03 PM
  • We have a similar problem with 64 bit clients.

     

    I use ODBC in .NET, and found that by changing the connection string from something like:

    private string sDSN = "DSN=YourDatabaseName";

    to something like:

    private string sDSN = "DSN=YourDatabaseName;Uid=YourUsername;Pwd=YourPassword";

    It works.

     

     

    Another solution is to manually add the password into the DSN.

    HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBC.INI/YourDatabaseName

     

     


    Thursday, September 29, 2011 8:13 PM
  • Actually, the password set in DSN are always ignored (for SQL drivers), regardless whether it is on 32-bit or 64-bit platform.

    Customers are advised to provide user name / password at runtime via the UID / PWD in SQLDriverConnect, or simply use the API SQLConnect.

     

    Thanks,
    Ming.
    WDAC Team, Microsoft.

     


    Pak-Ming Cheung - MSFT
    Friday, September 30, 2011 12:55 AM
    Answerer