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-04-20 : 09:38:18

Hi,

I have formulated the following query which works fine and links to Server1 and does the computation;

SELECT     TOP (100) PERCENT dbo.TblAuto_coll.Prac_no
FROM         dbo.TblAuto_coll INNER JOIN
                      Server1.[GPRD-RCT].dbo.Practice AS Practice ON dbo.TblAuto_coll.Prac_no = Practice.Practicecode
WHERE     (dbo.TblAuto_coll.col_status <> 'S')
ORDER BY dbo.TblAuto_coll.Prac_no

Ofcourse, I addlinked server as follows:

Use master
Go
EXEC sp_addlinkedserver
'Server1',
N'SQL Server'
GO

Now, I tried for another server same procedure and changed the query to;

SELECT     TOP (100) PERCENT dbo.TblAuto_coll.Prac_no
FROM         dbo.TblAuto_coll INNER JOIN
                      Server2.[GPRD-RCT].dbo.Practice AS Practice ON dbo.TblAuto_coll.Prac_no = Practice.Practicecode
WHERE     (dbo.TblAuto_coll.col_status <> 'S')
ORDER BY dbo.TblAuto_coll.Prac_no

I receive the error;

SQL Execution Error
Error Source: .Net SqlClient Data Provider
Error Message: Incorrect syntax near '-'

Why do I receive the error and the database name - GPRD-RCT exists in Server2 too, with the table Practice.

N/B: when I execute the query the line of code changes from

Server2.[GPRD-RCT].dbo.Practice AS Practice

Server2.GPRD - RCT.dbo.Practice AS Practice

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 14:49:09

where are you trying to run this editor? are you using default editor in ssms?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources