joining 2 sql servers for querying

Question

joining 2 sql servers for querying

SkyBVI

SSCoach

Points: 15128
More actions
June 16, 2010 at 9:30 am

#143589

Hi,
I want to join 2 tables on 2 different servers,..by applying a join condition...I added that 2nd server as a linked server....
wat code should i write?
Regards
Sushant Kumar
MCTS,MCP

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you would write the join statement as per normal, however to refer to a table on the linked server you would use a Four part naming convention [servername].[database].[owner].

SkyBVI SSCoach Points: 15128 More actions · Answer 2

select dbo.POP10100.PONUMBER,

dbo.POP10100.VENDORID,

dbo.POP10100.VENDNAME AS VENDORNAME,

dbo.POP10100.DOCDATE AS DOCUMENTDATE,

dbo.POP10100.PRMDATE AS PROMISEDDATE,

dbo.POP10100.REMSUBTO AS REMAININGSUBTOTAL,

dbo.POP10100.HOLD,

dbo.POP10100.BUYERID AS BUYER,

dbo.POP30300.POSTEDDT AS GPPOSTEDDATE,

dbo.IV10200.DATERECD AS RECEIPTDATE,

dbo.IV10200.RCPTNMBR AS RECEIVERNUMBER

from dbo.POP10100

JOIN dbo.POP30300

ON

dbo.POP10100.VENDORID = dbo.POP30300.VENDORID

JOIN [xxx-xxx-PRY2].[InventoryData].dbo.[IV10200] PR

ON dbo.POP10100.VENDORID = PR.VENDORID

where dbo.POP10100.VENDORID not in ('TRWH001')

AND dbo.POP10100.DOCDATE between '05/01/2010' AND '05/31/2010'

ORDER BY dbo.POP10100.VENDORID;

I already used 4 name convention

dbo.IV10200 is table on my 2nd server ...and xxx-xxx-pry2 is my 2nd server name

I am getting this error:

Login failed for user 'sa'

Wats the issue???

Regards
Sushant Kumar
MCTS,MCP

steveb. SSC-Forever Points: 46733 More actions · Answer 3

it looks like you dont have the correct permissions for the linked server.

Have a look at the linked server logs to see why the login failed.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 4

for working with linked servers you can use system store procedures to add linked server.use the following

USE master

GO

-- To use named parameters:

EXEC sp_addlinkedserver

@server = 'SF-PC035',

@srvproduct = ' ',

@provider = 'SQLNCLI',

@provstr = 'DRIVER={SQL Server};SERVER=SF-PC035\sqlexpress;userid=sa;password=telemed;'

sp_addlinked server is used for adding a datasource(link) to our datasource.

@server is the SQL server name.you can use either servername like

'Pc005' or tcp/ip like 192.168.3.201

@srvproduct is optional.you can give sourcename or null.

@provider is Datasource provider like oledb,odbc,SQLNLCLI(SQL server native client)

@Provstr is connection like to connect sqlconnection in .NEt.

after sucess fully running the system stored procedure

you want give credentials for accessing the linked server.

this was done by the following system stored procedure.

EXEC sp_addlinkedsrvlogin 'SF-PC035', 'false', NULL, 'sa', 'password'

sp_addlinkedserver is for creating linked server.

sp_addlinkedsrvlogin is for Providing authentication for the linked server.

Then Linked server created sucessfully.

after sucess full creation you can use the four part name convention.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ malleswara.....I already added tht 2nd server as linked server

@steveb-2 I guess ..The 2nd server(PRY2) has different sa password...so where should I put it in the query?? or after crreating a linked server I dont need to put the sa password??

Thanks for replies..

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 6

Hi,

@malleswara, this step did the trick:-

EXEC sp_addlinkedsrvlogin 'SF-PC035', 'false', NULL, 'sa', 'password'

Thanks all,

Sushant

Regards
Sushant Kumar
MCTS,MCP