UPDATE STATEMENTS IN OPENQUERY 1

[clivelp]

I need to run UPDATE commands in a stored procedure to a linked server connected to a legacy database, no probs with SELECT commands but am in a mess with UPDATE and INSERT

SELECT code as follows
SET @SQL = 'SELECT * FROM OPENQUERY(myServer,"
SELECT '+''+@Fields+''+'
FROM Department
WHERE '+''+@Field+''+' LIKE '''+''+@SearchId+'%'+'''
ORDER BY '+''+@Order+''+' &quot'

EXEC(@SQL)
GO

This works fine
Any help with UPDATE and INSERT much appreciated

 

[sguslan]

Use tables FQN to insert and update.

FQN for a linked server table is made up of 4 parts:
1 linked server name
2 database name
3 schema / owner
4 table

hence, use something like:

insert into linkedservername.databasename.schema.department
values( ....

or

update linkedservername.databasename.schema.department
set BossName='Terry'



If you do not like change, get out of the IT business..

 

[tlbroadbent]

It would be helpful to know the legacy database and to see the INSERT and UPDATE statements.

Not all databases support UPDATES through OpenQuery. The following is from SQL BOL.

The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. (emphasis added)

The update statment should look something like the following.

Update OpenQuery(linked_server,'Select * From HR.dbo.TblEmployees Where EmpID=1001)
Set EmpDept='Information Technology' If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[sguslan]

Every day we learn something new from Terry ..

Cheers with starts .. ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[tlbroadbent]

I just saw that I left off a quote from my first post.

Update OpenQuery(linked_server,'Select * From HR.dbo.TblEmployees Where EmpID=1001')
Set EmpDept='Information Technology' If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm