How to Insert/Update into a table on a Linked Server

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

SQL Server 2000 Forums

SQL Server Administration (2000)

How to Insert/Update into a table on a Linked Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-03 : 10:43:39

Neeraja writes "The following code in CAPS gives me an error. Pl. find the error also just below the code in CAPS: The code above the CAPS is to indicate the connections established for the Linked servers.

USE master
GO
EXEC sp_addlinkedserver
@server='Wock', --Name Of The Linked Server
@provider='SQLOLEDB',
@srvproduct='SQL',
@datasrc='Server1' --Name Of the SQL Server on the network
--Permissions
--Here LoginId is sa with no password
EXEC sp_addlinkedsrvlogin 'Wock','False',NULL,'sa','111'

--Selecting Data From Table latestRatios in the database Stocks using linked server Server3

SELECT * FROM OPENQUERY(Wock,'SELECT * FROM pubs..authors where transactionno=1')

delete Wock.pubs.dbo.authors where transactionno=1

declare @TS varbinary(50)
select @TS=max(Timestamp)
from
(
select Timestamp
FROM OPENQUERY(Wock,
'select max(TimeStamp) as TimeStamp from pubs.dbo.Titles')
union
select Timestamp
FROM OPENQUERY(Wock,
'select max(TimeStamp) as TimeStamp from pubs.dbo.jobs')
)

INSERT INTO WOCK.pubs.DBO.authors (TRANSACTIONNO, DATETIME, READTSVALUE, LASTTSVALUE)
SELECT TRANSACTIONNO, DATETIME, READTSVALUE, @TS FROM #TEMP

--------------------- ERROR -------------------------------

Server: Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'insert'.

--------------------- ERROR -------------------------------

Can anyone please help ...............

-KNR"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-03 : 13:02:09

The code above the INSERT is what is causing you the problem not the INSERT. I am unable to get your SELECT @TS to work using OPENQUERY. It just won't let you do it with as a derived table. You'll probably need to put the results into a temporary table then put it into a variable.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-03 : 21:53:54

You are using a derived table which has no name.

select @TS=max(Timestamp)
from
(
select Timestamp
FROM OPENQUERY(Wock,
'select max(TimeStamp) as TimeStamp from pubs.dbo.Titles')
union
select Timestamp
FROM OPENQUERY(Wock,
'select max(TimeStamp) as TimeStamp from pubs.dbo.jobs')
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources