UPDATETEXT across linked server

Question

Post reply

UPDATETEXT across linked server

pgraves

Ten Centuries

Points: 1105
More actions
March 20, 2007 at 5:50 am

#175658

Hi all,
I am trying to use the UPDATETEXT functionality across a linked server and am not sure if it is possible e.g.
This is BOL syntax:
UPDATETEXT table_name.column_name @dest_textptr 0 NULL table_name.column_name @src_textptr
I need to reference a column on a remote server:
UPDATETEXT server_name.database_name..table_name.column_name??? @dest_textptr 0 NULL table_name.column_name @src_textptr
Obviously, using the four-part naming convention means I can't specify the column name.
If anyone has any ideas on how to do this it would be apreciated.
Paul

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

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

I don't think that this is possible:

Check out this link

http://msdn2.microsoft.com/en-us/library/ms175129.aspx

there is a line in there:

READTEXT, WRITETEXT, and UPDATETEXT statements are not supported against remote tables.

What are you trying to achieve with this, maybe it's possible another way?!?

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

pgraves Ten Centuries Points: 1105 More actions · Answer 2

Adrian,

Thanks for that. I have searched for a while and could not find any reference to UPDATETEXT and remote servers.

I am basically using a trigger to replicate a change in one table to another table on a remote server. I cannot use SQL Server replication.

I have tried using a stored procedure on the remote server, but can't pass the Text column to the stored procedure because I can't assign it to a variable.

Paul

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

Okay Paul,

Do your rows have Unique IDs in your original table (the one with the trigger on)?

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 4

If they do, you could use your trigger to fire a stored procedure on the linked server; passing the row's Unique ID (say @rowID for ease) as a parameter to the procedure which could then copy the text in to a temporary table. You should then be able to use this remote temporary table to update the text on the remote table. The temporary table would need to be created within the stored procedure.

I guess it'd look something like this:

CREATE PROCEDURE spReplicateUpdate(

@rowID INT)

AS

CREATE TABLE #tmpTextTransfer(

textCol Text)

INSERT INTO #tmpTextTransfer

SELECT textCol

FROM originalServer.originalDatabase.originalOwner.originalTable

WHERE rowID = @rowID

/*

And then the UPDATETEXT command using the

now "local" temporary table data

*/

If not you might be able to pass enough parameters from the trigger to identify the correct row, still using the above theory.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

pgraves Ten Centuries Points: 1105 More actions · Answer 5

Adrian,

That is exactly what I have just thought of.

I tested it and it works perfectly fine.

Thanks for your help.

Paul