Update a table while conencting two different server

Question

Update a table while conencting two different server

arun_anand09

SSC Eights!

Points: 845
More actions
December 9, 2008 at 2:32 am

#198554

Hi Guys,
Hope someone could help me as this is causing a pain:
I have a server where I have restricted access and I need to update a table connecting a different server table. I have tried this which should work:
UPDATE
OPENDATASOURCE('SQLOLEDB', 'Data Source=CSDGWEB;User ID=EPP_Developer;Password=devel0per').EPP_Prod.EPP_Developer.ClarificationPayments
SET
IsReturned = 1
FROM
adhoc_payge.thangela.MissingClariDB AS a
INNER JOIN OPENDATASOURCE('SQLOLEDB', 'Data Source=CSDGWEB;User ID=EPP_Developer;Password=devel0per').EPP_Prod.EPP_Developer.ClarificationPayments AS c
ON a.paymentlot = c.lotnumber
But this is jsut running for hours and in the end gets a registry error. I have also tried:
Update a
FROM OPENROWSET('SQLOLEDB', 'Server=W2K4590;Trusted_Connection=yes;',
'SELECT *
FROM ADHOC_PAYGE.thangela.MissingClariDb
') AS a INNER JOIN EPP_Prod.EPP_Developer.ClarificationPayments AS c ON
ON a.paymentlot = c.lotnumber
AND a.item = c.itemnumber;
Can someone please let me know what i am doing wrong or it is just not possbile?
ANY HELP WOULD BE APPRECIATED!
Thanks in Advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Arun, you seem to be a little unsure of which db (of adhoc_payge and EPP_Prod) is on a remote server. Can you clarify please? Are either of them on a remote server? Which of them is local to the server which is running your code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

arun_anand09 SSC Eights! Points: 845 More actions · Answer 2

Hi,

I am sure that EPP_PROD.ClarificationPayments is on the Remote Server it is just that I was trying either way to crack this.

Okay let me rephrase my question: How do i run an Update query for below Select query which is running file and presenting me rows where I need to, UPDATE isReturned = 1

SELECT

c.MPXN, c.IsReturned, c.PaymentAmount, c.DateLoaded

FROM

thangela.MissingClariDb a INNER JOIN

OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server};SERVER=CSDGWEB;UID=EPP_Developer;PWD=devel0per',

EPP_Prod.EPP_Developer.ClarificationPayments) c ON a.Paymentlot = c.Lotnumber and a.item = c.itemnumber

HOPE someone could help please?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

This should do it, Arun:

UPDATE c SET IsReturned = 1

FROM OPENROWSET('SQLOLEDB', 'DRIVER={SQL Server};SERVER=CSDGWEB;UID=EPP_Developer;PWD=devel0per',

EPP_Prod.EPP_Developer.ClarificationPayments) c

INNER JOIN thangela.MissingClariDb a

ON a.Paymentlot = c.Lotnumber and a.item = c.itemnumber

As a matter of interest, what do you get if you run the following?

SELECT TOP 1 * FROM EPP_Prod.EPP_Developer.ClarificationPayments

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Hi

Woudn't it be more easy when you speak to you administrator to ask you to provide some rights as linked server 🙂