Update SQL Server Database from another db

Question

I have a test and production environment using SQL Server 2008 R2. I want to update some columns from my test environment to my prod environment. My query makes sense to me but it keeps erroring. Any advice on how to proceed with this is greatly appreciated.

use [NGProd]

update nxmd_practice_web_text
set nxmd_practice_web_text.item_value = TST.item_value
from [TestSQL1].[NGTest].[dbo].[nxmd_practice_web_text] TST
where TST.nx_modified_by = '00000000-0000-0000-0000-000000002692'
  and nxmd_practice_web_text.item_id = 4100
  and nxmd_practice_web_text.nx_practice_id = TST.nx_practice_id

I get the following error:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.

Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The item_value column is ntext but I am not comparing it. I am setting it so this error is really confusing to me.

I understand that the ntext data type is deprecated, but there is nothing I can do about it. This is a paid for product with millions of rows of data across thousands of tables. I can not modify this row type without it be converted back in future updates (which post semi annually).

Any help is greatly appreciated.

Thank you.

Answer 1

After trying to recreate your error using a Sql 2008 and a Sql 2000 server, I could only get a similar error when I changed the nx_modified_by to ntext or practice_id to ntext. The Sql Server 2008 Error Message was:

Msg 402, Level 16, State 1, Line 8 The data types ntext and varchar are incompatible in the equal to operator.

I'm guessing the nx_modified_by is also ntext (instead of unique_identifier), but included a commented out line in case it is the nx_practice_id.

Try This:

use [NGProd]
go
update prod 
  set prod.item_value = tst.item_value 
  from NGProd.[dbo].[nxmd_practice_web_text] as prod
    inner join [TestSQL1].[NGTest].[dbo].[nxmd_practice_web_text] as tst
      on prod.nx_practice_id = tst.nx_practice_id
      /*
        on cast(prod.nx_practice_id as varchar(8000)) 
         = cast(prod.nx_practice_id as varchar(8000)) 
         --*/
  where prod.item_id = 4100
    and cast(tst.nx_modified_by as varchar(36)) 
      = '00000000-0000-0000-0000-000000002692'
    --and tst.nx_modified_by = '00000000-0000-0000-0000-000000002692'

Also, make sure you have In Process enabled on the Linked Server Properties

Technet - Linked Server Properties - Allow inprocess

Allow inprocess

SQL Server allows the provider to be instantiated as an in-process server. When this option is not set, the default behavior is to instantiate the provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed.