Collation fun - 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 2005 Forums

Transact-SQL (2005)

Collation fun

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2010-04-29 : 15:28:37

If I have a database with a collation...

"SQL_Latin1_General_Pref_CP1_CI_AS"

On a Server Defined As

"SQL_Latin1_General_CP1_CI_AS"

And I create a Global Temporary Table in a procedured compiled in the "SQL_Latin1_General_Pref_CP1_CI_AS" collation...what collation is the Temp table created under?

I'm asking because all of a sudden on 2k5, I'm getting


Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_Pref_CP1_CI_AS" in the equal to operation.

Where I don't think I had this problem before on 2000...of course I could be wrong...but this I believe is the suspect code


TRUNCATE TABLE FK_create_code
SET @DYSQL = 'declare cstrts cursor fast_forward read_only for
 SELECT DISTINCT
	  c.[TABLE_SCHEMA]
	, c.[TABLE_NAME]
	, u.CONSTRAINT_NAME
  FROM   ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
  JOIN   ##Key_Column_usage u
    ON    c.[TABLE_NAME]      = u.[TABLE_NAME]
   AND	  c.[TABlE_SCHEMA]    = u.[TABLE_SCHEMA]
   AND    c.[COLUMN_NAME]     = u.[COLUMN_NAME]
  JOIN   ##TABLE_CONSTRAINTS t
    ON    u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
 WHERE    t.[CONSTRAINT_TYPE] = ' + '''FOREIGN KEY'''

Any thoughts?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-04-29 : 15:33:46

TEMP tables are created with Server Collation.

We always (house rules!!) explicitly state the Collation on all Create Table (including CREATE TABLE #TempTable, SELECT ... INTO #TempTable ... and DECLARE @TableVar TABLE) statements so that the table created in TEMPDB has known / expected / predictable Collation on VARCHAR / TEXT / etc columns

At the very least it saves us if the APP is migrated to a machine where the Server Collation is something different.

X002548
Not Just a Number

15586 Posts

Posted - 2010-04-30 : 09:23:42

Even in 2000????

Thanks for the info.....

Just to be extra lazy....where do you supply the collation?

CREATE TABLE tbname collation (

???

I'll go look it up

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-30 : 09:51:55

CREATE TABLE tbname
(
col1 varchar(100) collate SQL_Latin1_General_CP1_CI_AS
.
.
.
)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 10:04:24

quote:
Originally posted by X002548

Even in 2000????

Yup

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 10:05:23

The NULL bit (if you need it) comes last


CREATE TABLE tbname 
(
col1 varchar(100) collate SQL_Latin1_General_CP1_CI_AS {NOT} NULL
.
.
.
)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-30 : 11:21:29

I don't recommend it, but if you use the SELECT .. INTO syntax, like:

SELECT *
INTO #MyTable
FROM...

That will create the temp table with the same collation as the source columns. FYI..

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 11:25:46

Yeah, not good under SQL 2000 (Locks TEMPDB longer than SQL2005 would), but


SELECT *
INTO #MyTable
FROM...
WHERE 1=0

would be OK to create the table (lock-duration-wise)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-04-30 : 11:58:23

quote:
Originally posted by Kristen

Yeah, not good under SQL 2000 (Locks TEMPDB longer than SQL2005 would), but

SELECT *
INTO #MyTable
FROM...
WHERE 1=0

would be OK to create the table (lock-duration-wise)

I think that locking issue was only in SQL 6.5 and before. But, I'm not 100% sure on that.

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 13:06:55

I'm fairly certain the locking issue was in SQL 2000 too, but that's only from memory too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources