Prev: DB1 filegroup backups used to refresh same filegroup on DB2 ?
Next: Cannot connect to Database Engine
From: John Bell on 17 May 2010 06:10
Hi Bodo

Forcing the collation in the view may mean that you can "get away"
with not changing the column collation, but that is really a kludge.
Overall keeping collations consistent for the instance and database
helps to avoid this type of issue. Having the same collatons in dev,
test and live is also very important.

It looks like you need to enforce standards and consistency, you may
want to review your version control, build and release processes.

John

On Mon, 17 May 2010 11:36:30 +0200, "Bodo" <1> wrote:

>Thanks John,
>yes that works very well.
>
>However in the production database there are more tables
>with inconsistent collation order.
>So I have to modify these columns first and then rebuild
>all depending views.
>
>Again many thanks for jumping in.
>
>Bodo
>
>"John Bell" <jbellnewsposts(a)hotmail.com> schrieb im Newsbeitrag
>news:sju1v5tcckvcn4vu980j6mkpi0q6h0eokf(a)4ax.com...
>> Hi Bodo
>>
>> Have you tried dropping and re-creating the view?
>>
>> John
>>
>> On Mon, 17 May 2010 09:28:11 +0200, "Bodo" <1> wrote:
>>
>>>Hi Erland,
>>>thanks for responding.
>>>
>>>I run your query posted and as a result column KALENDERMONAT_TEXT collation
>>>is Latin1_General_CI_AS whereas database and server collation is set to
>>>SQL_Latin1_General_CP1_CI_AS
>>>I checked view definition that has no explicit collation option assigned and in the table columns
>>>collation definition
>>>is set to database default.
>>>
>>>Appreciate any thoughts on how to troubleshoot.
>>>Thanks
>>>Bodo
>>>
>>>
>>>"Erland Sommarskog" <esquel(a)sommarskog.se> schrieb im Newsbeitrag
>>>news:Xns9D79B9C0FE2E3Yazorman(a)127.0.0.1...
>>>> Bodo (1) writes:
>>>>> I'm unable to modify my view due to an error:
>>>>>
>>>>> Implicit conversion of varchar value to varchar cannot be performed
>>>>> because the collation of the value is unresolved due to a collation
>>>>> conflict
>>>>>
>>>>> Database colation is set to SQL_Latin1_General_CP1_CI_AS
>>>>>...
>>>>> The error refers to column [KALENDERMONAT_TEXT] in the view above.
>>>>
>>>> In TB_INDUSTRIEKALENDER, the column with this name has the
>>>> collation SQL_Latin1_General_CP1_CI_AS. But in the view:
>>>>
>>>>
>>>>> DDL of [dbo].[vw_AdressReport1]
>>>>> --------------------------------------------------
>>>>>
>>>>> SELECT col1,
>>>>> KALENDERJAHR
>>>>> ,KALENDERMONAT
>>>>> ,KALENDERMONAT_TEXT
>>>>> ,MONATJAHR_TEXT
>>>>> FROM [dbo].[TB_BETRIEB_FACTS] BF
>>>>> Inner Join dbo.vw_KalenderJahrMonat KAL On
>>>>> BF.[JAHRMONAT] = KAL.JAHRMONAT
>>>>
>>>> KALENDERMONAT_TEXT comes from another table, where the collation
>>>> apparently is a different one.
>>>>
>>>> You can find all columns that deviates from the database collation
>>>> with this query:
>>>>
>>>> select o.name, c.name, c.collation_name
>>>> from sys.columns c
>>>> join sys.objects o on c.object_id = o.object_id
>>>> where c.collation_name <>
>>>> convert(nvarchar(100), databasepropertyex(db_name(), 'Collation'))
>>>> and o.schema_id <> 4
>>>> order by o.name, c.name
>>>>
>>>>
>>>>
>>>> --
>>>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>>>
>>>> Links for SQL Server Books Online:
>>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>>> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>>>
>>>
>
From: Erland Sommarskog on 17 May 2010 08:34
Bodo (1) writes:
> Thanks John,
> yes that works very well.
>
> However in the production database there are more tables
> with inconsistent collation order.
> So I have to modify these columns first and then rebuild
> all depending views.
>
> Again many thanks for jumping in.

Looks like you have a mess to sort out due to sloppy routines when
installing SQL Server and creating databases. You should make sure that
you and your organisation has a well-defined routine when installing
SQL Server and which collation you should use. Else, you will have this
mess again and again.

The default collation when you install SQL Server is determined from your
system locale. You get if SQL_Latin1_General_CP1_CI_AS if your system
local is English (United States). If your system locale is German, you
will get Latin1_General_CI_AS.

I would also recommend that you use this latter collation, unless there is
a conscious decision to use SQL collations.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Bodo on 17 May 2010 13:49

"Erland Sommarskog" <esquel(a)sommarskog.se> schrieb im Newsbeitrag
news:Xns9D7B943EE4D81Yazorman(a)127.0.0.1...
> Bodo (1) writes:

> Looks like you have a mess to sort out due to sloppy routines when
> installing SQL Server and creating databases. You should make sure that
> you and your organisation has a well-defined routine when installing
> SQL Server and which collation you should use. Else, you will have this
> mess again and again.
>
> The default collation when you install SQL Server is determined from your
> system locale. You get if SQL_Latin1_General_CP1_CI_AS if your system
> local is English (United States). If your system locale is German, you
> will get Latin1_General_CI_AS.
>
> I would also recommend that you use this latter collation, unless there is
> a conscious decision to use SQL collations.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Thanks Erland for your post and sample query on how to determine inconsistency
in collation order.
This was very valueable to find out the table columns affected.

Unfortunately SQL Server Management Studio "supports" you with collation by
adding the db default collation with each Create table statement that you can produce
by right klick on a table ... script table to...
e.g:
CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](

[SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,

[KALENDERJAHR] [int] NOT NULL,

[KALENDERMONAT] [tinyint] NOT NULL,

....

So there has been a few scripts in the past with explicit Collate specification that I give to my
client.
Later on I always removed that clause from each create statement.
Now the production database defintion is consistent to my development system.

Again many thanks to both of you!

Best regards
Bodo


From: Sylvain Lafontaine on 17 May 2010 14:03

> Unfortunately SQL Server Management Studio "supports" you with collation
> by
> adding the db default collation with each Create table statement that you
> can produce
> by right klick on a table ... script table to...
> e.g:
> CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](
>
> [SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
>
> [KALENDERJAHR] [int] NOT NULL,
>
> [KALENDERMONAT] [tinyint] NOT NULL,
>
> ...
>
> So there has been a few scripts in the past with explicit Collate
> specification that I give to my client.
> Later on I always removed that clause from each create statement.
> Now the production database defintion is consistent to my development
> system.
>
> Again many thanks to both of you!
>
> Best regards
> Bodo

You can deactivate the option of scripting the collations: Tools | Options |
SQL Server Object Explorer | Scripting | Include collation -> set to False.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)



From: Bodo on 17 May 2010 14:20

"Sylvain Lafontaine" <sylvainlafontaine2009(a)yahoo.ca> schrieb im Newsbeitrag
news:eFrcWte9KHA.1872(a)TK2MSFTNGP02.phx.gbl...
>
>> Unfortunately SQL Server Management Studio "supports" you with collation by
>> adding the db default collation with each Create table statement that you can produce
>> by right klick on a table ... script table to...
>> e.g:
>> CREATE TABLE [dbo].[TB_ADRESSBERICHT_BETRIEB](
>>
>> [SATZART] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
>>
>> [KALENDERJAHR] [int] NOT NULL,
>>
>> [KALENDERMONAT] [tinyint] NOT NULL,
>>
>> ...
>>
>> So there has been a few scripts in the past with explicit Collate specification that I give to my
>> client.
>> Later on I always removed that clause from each create statement.
>> Now the production database defintion is consistent to my development system.
>>
>> Again many thanks to both of you!
>>
>> Best regards
>> Bodo
>
> You can deactivate the option of scripting the collations: Tools | Options | SQL Server Object
> Explorer | Scripting | Include collation -> set to False.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server (French)
>
>
>
Thanks Sylvain,
I thought there must be such option in SSMS, however I can't find this option in Tools-Options-SQL
Server Object Explorer.
I work with MS SSMS 9.0 (2005).

Thanks
Bodo


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: DB1 filegroup backups used to refresh same filegroup on DB2 ?
Next: Cannot connect to Database Engine