bemara57
asked on
How do I query my linked server records in MS SQL 2000 Server?
I have a linked server set up in Microsoft SQL 2000 Server. When I open up the tables in the Linked Server section under security, I see all my tables, but can't go further than that. How do I perform queries onto the data so I can view the actual data records?
This is the properties set up in the linked server:
Linked server: SWDATA
Provider Name: Microsoft OLE DB Provider for ODBC Driver
Product name: Demo Files
Data source: ODBC;DSN=Demo Files;RemotePort=20222;SER
Provider string: DRIVER={AcuODBC Driver};SERVER=NotTheServe
Location:
Catalog:
This is the properties set up in the linked server:
Linked server: SWDATA
Provider Name: Microsoft OLE DB Provider for ODBC Driver
Product name: Demo Files
Data source: ODBC;DSN=Demo Files;RemotePort=20222;SER
Provider string: DRIVER={AcuODBC Driver};SERVER=NotTheServe
Location:
Catalog:
you query the linked server by fully referenceing it...
select * from swdata.dbname.owner.tablen
select * from swdata.dbname.owner.tablen
ASKER
Trying any of the queries above I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'SWDATA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
How would I execute this with the right syntax?
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'SWDATA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
How would I execute this with the right syntax?
Syntax
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
" Trying any of the queries above I get this error:
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'SWDATA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."
If you are getting the above error, then your linked servers are not set up correctly or the SQL Login that you created for the linked server doesn't have the proper rights. The user will need to have rights on both servers in order for the link between servers to work properly and it should probably be the same user on both server.
As for querying a linked server, QPR has it right. You need to fully qualify the server name in order to query it.
Hope this helps,
Pete J.
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'SWDATA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."
If you are getting the above error, then your linked servers are not set up correctly or the SQL Login that you created for the linked server doesn't have the proper rights. The user will need to have rights on both servers in order for the link between servers to work properly and it should probably be the same user on both server.
As for querying a linked server, QPR has it right. You need to fully qualify the server name in order to query it.
Hope this helps,
Pete J.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
but you can try select * from swdate...<tablename>
or select * from openquery(swdata, '<text of query>')
Do this in Query Analyzer