Query between 2 servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query between 2 servers

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-24 : 09:22:59

Hi,

I have 2 databases in separate servers.. Therefore, I connected to SQL in one of servers and remotely connected within SQL server to the other server.

Then I wrote the following query -

SELECT gprdsql.TblPracDetails.prac_no, [GPFGSCSF01].[GPRC-RCT].dbo.Practice.PracNo AS Expr1
FROM gprdsql.TblPracDetails INNER JOIN
[GPFGSCSF01].[GPRC-RCT].dbo.Practice ON gprdsql.TblPracDetails.prac_no = [GPFGSCSF01].[GPRC-RCT].dbo.Practice.PracNo

Initial warning;

The query cannot be represented graphically in the Diagram and criteria pane.

Selected -Continue

Query change by SQl to;

SELECT gprdsql.TblPracDetails.prac_no, [GPFGSCSF01].[GPRC-RCT].dbo.Practice.PracNo AS Expr1
FROM gprdsql.TblPracDetails INNER JOIN
[GPFGSCSF01].GPRC - RCT.dbo.Practice ON gprdsql.TblPracDetails.prac_no = [GPFGSCSF01].[GPRC-RCT].dbo.Practice.PracNo

Error Message:

Incorrect syntax near '-'.

Any help please...

Many thanks

mbourgon
Starting Member

6 Posts

Posted - 2012-02-24 : 09:55:27

Easy.

".GPRC - RCT."

That field needs to be bracketed - spaces and dashes (among other characters) aren't allowed in names without the brackets.

".[GPRC - RCT]."

I would also suggest you not preface each field with the entire 4-way.
I'm actually surprised it works.

SELECT TblPracDetails.prac_no, Practice.PracNo AS Expr1
FROM gprdsql.TblPracDetails INNER JOIN
[GPFGSCSF01].[GPRC - RCT].dbo.Practice
ON TblPracDetails.prac_no = Practice.PracNo

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-02-24 : 09:57:01

you just remove remote connection name in select list and join condition...
i think no need to mention in these places ................
SELECT gprdsql.TblPracDetails.prac_no, [GPRC-RCT].dbo.Practice.PracNo AS Expr1
FROM gprdsql.TblPracDetails INNER JOIN
[GPFGSCSF01].[GPRC-RCT].dbo.Practice ON gprdsql.TblPracDetails.prac_no = [GPRC-RCT].dbo.Practice.PracNo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:00:48

which editor are you using? I've not seen this behaviour in default editor which is Management studio

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-24 : 10:17:57

Microsoft SQL Server 2005 managament studio..

jayam tried your suggestion and received the follwing error;

Msg 7202, Level 11, State 2, Line 1
Could not find server 'GPFGSCSF01' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Any help please!!!

Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:27:03

quote:
Originally posted by dr223

Microsoft SQL Server 2005 managament studio..

jayam tried your suggestion and received the follwing error;

Msg 7202, Level 11, State 2, Line 1
Could not find server 'GPFGSCSF01' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Any help please!!!

Many thanks

are you sure you've linked server connection set up for'GPFGSCSF01' ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-24 : 10:27:27

Well you cannot just query across servers. Despite what a lot of people think, there is some security in windows. That having been said, the error tells you that one option is to set up a linked server. Here is link to the BOL topic:

http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-24 : 11:38:24

how do I link the servers and insert the select statement?

Did this;


EXEC sp_addlinkedserver
'GPFGSCSF01',
N'SQL Server'
GO

SELECT gprdsql.TblPracDetails.prac_no, [GPRC-RCT].dbo.Practice.PracNo AS Expr1
FROM gprdsql.TblPracDetails INNER JOIN
[GPFGSCSF01].[GPRC-RCT].dbo.Practice ON gprdsql.TblPracDetails.prac_no = [GPRC-RCT].dbo.Practice.PracNo

GO

Received the following error

Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server 'GPFGSCSF01' already exists.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "GPRC-RCT.dbo.Practice.PracNo" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "GPRC-RCT.dbo.Practice.PracNo" could not be bound.

Any help please!!

many thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-24 : 11:47:33

May try a table alias?

SELECT 
    gprdsql.TblPracDetails.prac_no, 
    Practice.PracNo AS Expr1
FROM 
    gprdsql.TblPracDetails 
INNER JOIN
    [GPFGSCSF01].[GPRC-RCT].dbo.Practice AS Practice
    ON gprdsql.TblPracDetails.prac_no = Practice.PracNo

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-02-24 : 12:05:49

Thanks Lamprey .. It worked, what if i have the following query instead how can i create the table alias..


SELECT DISTINCT TOP (100) PERCENT dbo.TblAuto_coll.Prac_no
FROM         dbo.TblAuto_coll INNER JOIN
             [GPFGSCSF01].[GPRD-RCT].dbo.Practice ON dbo.TblAuto_coll.Prac_no = [GPFGSCSF01].[GPRD-RCT].dbo.Practice.PracNo
ORDER BY dbo.TblAuto_coll.Prac_no

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-24 : 12:33:49

Ummmm.... Maybe this link will help:
http://msdn.microsoft.com/en-us/library/ms187455(v=sql.90).aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-25 : 12:06:45

quote:
Originally posted by dr223

Thanks Lamprey .. It worked, what if i have the following query instead how can i create the table alias..

SELECT DISTINCT TOP (100) PERCENT t1.Prac_no
FROM dbo.TblAuto_coll t1 INNER JOIN
[GPFGSCSF01].[GPRD-RCT].dbo.Practice t2 ON t1.Prac_no = t2.PracNo
ORDER BY t1.Prac_no

same as shown above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jayam.cnu
Starting Member

45 Posts

Posted - 2012-02-27 : 02:22:50

Dr223 ... with out linked server you can not get the result . first create linked server then execute this query.
and if it is not possible to create lined server if you know the other server username and pwd u can use openrowset() command.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources