SQL Server Performance Forum – Threads Archive

Collation issue

We have a SQL Server 2000 database server with a database created on this box with 65 compatibility set. I have created a stored procedure based on the undocumented stored procedure sp_MShelpcolumns. USE MASTER GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_tabledefinition]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) drop procedure [dbo].[sp_tabledefinition] GO create procedure [dbo].[sp_tabledefinition] ( @tablename nvarchar(517), @flags int = 0, @orderby nvarchar(10) = null, @flags2 int = 0 ) as — taken from sp_MShelpcolumns – create table #sphelpcols ( col_name nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NOT NULL, col_typename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NOT NULL, col_len int NOT NULL, col_prec int NULL, col_scale int NULL, col_null bit NOT NULL, col_identity bit NOT NULL, col_flags int NULL, col_seed nvarchar (40) COLLATE database_default NULL, col_increment nvarchar (40) COLLATE database_default NULL, col_collation nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NULL, col_language int, ————————————— — Required Fields – ————————————— col_id int NOT NULL, col_objectid int NOT NULL, col_dridefid int NULL, col_basetypename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NOT NULL ) if @flags is null select @flags = 0 if (@tablename = N’?’) begin print N” print N’Usage: sp_MShelpcolumns @tablename, @flags int = 0′ print N’ where @flags is a bitmask of:' print N’ 0x0200= No DRI (ignore Checks, Primary/Foreign/Unique Keys, etc.)' print N’ 0x0400= UDDTs –> Base type' print N’ 0x80000= TimestampToBinary (convert timestamp cols to binary(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)' print N’ 0x40000000= No Identity attribute' return 0 end declare @objid int select @objid = object_id(@tablename) if (@objid is null) begin RAISERROR (15001, -1, -1, @tablename) return 1 end set nocount on insert #sphelpcols select convert(varchar(255), c.name), convert(varchar(255), st.name), case when bt.name in (N’nchar’, N’nvarchar’) then c.length/2 else c.length end, ColumnProperty(@objid, c.name, N’Precision’), ColumnProperty(@objid, c.name, N’Scale’), convert(bit, ColumnProperty(@objid, c.name, N’AllowsNull’)), case when (@flags & 0x40000000 = 0) then convert(bit, ColumnProperty(@objid, c.name, N’IsIdentity’)) else 0 end, case when st.name in (N’char’,N’varchar’,N’binary’,N’varbinary’,N’nchar’,N’nvarchar’) then 0x0001 when st.name in (N’decimal’,N’numeric’) then 0x0002 else 0 end + case isnull(ColumnProperty(@objid, c.name, N’IsRowGuidCol’), 0) when 0 then 0 else 0x0008 end, case when (ColumnProperty(@objid, c.name, N’IsIdentity’) <> 0) then CONVERT(varchar(80), ident_seed(@tablename)) else null end, case when (ColumnProperty(@objid, c.name, N’IsIdentity’) <> 0) then CONVERT(varchar(80), ident_incr(@tablename)) else null end, c.collation, c.language, ————————————— — Required Fields – ————————————— c.colid, c.id, case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N’IsDefaultCnst’) <> 0)) then t.id else null end, convert(varchar(255), bt.name) from dbo.syscolumns c left outer join dbo.sysobjects d on d.id = c.cdefault left outer join dbo.sysobjects r on r.id = c.domain join dbo.systypes st on st.xusertype = c.xusertype join dbo.systypes bt on bt.xusertype = c.xtype left outer join dbo.syscomments t on t.id = c.cdefault and t.colid = 1 and not exists (select * from dbo.syscomments where id = c.cdefault and colid = 2) where c.id = @objid order by c.colid if (@flags & 0x80000 != 0) update #sphelpcols set col_typename = N’binary’, col_len = 8, col_flags = col_flags | 0x0001 where col_typename = N’timestamp' if (@flags is not null and @flags != 0) begin if (@flags & 0x0400 != 0) begin declare @typeflagmask int select @typeflagmask = (convert(int, 0x0001) + convert(int, 0x0002)) update #sphelpcols set col_typename = convert(varchar(255), b.name), col_flags = col_flags & [email protected] + case when b.name in (N’char’,N’varchar’,N’binary’,N’varbinary’,N’nchar’,N’nvarchar’) then 0x0001 when b.name in (N’decimal’,N’numeric’) then 0x0002 else 0 end from #sphelpcols c, dbo.systypes n, dbo.systypes b where n.name = col_typename –// xtype (base type) of name (FAILS HERE)[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] and b.xusertype = n.xtype –// Map it back to where it’s xusertype, to get the name end end if (@flags & 0x0200 = 0 and (OBJECTPROPERTY(@objid, N’TableHasPrimaryKey’) <> 0)) begin declare @indid int select @indid = indid from dbo.sysindexes i where i.id = @objid and i.status & 0x0800 <> 0 if (@indid is not null) update #sphelpcols set col_flags = col_flags | 0x0004 from #sphelpcols c, dbo.sysindexkeys i where i.id = @objid and i.indid = @indid and i.colid = c.col_id end set nocount off if (@orderby is null or @orderby = N’id’) begin select c.col_name, c.col_typename, c.col_len, col_prec = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_prec else NULL end, col_scale = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_scale else NULL end, c.col_null, c.col_identity, c.col_flags, c.col_seed, c.col_increment, c.col_collation, case when ( c.col_language >= 0 ) then c.col_language else -1 end as col_language&l t;br />from ((#sphelpcols c left outer join dbo.syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join dbo.syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid) left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where scol.colid = sdep.depnumber and sdep.deptype = 1 and scol.id = @objid and sdep.depid = @objid and ColumnProperty(scol.id, scol.name, N’IsTypeForFullTextBlob’) = 1 ) as d on c.col_id = d.FT_ID order by c.col_id end else begin select c.col_name, c.col_typename, c.col_len, col_prec = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_prec else NULL end, col_scale = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_scale else NULL end, c.col_null, c.col_identity, c.col_flags, c.col_seed, c.col_increment, c.col_collation, case when ( c.col_language >= 0 ) then c.col_language else -1 end as col_language from ( (#sphelpcols c left outer join dbo.syscomments cm on (cm.id = c.col_objectid and cm.number = c.col_id ) ) left outer join dbo.syscomments cn on (c.col_dridefid is not null and cn.id = c.col_dridefid) ) left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where scol.colid = sdep.depnumber and sdep.deptype = 1 and scol.id = @objid and sdep.depid = @objid and ColumnProperty(scol.id, scol.name, N’IsTypeForFullTextBlob’) = 1 ) as d on (c.col_id = d.FT_ID) order by c.col_name end GO Execute the following line against a 65 compatible database on the SQL 2000 database server execute sp_tabledefinition ‘dbo.EnterTableNameHere’, 512, @orderby = ‘id' Does anyone know how I can get around this?[?]

I never reach a collation error. I get this error first: Server: Msg 207, Level 16, State 3, Procedure sp_tabledefinition, Line 123
Invalid column name ‘col_numtype’.
Server: Msg 207, Level 16, State 1, Procedure sp_tabledefinition, Line 123
Invalid column name ‘col_numtype’.
Server: Msg 207, Level 16, State 1, Procedure sp_tabledefinition, Line 150
Invalid column name ‘col_numtype’.
Server: Msg 207, Level 16, State 1, Procedure sp_tabledefinition, Line 150
Invalid column name ‘col_numtype’. This is when running the following in Northwind:
execute sp_tabledefinition ‘dbo.Employees’, 512, @orderby = ‘id’

Apologies, correction made for col_numtype – still get collation conflict. USE MASTER GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_tabledefinition]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) drop procedure [dbo].[sp_tabledefinition] GO create procedure [dbo].[sp_tabledefinition] ( @tablename nvarchar(517), @flags int = 0, @orderby nvarchar(10) = null, @flags2 int = 0 ) as – taken from sp_MShelpcolumns – create table #sphelpcols ( col_name nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NOT NULL, col_typename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NOT NULL, col_len int NOT NULL, col_prec int NULL, col_scale int NULL, col_null bit NOT NULL, col_identity bit NOT NULL, col_flags int NULL, col_seed nvarchar (40) COLLATE database_default NULL, col_numtype smallint NOT NULL, col_increment nvarchar (40) COLLATE database_default NULL, col_collation nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NULL, col_language int, ————————————— – Required Fields – ————————————— col_id int NOT NULL, col_objectid int NOT NULL, col_dridefid int NULL, col_basetypename nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> COLLATE database_default NOT NULL ) if @flags is null select @flags = 0 if (@tablename = N’?’) begin print N” print N’Usage: sp_MShelpcolumns @tablename, @flags int = 0′ print N’ where @flags is a bitmask of:' print N’ 0x0200 = No DRI (ignore Checks, Primary/Foreign/Unique Keys, etc.)' print N’ 0x0400 = UDDTs –> Base type' print N’ 0x80000 = TimestampToBinary (convert timestamp cols to binary(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)' print N’ 0x40000000 = No Identity attribute' return 0 end declare @objid int select @objid = object_id(@tablename) if (@objid is null) begin RAISERROR (15001, -1, -1, @tablename) return 1 end set nocount on insert #sphelpcols select convert(varchar(255), c.name), convert(varchar(255), st.name), case when bt.name in (N’nchar’, N’nvarchar’) then c.length/2 else c.length end, ColumnProperty(@objid, c.name, N’Precision’), ColumnProperty(@objid, c.name, N’Scale’), case when (@flags2 & 1 <> 0 and bt.name in (N’tinyint’,N’smallint’,N’decimal’,N’int’,N’real’,N’money’,N’float’,N’numeric’,N’smallmoney’,N’bigint’)) then 1 else 0 end, convert(bit, ColumnProperty(@objid, c.name, N’AllowsNull’)), case when (@flags & 0x40000000 = 0) then convert(bit, ColumnProperty(@objid, c.name, N’IsIdentity’)) else 0 end, case when st.name in (N’char’,N’varchar’,N’binary’,N’varbinary’,N’nchar’,N’nvarchar’) then 0x0001 when st.name in (N’decimal’,N’numeric’) then 0x0002 else 0 end + case isnull(ColumnProperty(@objid, c.name, N’IsRowGuidCol’), 0) when 0 then 0 else 0x0008 end, case when (ColumnProperty(@objid, c.name, N’IsIdentity’) <> 0) then CONVERT(varchar(80), ident_seed(@tablename)) else null end, case when (ColumnProperty(@objid, c.name, N’IsIdentity’) <> 0) then CONVERT(varchar(80), ident_incr(@tablename)) else null end, c.collation, c.language, ————————————— – Required Fields – ————————————— c.colid, c.id, case when (@flags & 0x0200 = 0 and c.cdefault is not null and (OBJECTPROPERTY(c.cdefault, N’IsDefaultCnst’) <> 0)) then t.id else null end, convert(varchar(255), bt.name) from dbo.syscolumns c left outer join dbo.sysobjects d on d.id = c.cdefault left outer join dbo.sysobjects r on r.id = c.domain join dbo.systypes st on st.xusertype = c.xusertype join dbo.systypes bt on bt.xusertype = c.xtype left outer join dbo.syscomments t on t.id = c.cdefault and t.colid = 1 and not exists (select * from dbo.syscomments where id = c.cdefault and colid = 2) where c.id = @objid order by c.colid if (@flags & 0x80000 != 0) update #sphelpcols set col_typename = N’binary’, col_len = 8, col_flags = col_flags | 0x0001 where col_typename = N’timestamp' if (@flags is not null and @flags != 0) begin if (@flags & 0x0400 != 0) begin declare @typeflagmask int select @typeflagmask = (convert(int, 0x0001) + convert(int, 0x0002)) update #sphelpcols set col_typename = convert(varchar(255), b.name), col_flags = col_flags & [email protected] + case when b.name in (N’char’,N’varchar’,N’binary’,N’varbinary’,N’nchar’,N’nvarchar’) then 0x0001 when b.name in (N’decimal’,N’numeric’) then 0x0002 else 0 end from #sphelpcols c, dbo.systypes n, dbo.systypes b where n.name = col_typename –// xtype (base type) of name (FAILS HERE) and b.xusertype = n.xtype –// Map it back to where it’s xusertype, to get the name end end if (@flags & 0x0200 = 0 and (OBJECTPROPERTY(@objid, N’TableHasPrimaryKey’) <> 0)) begin declare @indid int select @indid = indid from dbo.sysindexes i where i.id = @objid and i.status & 0x0800 <> 0 if (@indid is not null) update #sphelpcols set col_flags = col_flags | 0x0004 from #sphelpcols c, dbo.sysindexkeys i where i.id = @objid and i.indid = @indid and i.colid = c.col_id end set nocount off if (@orderby is null or @orderby = N’id’) begin select c.col_name, c.col_typename, c.col_len, col_prec = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_prec else NULL end, col_scale = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_scale else NULL end, c.col_null, c.col_identity, c.col_flags, c.col_seed, c.col_increment, c.col_collation, case when ( c.col_language >= 0 ) then c.col_language else -1 end as col_language from ((#sphelpcols c left outer join dbo.syscomments cm on cm.id = c.col_objectid and cm.number = c.col_id) left outer join dbo.syscomments cn on c.col_dridefid is not null and cn.id = c.col_dridefid) left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where scol.colid = sdep.depnumber and sdep.deptype = 1 and scol.id = @objid and sdep.depid = @objid and ColumnProperty(scol.id, scol.name, N’IsTypeForFullTextBlob’) = 1 ) as d on c.col_id = d.FT_ID order by c.col_id end else begin select c.col_name, c.col_typename, c.col_len, col_prec = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_prec else NULL end, col_scale = case when (col_basetypename in (N’decimal’,N’numeric’) or (@flags2 & 1 <> 0 and col_numtype & 1 <> 0)) then c.col_scale else NULL end, c.col_null, c.col_identity, c.col_flags, c.col_seed, c.col_increment, c.col_collation, case when ( c.col_language >= 0 ) then c.col_language else -1 end as col_language from ( (#sphelpcols c left outer join dbo.syscomments cm on (cm.id = c.col_objectid and cm.number = c.col_id ) ) left outer join dbo.syscomments cn on (c.col_dridefid is not null and cn.id = c.col_dridefid) ) left outer join (select distinct FT_COLNAME = scol.name, FT_ID = sdep.number from dbo.syscolumns scol, dbo.sysdepends sdep where scol.colid = sdep.depnumber and sdep.deptype = 1 and scol.id = @objid and sdep.depid = @objid and ColumnProperty(scol.id, scol.name, N’IsTypeForFullTextBlob’) = 1 ) as d on (c.col_id = d.FT_ID) order by c.col_name end GO use northwind GO execute sp_tabledefinition ‘dbo.Employees’, 512, @orderby = ‘id'

Now I get this error:
Server: Msg 515, Level 16, State 2, Procedure sp_tabledefinition, Line 59
Cannot insert the value NULL into column ‘col_numtype’, table ‘tempdb.dbo.#sphelpcols___000100000013’; column does not allow nulls. INSERT fails.
The statement has been terminated. If I change the colum to allow null it works on both compatibility modes (65 and 80). But I have the same collation on the tempDB as my user databases so I guess that is why it works. What collation do you have on your tempDB and what collation do you have on your user database and does sp_MShelpcolumns work in this database?

quote:Originally posted by Argyle Now I get this error:
Server: Msg 515, Level 16, State 2, Procedure sp_tabledefinition, Line 59
Cannot insert the value NULL into column ‘col_numtype’, table ‘tempdb.dbo.#sphelpcols___000100000013’; column does not allow nulls. INSERT fails.
The statement has been terminated. If I change the colum to allow null it works on both compatibility modes (65 and 80). But I have the same collation on the tempDB as my user databases so I guess that is why it works. What collation do you have on your tempDB and what collation do you have on your user database and does sp_MShelpcolumns work in this database?

The tempDB & master database have a collation type of SQL_Latin1_General_CI_AS and the 65 database I want to run this against has a collation type of SQL_Latin1_General_CP850_CI_AS. The problem I have is that an SQL 2k database running 65 Compatiblity will not, it appears allow for any collation conversions.

Hi Richard, I can’t think of an elegant way around your problem… With no one replying to your queries so far, I suspect that we’re all in the same boat… The only thing I can think of is setting up a second instance with a matching collation… Cheers
Twan

This is do-able, sp_MSHelpColumns tablename works, but I can’t for the life of me figure out what the difference is between sp_MSHelpColumns and the stored procedure I have written are, apart from the removal of information from the select that is unneeded for what I am trying to do. The main problem I have with sp_MSHelpColumns is that I cannot do the following: insert into #sometable
execute sp_MSHelpColumns tablename The temporary table I need to create would have a definition exceeding the capacity in bytes allowed for a table. (This is mainly because of the two fields with a column name of text). Any help would be greatly appreciated. Regards,
Rik

]]>