How to get a value from a table in different server in SQL Server 2005?

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

Hi All,
I want to do a query in which one of the value is from a column which is in a different server. My query is executing in a server and one of the values to be fetched from a table in a different server.Can anyone suggest me some way to do this?

Thanks in advance.

Posted 25-Aug-10 20:25pm

Subramaniam.C

Add a Solution

4 solutions

Solution 1

If it's a completely different server, I'm not sure it's possible. If it's a different DB on the same server, you can refer to it in your SQL. SQL Server 2008 onwards can run .NET code, which may make it possible.

Posted 25-Aug-10 20:31pm

Christian Graus

Comments

AspDotNetDev 26-Aug-10 12:46pm

2005 can run .NET code as well.

Solution 2

Well you can take a look at OPENROWSET

I give you a simple example to fetch records from other server like

SQL

SELECT a.* FROM OPENROWSET('MSDASQL.1', 'DRIVER=SQL Server;SERVER=192.168.2.2;UID=sa;PWD=;DATABASE=CBOS',
'SELECT * FROM MASTER') as a

Try google for more example

Posted 25-Aug-10 20:39pm

Goutam Patra

Comments

Christian Graus 26-Aug-10 2:55am

Wow - cool. I had no idea this existed ( but then, no project i've worked on has been insane enough to split data between two databases )

Goutam Patra 26-Aug-10 3:51am

<has been="" insane="" enough="" to="" split="" data="" between="" two="" databases="">
Thats right. I was just trying to answer the question. I really don’t bother about OP’s DB design

I only use this to import data from Access, Excel files.

Solution 3

Not sure it's the best approach, but...

You can create a web service to allow you to get data from one SQL Server instance.

You can then create a CLR UDF to call that web service from another SQL Server instance.

Kinda cheating, but it should work.

Posted 25-Aug-10 20:51pm

AspDotNetDev

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)

Shabeer Ibrahim · Accepted Answer · 2010-08-25T23:49:00

Solution 5

Your query can be achieve using following steps

1.add link server thro sql server.

XML

sp_addlinkedserver '<Server Alias>', '', 'SQLNCLI', NULL, NULL, 'SERVER=<IP>', NULL
sp_addlinkedsrvlogin '<Server Alias>', 'false', NULL, '<username>', '<password>'

just write the statement

select * from [LINKSERVERALIASNAME].[DATABASENAME].[ObjectName].[TABLENAME]

Posted 25-Aug-10 23:49pm

Shabeer Ibrahim

Comments

Subramaniam.C 26-Aug-10 5:56am

Thank u Shabeer.Its working fine..

AspDotNetDev 26-Aug-10 12:46pm

FYI, I deleted your duplicate answer.

shanawazway 20-Sep-10 2:22am

Hi Shabbir,this Shahnawaz. I read ur answer, but I have not it understood properly.
Can u please explain me how to execute it and (From .Net Visual Studio OR SQL Visual Studio)

Can u explain me below two lines, and from where it should run ?
sp_addlinkedserver '<server alias="">', '', 'SQLNCLI', NULL, NULL, 'SERVER=<ip>', NULL
sp_addlinkedsrvlogin '<server alias="">', 'false', NULL, '<username>', '<password>'