Avatar of bemara57
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;SERVER=NotTheServer
Provider string: DRIVER={AcuODBC Driver};SERVER=NotTheServer
Location:
Catalog:
Microsoft SQL Server

Avatar of undefined
Last Comment
rboyd56
Avatar of rboyd56
rboyd56

Querying the tables is dependent on what the driver expects:

but you can try select * from swdate...<tablename>

or select * from openquery(swdata, '<text of query>')

Do this in Query Analyzer
Avatar of QPR
QPR
Flag of New Zealand image

you query the linked server by fully referenceing it...

select * from swdata.dbname.owner.tablename
Avatar of bemara57
bemara57

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?
Avatar of QPR
QPR
Flag of New Zealand image

Syntax
sp_addlinkedserver [ @server = ] 'server'
    [ , [ @srvproduct = ] 'product_name' ]
    [ , [ @provider = ] 'provider_name' ]
    [ , [ @datasrc = ] 'data_source' ]
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ]
Avatar of PJan8724
PJan8724

" 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.
ASKER CERTIFIED SOLUTION
Avatar of rboyd56
rboyd56

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo