How to query linked SQL server database ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hello everyone,

I have two machines which are connected with LAN and i have SQL-2008 on both machine and i have linked both the SQL server by using this stored procedure

SQL

Exec sp_addlinkedserver
@servername = 'CMSSERVER\SQLEXPRESS',
@srvproduct='SQLServer OLEDB Provider',
@provider='SQLOLEDB',
@datasrc='CMS_Syllabus'

now i need to query this server database, from another machine.
I have written this

select * from CMSSERVER\SQLEXPRESS.CMS_Syllabus.dbo.Admin_Details

but the error i am getting is incorrect syntax near '\'

Please tell me am i doing this correctly , or give me some other idea to implement this.

I need to fire query on other linked server from the one which i have, please explain me how to make it..

Really stucked up..

Thanks in advance.
Regards,
Krunal Panchal

Posted 26-Apr-11 23:22pm

krunal25

Updated 2-Oct-19 20:09pm

walterhevedeich

v2

Add a Solution

Comments

LaxmikantYadav 27-Apr-11 5:39am

Try This,

select * from CMSSERVER\\SQLEXPRESS.CMS_Syllabus.dbo.Admin_Details

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2011-04-26T23:28:00

Solution 1

Try this
select * from [CMSSERVER\SQLEXPRESS].CMS_Syllabus.dbo.Admin_Details

Posted 26-Apr-11 23:28pm

RDBurmon

Comments

[no name] 27-Apr-11 11:33am

Hi,
Your thing worked but i am getting error of Named Pipes, enabled named pipes and tcp/ip on both machine ..
please help me out what to do in this case

Regards
Krunal Panchal

walterhevedeich · Answer 2 · 2011-04-26T23:29:00

Solution 2

Maybe using openquery can help.

http://www.techrepublic.com/blog/datacenter/how-do-i-query-foreign-data-using-sql-servers-linked-servers/133[^]

http://www.ehow.com/how_5164820_query-linked-servers.html[^]

Posted 26-Apr-11 23:29pm

walterhevedeich

Bohdan Stupak · Answer 3 · 2015-06-10T03:15:00

Solution 3

You should try OPENQUERY if you're workin with linked servers

SQL

select * from OPENQUERY([CMSSERVER\SQLEXPRESS], 'select * from CMS_Syllabus.dbo.Admin_Details')

Posted 10-Jun-15 3:15am

Bohdan Stupak

Comments

Maciej Los 10-Jun-15 9:24am

Why do you post an answer for such old question?

CHill60 10-Jun-15 10:14am

OpenQuery was suggested over 3 years ago. There was no point in this post

Bohdan Stupak 12-Jun-15 6:50am

I was googling for openquery syntax and found this question. So when I've finished my research I've diceded also to post answer here. Don't forget that questioner is not only person, who is ineterested in finding correct answer. Also there are many people who just google around.

CHill60 12-Jun-15 8:08am

I agree. But you have brought nothing new - OpenQuery was suggested in Solution 2 over 3 years ago. You are duplicating information that was already available on this post. As I said, a pointless exercise, and one that has attracted down votes