UPDATETEXT across linked server

[pgraves]

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

 

[ptheriault]

Look up openquery in BOL.

I've never done an updatetext with it but here is an example of an insert that I use. This should give you a idea.

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] MavResource.dbo.MONTHLY_EARNED(
ERP_ID 
)
[COLOR=blue]select[/color]
	[COLOR=#FF00FF]convert[/color](bigint, ERP_ID)
[COLOR=blue]from[/color] OPENQUERY
(RSDEV,
[COLOR=red]'select
[/color]    [COLOR=#FF00FF]to_char[/color](ERP_ID) [COLOR=blue]as[/color] ERP_ID
    [COLOR=blue]FROM[/color] MONTHLY_EARNED
   [COLOR=blue]WHERE[/color] EVAL_PERIOD = ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](EVAL_PERIOD)
                         [COLOR=blue]FROM[/color] MONTHLY_EARNED)[COLOR=red]'
[/color]   )

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

I would recommend using a stored procedure on the remote server's side to do the update text passing it all the needed information to identify the record to be updated.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pgraves]

Denny,

That is exactly what I have just tried to do, however, at some point I still need to refer to the database column using UPDATETEXT.

If I run it at source I have to reference a remote destination server. If I run it at the destination I have to reference a remote source server.

I find it difficult to believe that no-one has had to update a text/ntext field across a linked server before, but I have spent a while searching without any luck.

Thanks for your suggestion anyway.

Paul

 

[mrdenny]

Setup your code like this.

create procedure usp_Something
    @recordid int,
    @TextToAdd text
as
...
exec RemoteServer.Database.dbo.database @recordid=@recordid, @TextToAdd=@TextToAdd
...
go

create procedure usp_SomethingElse
   @recordid int,
   @TextToAdd text
as
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPRT(TextField)
FROM table
where RecordID = @recordid
...
UPDATETEXT Table.column @ptrval 0, 0, @TextToAdd
...
GO

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

The code on the calling server should actully be

[create procedure usp_Something
    @recordid int,
    @TextToAdd text
as
...
exec RemoteServer.Database.dbo.usp_SomethingElse @recordid=@recordid, @TextToAdd=@TextToAdd
...

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pgraves]

Denny,

I have created a stored proc on the remote server which inserts the text column into a temp table.

I then use UPDATETEXT which references the temp table as the source table.

It's probably not the most efficient method but it works.

Thanks for your suggestions.

Paul

 

[mrdenny]

The fact that it's working is the most important part. Now that it's working you can look into making it work better if it's needed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com