none
OLE DB provider "MSDASQL" for linked server "Alchemy" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

    Question

  • I am having trouble creating a usable Linked server to our Alchemy Dcument storage product in SQL server (2005). I am using the following statement.

    sp_addlinkedserver

     

    @server = 'Alchemy'

    ,

     

    @srvproduct = 'OpenText'

    ,

     

    @provider = 'MSDASQL'

    ,

     

    @datasrc = 'Alchemy'

     

     

    The Alchemy System DSN that I created using the ODBC Manager works Fine with MS Access (2007) and MS Query. However when I try to use the linked server in a sql statment such as

    Select * from Alchemy..dbo.Procure  which is one of my table names I get the following error message.

    OLE DB provider "MSDASQL" for linked server "Alchemy" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

    OLE DB provider "MSDASQL" for linked server "Alchemy" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

    OLE DB provider "MSDASQL" for linked server "Alchemy" returned message "[Simba][SimbaEngine ODBC Driver][DRM File Library]Unable to connect to the Alchemy server."

    I also tried getting the connect string the MS Query uses and recoded my linked server set-up after first dropping the linked server so that it looked like this.

    sp_addlinkedserver

     

    @server = 'Alchemy'

    ,

     

    @srvproduct = 'OpenText'

    ,

     

    @provider = 'MSDASQL'

    ,

     

    @provstr = 'DRIVER={Alchemy Driver (*.ald)};DBQ=C:\Program Files\Common Files\ODBC\Data Sources;CODEPAGE=1252;'

    However I get the same error message. I also tried the Access version of the connect string which just substitutes 'DSN=Alchemy'  for the Driver keyword.

    What do I need to change or what steps can I use to trouble shoot this problem?

    Tuesday, June 29, 2010 4:20 PM