"Sevugan" <Sevugan@discussions.microsoft.com> wrote in message
news:127ABC63-916C-470E-B424-34C860D7E77E@microsoft.com...
> Hi,
> I am passing the name of a temporary table(#TempTable1) to a stored
> procedure. I am creating another temporary table inside this stored
procedure
> (#TempTable2).
> I am executing an update statement by joining these 2 temporary tables
in
> this stored procedure.
>
> UPDATE A
> SET A.Field1= ISNULL(B.Field1, 0)
> FROM #TempTable1 A, #TempTable2 B
> WHERE A.Field2 = B.Field2
>
> At that point of time, I am getting the following error.
> "Cannot resolve collation conflict for equal to operation."
>
> Why does this happen. How can I resolve the same?
>
> Thanks and Regards,
>
> Sevugan.C

"Uri Dimant" wrote:

> Hi
>
> UPDATE A
> SET A.Field1= ISNULL(B.Field1, 0)
> FROM #TempTable1 A, #TempTable2 B COLLATE .(Put COLLATION...Name here).....
> WHERE A.Field2 = B.Field2
>
> Note: It is considered a bad practice to pass a table name as a parameter to
> stored procedure as well as using such tecnique for updating
>
> Please take a look at David Portas's example how this tecnique can affect
> the output.
> CREATE TABLE Countries
> (countryname VARCHAR(20) NOT NULL PRIMARY KEY,
> capitalcity VARCHAR(20));
>
> CREATE TABLE Cities
> (cityname VARCHAR(20) NOT NULL,
> countryname VARCHAR(20) NOT NULL
> REFERENCES Countries (countryname),
> CONSTRAINT PK_Cities
> PRIMARY KEY (cityname, countryname));
>
> INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
> INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
>
> INSERT INTO Cities VALUES ('Washington', 'USA');
> INSERT INTO Cities VALUES ('London', 'UK');
> INSERT INTO Cities VALUES ('Manchester', 'UK');
>
> The MS-syntax makes it all too easy for the developer to slip-up by
> writing ambiguous UPDATE...FROM statements where the JOIN criteria is
> not unique on the right side of the join.
>
> Try these two identical UPDATE statements with a small change to the
> primary key in between.
>
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
> ON Countries.countryname = Cities.countryname;
>
> SELECT * FROM Countries;
>
> ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
> ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
> cityname);
>
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* don't do this! */
> ON Countries.countryname = Cities.countryname;
>
> SELECT * FROM Countries;
>
> You get this from the first SELECT statement:
>
> countryname capitalcity
> -------------------- --------------------
> UK London
> USA Washington
>
> and this from the second:
>
> countryname capitalcity
> -------------------- --------------------
> UK Manchester
> USA Washington
>
> (though these results aren't guaranteed - that's part of the problem).
>
> Why did the result change? The physical implementation has affected the
> meaning of the code, with serious, potentially disastrous consequences.
> How can you even test your code if its results are subject to change
> due to the vagaries of storage, indexing and cacheing?
>
> With the ANSI syntax there is no ambiguity. The UPDATE statement
> compels the programmer to design an unambiguous assignment subquery
> that returns no more than a single value.
>
> UPDATE Countries
> SET capitalcity =
> (SELECT MIN(cityname)
> FROM Cities
> WHERE Countries.countryname = Cities.countryname);
>
> At the very least this forces the developer to reconsider whether the
> UPDATE statement makes logical sense. You might want to make an effort
> to learn Standard SQL instead of a dialect that can change at any time,
> which will not port, cannot be understood by other programmers, etc.
>
>
> "Sevugan" <Sevugan@discussions.microsoft.com> wrote in message
> news:127ABC63-916C-470E-B424-34C860D7E77E@microsoft.com...
> > Hi,
> > I am passing the name of a temporary table(#TempTable1) to a stored
> > procedure. I am creating another temporary table inside this stored
> procedure
> > (#TempTable2).
> > I am executing an update statement by joining these 2 temporary tables
> in
> > this stored procedure.
> >
> > UPDATE A
> > SET A.Field1= ISNULL(B.Field1, 0)
> > FROM #TempTable1 A, #TempTable2 B
> > WHERE A.Field2 = B.Field2
> >
> > At that point of time, I am getting the following error.
> > "Cannot resolve collation conflict for equal to operation."
> >
> > Why does this happen. How can I resolve the same?
> >
> > Thanks and Regards,
> >
> > Sevugan.C
>
>