How to get the data from linked servers using queries

Question

Hi!

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

Answer 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.

Answer 2

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

Answer 3

You have to use OPENQUERY:

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