Collation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Collation problem

Author

Topic

ochakov
Starting Member

3 Posts

Posted - 2006-09-07 : 12:31:45

I have very interesting collation problem. If my database is not the same collation as the server, when I try to create a temporary stored procedure, I get 'Cannot resolve collation conflict for equal to operation' error when compare stored procedure parameters to constants. Below is a sample script that produces the error. If I remove the database collation override or create not temporary stored procedure, the error is not produced.


USE master
CREATE DATABASE coll_test COLLATE Japanese_CS_AS

GO

USE coll_test
CREATE TABLE tab_test
	(fid INT, fvar1 varchar(255), fvar2 nvarchar(255))

INSERT INTO tab_test values
	(1, 'Test 1', N'Unicode Test 1')
INSERT INTO tab_test values
	(2, 'Test 2', N'Unicode Test 2')
INSERT INTO tab_test values
	(3, 'Test 3', N'Unicode Test 3')
INSERT INTO tab_test values
	(4, 'Test 4', N'Unicode Test 4')
INSERT INTO tab_test values
	(5, 'Test 5', N'Unicode Test 5')

GO

CREATE PROCEDURE tempdb..proc_test
	@Param1 varchar(255) = '',
	@Param2 nvarchar(255) = N''
AS
SELECT * FROM tab_test WHERE
	(@Param1=''  OR fvar1 = @Param1) AND
	(@Param2=N'' OR fvar2 = @Param2)

GO

EXEC #proc_test
DROP PROCEDURE #proc_test

GO

USE master
DROP DATABASE coll_test

ochakov
Starting Member

3 Posts

Posted - 2006-09-07 : 12:45:19

Also, if I use a local variable instead of '' constant, it works

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-07 : 13:48:14

The tempdb database isn't using the same collation so you need to coerce the collation in that stored procedure like this:

CREATE PROCEDURE #proc_test
@Param1 varchar(255) = '',
@Param2 nvarchar(255) = N''
AS
SELECT * FROM tab_test WHERE
(@Param1='' COLLATE Japanese_CS_AS OR fvar1 = @Param1) AND
(@Param2=N'' COLLATE Japanese_CS_AS OR fvar2 = @Param2)

ochakov
Starting Member

3 Posts

Posted - 2006-09-07 : 14:01:09

Thanks for your reply.
I know that the problem can be solved this way. The question is: where am I using anything from tempdb? The documentation says that the parameters and constants collation is taken from the calling database and not from tempdb, so it looks like a bug.
My real problem is that I have 80 stored procedures to convert and I am thinking of some nicer solution.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-07 : 14:53:34

Your stored procedure is IN tempdb. If you create the stored procedure in the database with the collation then you won't get those errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources