How to get the data from linked servers using quer

问题:

I have created a linkedserver as ravikiran-vm which is the virtual machine in my desktop.

Now I have a database called kiran which contains an employ table.
To retrieve employ data, I do the following:

select * from ravikiran-vm.kiran.employ

but it shows the error "Incorrect syntax near '-'."

Can anyone help me, please?

Thanks in advance.

Thanks guys with ur support it working fine... Now i hav to schedule this as a new job.when i execute it as normal it shows o/p. but when i cinfigure the same query as sqlserver agent job it gives error and query not executing...Plz help me in this regard

Thanks in advance

回答1:

I think you should change the name of the linked server, as the - char is reserved in SQL.

You could try surrounding the name with brackets, but it becomes boring

Also, you should include the schema name in the query, or double point to use the default one:

so, you can try:

select * from [ravikiran-vm].kiran.dbo.employ
select * from [ravikiran-vm].kiran..employ

Or whatever your schema be.

回答2:

You have to use OPENQUERY:

SELECT * FROM OPENQUERY([ravikiran-vm],'SELECT * FROM KIRAN..EMPLOY')

回答3:

to get data from linked server you use 4 part notation Server.Database.Schema.Table

since you have an invalid character in your name(-) you need to add brackets around the name

select * from [ravikiran-vm].kiran..employ

You probably also don't want all the data returned

回答4:

Usually direct queries should not be used in case of linked server because it heavily use temp database of SQL server. At first step data is retrieved into temp DB then filtering occur. There are many threads about this. It is better to use open OPENQUERY because it passes SQL to the source linked server and then it return filtered results e.g.

SELECT * FROM OPENQUERY(Linked_Server_Name , 'select * from TableName where ID = 500')

回答5:

1- Link the server

EXEC sp_addlinkedserver 'OracleSvr', 
'Oracle 7.3', 
'MSDAORA', 
'ORCLDB'


2-SELECT
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM albert.titles')

3-UPDATE 
UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles WHERE id = 101') 

4-INSERT
INSERT OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles')
VALUES ('NewTitle');

5-DELETE
DELETE OPENQUERY (OracleSvr, 'SELECT name FROM albert.titles WHERE name = ''NewTitle''')

i just copied from here (http://www.sqlservercentral.com/Forums/Topic916320-392-1.aspx)