Results 1 to 11 of 11

Thread: Linked Server 2005 error connecting to Unidata

  1. #1
    Join Date
    Jan 2004
    Posts
    52

    Linked Server 2005 error connecting to Unidata

    Hi -

    I am setting up linked server 2005 to connect to Unidata using ODBC connection. I used Management Studio to create the link. When I test, it connects successfully. However when I try to open the catalog, I get below error:

    The OLE DB provider "SQL Server" for linked server "(null)" reported an error. One or more arguments were reported invalid by the provider.
    Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)


    Below is my linked server connection:

    EXEC master.dbo.sp_addlinkedserver @server = N'CAD', @srvproduct=N'IBM Unidata ODBC data', @provider=N'MSDASQL', @datasrc=N'CAD'

    Any idea please? Do I need to add the provider string? If so, what parms to provide?

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Are you using 64-bit Windows server? ODBC does not work on 64-bit.

  3. #3
    Join Date
    Jan 2004
    Posts
    52
    32-bit Windows O/S

  4. #4
    Join Date
    Jan 2004
    Posts
    52
    Forgot to mention is that same connection setting working with SQL 2000!!!! I think it is a SQL 2005 issue.

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Did you install unidata odbc on sql server? Did you map sql user to linked server?

  6. #6
    Join Date
    Jan 2004
    Posts
    52
    Yes, I have. One question I have, is there a way to update list of providers under linked server in SQL 2005?

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    Don't think so. But how did you map user?

  8. #8
    Join Date
    Jan 2004
    Posts
    52
    I am passing remote account. Attached is a s/n print of linked server setting.

    Just so you know, when I use sp_tables_ex , it lists all my schemas. Also I can query some tables and not others!
    The connections test is successful, BUT I can't expand the catalogs!!!!
    Attached Files Attached Files

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Does CAD have permission on all tables? Do those table have data type not compatible with sql server? By the way, expand catalog doesn't work on some data source. Especially non-ole db providers.

  10. #10
    Join Date
    Jan 2004
    Posts
    52
    Some of the data types from distination (unidata) does not match SQL server.

    How would you fix data type mismatch?

  11. #11
    Join Date
    Sep 2002
    Posts
    5,938
    Try with convert function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •