April 27, 2005 at 3:31 am
Hi, can someone help with the following problem ? (SQLserver 2000)
I have a stored proc running fine on my development server. when I add a new SP onto the live server and paste the same code from the development server and try to save I get the following error message: -
Error 446: Cannot resolve collation conflict for equal to operation.
The SP is a s follows: -
CREATE PROCEDURE [dbo].[prc993_Core_audit_field_list]
(@strTableName varchar(128))
AS
Select A.[name] as 'column_name',
B.[id] as 'column_id',
0 as color,
0 as error,
0 as selected,
0 as found,
A.colorder as 'rownumber',
Case A.xType
When 34 Then 'image' ---- Blob --
When 35 Then 'text'
When 52 Then 'smallint'
When 56 Then 'int'
When 60 Then 'money'
When 61 Then 'datetime'
When 104 Then 'bit'
When 106 Then 'decimal'
When 167 Then 'varchar'
When 175 Then 'char'
Else 'other'
End as 'column_type',
Cast(C.value as varchar(255) ) as 'column_description',
B.[name] as 'table_name'
From sysobjects B inner join
syscolumns A on B.id = A.id Left Outer Join
::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@strTableName, N'column', null) C on A.name = C.objname
Where B.name = @strTableName
Order by A.colorder
GO
Thanks for any help.
CCB
April 27, 2005 at 4:20 am
You need add the sentence
COLLATE SQL_Latin1_General_CP1_CI_AS
I have my server in Español , you need put collate '''''''''''', your language.
April 27, 2005 at 4:29 am
Ok,
Where in the SP does this ned to go ?
I am using COLLATE Latin1_General_CI_AS.
Thanks.
CCB
April 27, 2005 at 7:32 am
You can prove..... in red color
CREATE PROCEDURE [dbo].[prc993_Core_audit_field_list]
(@strTableName varchar(128))
AS
Select A.[name] as 'column_name',
B.[id] as 'column_id',
0 as color,
0 as error,
0 as selected,
0 as found,
A.colorder as 'rownumber',
Case A.xType
When 34 Then 'image' ---- Blob --
When 35 Then 'text'
When 52 Then 'smallint'
When 56 Then 'int'
When 60 Then 'money'
When 61 Then 'datetime'
When 104 Then 'bit'
When 106 Then 'decimal'
When 167 Then 'varchar'
When 175 Then 'char'
Else 'other'
End as 'column_type',
Cast(C.value as varchar(255) ) as 'column_description',
B.[name] as 'table_name'
From sysobjects B inner join
syscolumns A on B.id = A.id COLLATE Latin1_General_CI_AS
Left Outer Join
::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@strTableName, N'column', null) C on A.name = C.objname
Where B.name = @strTableName
Order by A.colorder
April 27, 2005 at 8:19 am
Mine is in us_english but I think that the right place should be on the name column :
Select A.[name] as 'column_name',
B.[id] as 'column_id',
0 as color,
0 as error,
0 as selected,
0 as found,
A.colorder as 'rownumber',
Case A.xType
When 34 Then 'image' ---- Blob --
When 35 Then 'text'
When 52 Then 'smallint'
When 56 Then 'int'
When 60 Then 'money'
When 61 Then 'datetime'
When 104 Then 'bit'
When 106 Then 'decimal'
When 167 Then 'varchar'
When 175 Then 'char'
Else 'other'
End as 'column_type',
Cast(C.value as varchar(255) ) as 'column_description',
B.[name] as 'table_name'
From sysobjects B inner join
syscolumns A on B.id = A.id
Left Outer Join
::FN_LISTEXTENDEDPROPERTY(N'MS_Description',N'user',N'dbo',N'table',@strTableName, N'column', null) C on A.name = C.objname COLLATE Latin1_General_CI_AS
Where B.name = @strTableName
Order by A.colorder
hth
* Noel
April 27, 2005 at 8:25 am
ok you put the collate in the equals, if you prove put in both and its funcionatility, ok , its resolve.
April 27, 2005 at 9:38 am
Hi, I could only get it to work with Noels suggestion.
Thanks to all who responded.
CCB
April 28, 2005 at 2:08 am
Note that adding collation comments into your SQL code could invariably increase the execution time of the code/procedure. It is best to keep the Server Collation the same between your development environment on your production environment.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply