Can see but cannot query DB2 data via linked server

[Neil]

I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password

I can then see the tables in EM, but when I try to query them in QA I get this

Select * from db2db...customer

Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'

I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server

My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data

any ideas ? I think I need to enter something in the catalog box, but what ?

thanks in advance.

[Ray Miao]

Use openquery instead.


------------
Neil at 6/13/01 5:26:57 AM

I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password

I can then see the tables in EM, but when I try to query them in QA I get this

Select * from db2db...customer

Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'

I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server

My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data

any ideas ? I think I need to enter something in the catalog box, but what ?

thanks in advance.