RE: Collation and Win2K3 Clustered Environment.
From: Bart Duncan [MSFT] (bartd_at_online.microsoft.com)
Date: 02/23/05
- Next message: Calvin Do: "Re: Using SQL Mail in a cluster"
- Previous message: Mike Epprecht \(SQL MVP\): "Re: Change from Active\Active to Active\Passive Cluster"
- In reply to: MikeH: "Collation and Win2K3 Clustered Environment."
- Next in thread: MikeH: "RE: Collation and Win2K3 Clustered Environment."
- Reply: MikeH: "RE: Collation and Win2K3 Clustered Environment."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 23 Feb 2005 21:43:13 GMT
If the collation names are different you'll get the collation conflict
error -- a difference in accent sensitivity is sufficient to expose the
problem.
One option is rebuild master in dev or test (again) so that the two servers
have the same collation. You almost matched the collation last time, but
the different accent sensitivity setting is also critical.
Another option is to make sure that the T-SQL is written in a way that
makes it immune to the problem. For the scenario you describe you could do
this by making sure that your temp tables inherit the collation of the
current user database, not the collation of tempdb. A "COLLATE
database_default" clause will accomplish this. For example, when creating
the temp table in the stored proc:
CREATE TABLE #temp1 (
c1 int,
c2 varchar (30) COLLATE database_default,
c3 char(12) COLLATE database_default,
)
HTH,
Bart
------------
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Thread-Topic: Collation and Win2K3 Clustered Environment.
| thread-index: AcUUNUWE2BoB7xCnSeWo8pOsRpOhYg==
| X-WBNR-Posting-Host: 12.227.130.93
| From: "=?Utf-8?B?TWlrZUg=?=" <MikeH@discussions.microsoft.com>
| Subject: Collation and Win2K3 Clustered Environment.
| Date: Wed, 16 Feb 2005 06:39:08 -0800
| Lines: 51
| Message-ID: <87A19939-8444-4CEC-BBB4-ED092DFFD4D7@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clustering
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.clustering:3114
| X-Tomcat-NG: microsoft.public.sqlserver.clustering
|
| This past weekend I was challenged a little in resolving what I hoped was
a
| simple collation issue. We are running Win2K3 Ent. Clustering Services,
SQL
| Server 2000 Enterprise, b.8.00.760.
|
| On Friday, 2.11, a user migrated an application to this new clustered
setup
| and immediately received the infamous 446 collation error.
|
| I spent most of Friday evening and all day Saturday reviewing support
topics
| and news groups. I found that there was 1 issue that could be affecting
us -
| the build of SQL Server for Win2K3 Ent. Clustering is subtly different
than
| for Win2KAS Clustering - and all of my research came back to one thing:
Run
| rebuildm.exe and set the collation of this instance to be the same as the
| existing development/production environment. The default setup on the
cluster
| was different than the previous production setup, as well as different
than
| the current development environment.
|
| So, I backed everyone's database up, then the master objects, shut the
| instance down, executed rebuild and set the collation to what I needed it
to
| be.
|
| When I brought this user's database online - the same exact error occurs.
|
| Prior to doing this, here is what we were faced with:
| Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
| Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
|
| The new clustered SQL=SQL Server Enterprise,
w/Collate=Latin1_General_CI_AI.
|
| The database/application in question, at the procedure where we receive
the
| collation error, basically calls a function that creates a temp table,
then
| another that pulls data for a report - however, the data is collected as
a
| JOIN on this tempdb temp object and a series of Views the programmer
| previously setup. These views in turn hit various other static tables as
well.
|
| We were thinking that because of the collation difference on the new
Win2K3
| setup, as well as Collation/Locale settings differences with Win2K3
| Clustering, we should at a minimum change the instance to match the
| SQL_Latin1, etc. collation of development. This however, has not solved
the
| problem.
|
| At this juncture, the Win2K3 Clustered server has the collation
| SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
| their current production environment, which is 2KAS nonclustered) is
| SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now
is
| the accent sensitivity - but this should not be the issue.
|
| If anyone else out there has any feedback, I'd be grateful for your time.
|
| Thanks....
|
| mhamilton"AT"nusoftsolutions"DOT"com
|
- Next message: Calvin Do: "Re: Using SQL Mail in a cluster"
- Previous message: Mike Epprecht \(SQL MVP\): "Re: Change from Active\Active to Active\Passive Cluster"
- In reply to: MikeH: "Collation and Win2K3 Clustered Environment."
- Next in thread: MikeH: "RE: Collation and Win2K3 Clustered Environment."
- Reply: MikeH: "RE: Collation and Win2K3 Clustered Environment."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
