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.

Are you using 64-bit Windows server? ODBC does not work on 64-bit.

32-bit Windows O/S

Forgot to mention is that same connection setting working with SQL 2000!!!! I think it is a SQL 2005 issue.

Did you install unidata odbc on sql server? Did you map sql user to linked server?

Yes, I have. One question I have, is there a way to update list of providers under linked server in SQL 2005?

Don't think so. But how did you map user?

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!!!!

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.

Some of the data types from distination (unidata) does not match SQL server.

How would you fix data type mismatch?

Try with convert function.