Update table on 2 dif servers with collation conflict 1

[03Explorer]

I have two tables

TABLE: TableA
GUID


TABLE:TableB
GUID
Variable

I created new column in TableA [CT] to be a substring from TableB [Variable]

I want to update TableA with the substring from TableB, but when I use straight sql I get error message [COLOR=red yellow]cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to peration.[/color]

Can I perform this with SSIS to avoid the conflict? Or is there a better way to write the following sql code...

UPDATE TableA
set TableA.CT = (SELECT
                    Substring(tb.Variable,3,2)
                  FROM
                    TableB tb (nolock)
                  WHERE
                    tb.GUID = TableA.GUID)
WHERE
  TableA.GUID = (SELECT
		  tb.GUID
		 FROM
		  TableB tb (nolock)
	 	WHERE
	          TableA.GUID = tb.GUID)

 

[dhulbert]

Add COLLATE Latin1_General_BIN

after your where clause, and maybe after your update as well and that should get rid of the problem.



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[03Explorer]

Thank you Dhulbert!

At first I was uncertain of your direction, but after finding a site to assist in guidance Example I came up with the solution below

UPDATE TableA
set TableA.CT = (SELECT
                    Substring(tb.Variable,3,2)
                  FROM
                    TableB tb (nolock)
                  WHERE
                    tb.GUID [COLOR=red][b]COLLATE Latin1_General_BIN[/b][/color] = TableA.GUID)
WHERE
  TableA.GUID [COLOR=red][b]COLLATE Latin1_General_BIN[/b][/color] = (SELECT
          tb.GUID
         FROM
          TableB tb (nolock)
         WHERE
              TableA.GUID [COLOR=red][b]COLLATE Latin1_General_BIN[/b][/color] = tb.GUID)

I can't say for certain, because query is working currently and there are 900,000 rows to update, if it worked. But I do know I haven't received the Collation error this time (and I am 5 minutes into execution and growing).

Thanks Again!!