November 29, 2008 at 1:41 am
Hi,
I have been installed the SQL Server 2005 x64 on Windows 2003 R2 EE x64 with default collation settings. It is already in production,recently I ran the SQLBPASetup.msi to know the server health. In the report I found the issue about Collation settings that are different for System and User databases. The recommandation, I read is to rebuild system databases.
System databases: collation: SQL_Latin1_General_CP1_CI_AS
User databases:collation: Latin1_General_CI_AS_KS_WS
As it is already in Production I do not want to do this. Could you plz suggest me the method that we can change the user databases collation settings to match as the system databases.
and is this a serious issue to consider?
here is the message:
Warning Set the Collation of User-defined Databases to match those of [master] and [model] Databases SQLDBAVS1\ins1.'ABC' Database on server [SQLDBAVS1\ins1] has collation setting different from 'master' or 'model' databases. We recommend that User-defined databases to have collation matching [master] or [model] Databases.
Thanks
Madhu
November 29, 2008 at 5:04 am
Hi Madhu,
Well it depends on the activity on your server. For instance, when you work with objects in tempdb it can lead to a "Cannot resolve the collation conflict " error.
Good news is that it is dead easy to change the collation of the database:
ALTER DATABASE.... COLLATE
Bad news is that this only affects new objects. Since collation affects how your data is stored, the only way to change it is to script the database, change collation in the script (unless you can do that in the GUI for scripting the database these days), create the new database, BCP your data out, BCP back into the new database.
You can also specify the collation on a per query/per column basis such as this:
SELECT col1 COLLATE Latin1_General_CI_AI FROM myDB..myTable
So I guess you need to compare the effort of changing the collation for the entire database to the effort of specifyinge COLLATE in any query that works with temporary objects (tables, table variables).
"International Features in Microsoft SQL Server 2005", http://msdn.microsoft.com/en-us/library/bb330962.aspx has more information on the subject.
Having said this, your collation on the system databases, SQL_Latin1_General_CP1_CI_AS, is a SQL Collation and Latin1_General_CI_AS_KS_WS is a Windows Collation. SQL Collations should only be used to maintain compatibility with earlier versions SQL Server or applications that were developed using SQL collations. More information in BoL "Collation Types" (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0770f908-abc3-4401-9934-8621118e8335.htm). Personally, I would probably take the onetime cost of rebuilding the system databases ("Rebuilding System Databases", http://msdn.microsoft.com/en-us/library/dd207003.aspx).
HTH
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 30, 2008 at 2:19 pm
install a new instance with the correct collation and transfer any objects across, then remove the original instance. Pay close attention to the installer and select the right collation, check your windows regional seetings as this defines the collation the installer picks for you.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 30, 2008 at 2:57 pm
thank you,
Our environment is Active\Active\passive clustered SQL Server 2005.
The link http://msdn.microsoft.com/en-us/library/dd207003.aspx#Mtps_DropDownFilterText explains for SQL Server 2008. I tried to find out for SQL Server 2005 but I did not get it. So Rebuilding systen databases in SQL Server 2005 and 2008 is same or are there any changes?
In cluster environment do we need to take any special considerations?
Thanks in advance
November 30, 2008 at 4:07 pm
November 30, 2008 at 6:17 pm
could you plz take me to the link in BOL
How to rebuild system databases in SQL Server 2005
thank you
November 30, 2008 at 6:23 pm
Check section named "To rebuild system databases for a default instance of SQL Server 2005 from the command prompt" at http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx
Manu
November 30, 2008 at 7:46 pm
thanks Manu....
In My Case I would like to change only the user databases collation settings from Latin1_General_CI_AS_KS_WS to SQL_Latin1_General_CP1_CI_AS.
System databases: collation: SQL_Latin1_General_CP1_CI_AS
User databases:collation: Latin1_General_CI_AS_KS_WS
We have 3 nodes in the cluster.
Node1, Node3 active and Npde2 passive
Node1 has default instance which has collation settings different for system and user databases.
Node3 has 3 named instances, all have same collation for system databases and user databases as SQL_Latin1_General_CP1_CI_AS.
On node1,initially we had a enterprise evaluation edition,later we upgraded it to enterprise edition from command propmt.Recently, I used SQLBPA,then I realized that we have different collation for system and user databases for the default instance.So, Now I want to chnge the user databases collation settings to match system database's collation SQL_Latin1_General_CP1_CI_AS
For this If I go for rebuild system databases, I guess it is not possible because I would like to change the collation to SQL_Latin1_General_CP1_CI_AS,which is already system databases has.So I need to change for user databases right?
plz advice me
December 1, 2008 at 9:09 am
You don't need to change anything on system database. As somebody posted only change it for user databases one by one.
MJ
December 3, 2008 at 7:13 pm
hi,
Iam planning to do the following steps to change the collation settings of user databases.Corect me if Iam wrong?
1. Script out all user databases .In management studio, Database->tasks->Generate Scripts->In the wizard choose database and script all objects in the selected database->next->In choose script option enable the option called script collation(select TRUE from dropdown) and keep all other option to default values
2.In the generated Script, Iam going to change the collation setting to match the system database collation.
3.Do I need to export all the data from each individual table to a text file using BCP before running the Script that is changed in step 2 and drop the database and create the new database with old database name by choosing the right collation setting and Run the script generated in step2 and finally import the data into the database using BCP.
If the above process is going to resolve my issue plz explain me how to do the export\import of all the data from all tables to atext file and then text file to tables.
OR is there any way to take backups of all user databases,drop them and while restoring can we change the collation settings of the databases to match with system databases?
The instance is already in Production and it is a clustered environment.
Thanks for your suggestions and help
December 4, 2008 at 4:04 am
Hi again,
"1. Script out all user databases .In management studio, Database->tasks->Generate Scripts->In the wizard choose database and script all objects in the selected database->next->In choose script option enable the option called script collation(select TRUE from dropdown) and keep all other option to default values"
-- No, If you are absolutely sure that all tables and all columns should have the SQL_Latin1 collation, choose False otherwise the script will contain the Collation you already have. I think "Script Object Level Permissions" defaults to False so you probably want to change that. You need to check the options one by one (for instance your Compatibility Level is maybe 80 so that should be set to 80).
"2.In the generated Script, I am going to change the collation setting to match the system database collation."
-- OK, you might want to add the COLLATE SQL_Latin1_General_CP1_CI_AS clause to the CREATE DATABASE statement to be on the safe side.
Then also make the following changes:
- Database name is different let's say "NewDatabase"
- The USE DATABASE clause specifies "NewDatabase" (make sure there are no references to "OldDatabase" or whatever the name is of your current database)
- Doublecheck so you don't find any "COLLATE" clause anywhere in the rest of the script
3. Run the script created in step 1 and 2 to create "NewDatabase". Check the collation of the new database.
Now you need to transfer the data from "OldDatabase" to "NewDatabase". Create a package that you can execute later. You need to stop the traffic to Latin1_DB before you execute the package so that no changes are lost.
A. Right-click "OldDatabase" again and choose Tasks/Export Data.
B. Specify
- "OldDatabase" as the source
- "NewDatbase" as the destination
C. In the Specify Table Copy or Query, choose "Copy data from one or more...." and select the "optimize for..."
D. At the end choose to create a package.
Once all data has been transferred, use sp_dbrename to rename "OldDatabase" to something, and then rename the "NewDatabase" to "OldDatabase".
Optionally for step A-D, you can use BCP.. OUT and then BULK INSERT to get the data out and back in.
The data has to go out of the database and then back in because BACKUP/RESTORE does not write data on a row by row basis so it has no means of changing your collation (remember it affects how data is stored in your table).
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
December 6, 2008 at 9:37 pm
Hi,
I followed below steps:
1. Script out the database .In management studio, Database->tasks->Generate Scripts->In the wizard choose database and script all objects in the selected database->next->'In choose script option' page everything is to default values(made no changes) and also tried by setting object_level permissions to True
2.In the generated Script, I changed the collation setting to match the system database collation(SQL_Latin1_General_CP1_CI_AS)
3.Created a new database ABC
4.Execute the script that we have in Step2.
use ABC
Go
execte the script from step2
Results: I got inconsistent result as below
Cannot add rows to sysdepends for the current object because it depends on the missing object 'dbo.proc_MIP_GetNextVersion'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profilesynch_RegisterSiteToSynch'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profile_GetTableNameParts'. The object will still be created.
Warning! The maximum key length is 900 bytes. The index 'IX_DataServicePropMap_ServerIDPropName' has maximum length of 924 bytes. For some combination of large values, the insert/update operation will fail.
Warning! The maximum key length is 900 bytes. The index 'IX_VocValList_1' has maximum length of 8020 bytes. For some combination of large values, the insert/update operation will fail.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'dbo.profile_RemoveUser'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'Profile_sr_UserRecordID'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profile_SyncAlternateLastUpdateWithPrimary'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profile_sr_UpdateBucketInfo'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profile_sr_GetVocValueInfo'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profile_UpdateOrgColleagues'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'DBO.profile_GetDataServicePropMapping'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_MSS_GetConfigurationProperty'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_MSS_QLog_GetSiteId'. The object will still be created.
Msg 1940, Level 16, State 1, Line 2
Cannot create index on view 'dbo.ANLResourceHits'. It does not have a unique clustered index.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profile_UpdateOrgColleagues'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_ar_BumpMetadataObjectVersionCounter'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_ar_BumpMetadataObjectVersionCounter'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_ar_BumpMetadataObjectVersionCounter'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_ar_TypeDescriptorContainsFlagsFixup'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_ar_TypeDescriptorContainsFlagsFixup'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'proc_ar_BumpMetadataObjectVersionCounter'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'dbo.Orgle_job_UpdateLastTouchTime'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'Orgle_sr_UpdateOrgleSiteList'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'Orgle_sr_UpdateReportChain'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'Orgle_RunOrgleRules'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'Orgle_sr_UpdateOrgleSiteList'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'Orgle_sr_UpdateReportChain'. The object will still be created.
Cannot add rows to sysdepends for the current object because it depends on the missing object 'profile_GetViewerRights'. The object will still be created.
Plz advice me
Thanks
December 8, 2008 at 12:32 am
Hi,
As you can see, they are all warnings and they come because the order in which the objects are created. As stated in the Warning output, all objects have been created however the information about dependencies was not entered at the time of creation (if you run sp_dependes on the objects now, the dependencies should all be there).
The only error is
"Msg 1940, Level 16, State 1, Line 2
Cannot create index on view 'dbo.ANLResourceHits'. It does not have a unique clustered index."
Find the part of the script that creates the index on this view and rerun it (the unique clustered index was probably created later on in the script).
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
December 8, 2008 at 4:21 pm
Thank You
I followed the steps we discussed.
1.To avoid this error:Msg 1940, Level 16, State 1, Line 2
Cannot create index on view 'dbo.ANLResourceHits'. It does not have a unique clustered index. I created nonclustered index on view and it went well.
2.sp_depends[dbo.proc_MIP_GetNextVersion]
sp_depends[profilesynch_RegisterSiteToSynch]
sp_depends[profile_GetTableNameParts]
sp_depends[dbo.profile_RemoveUser]
sp_depends[Profile_sr_UserRecordID]
sp_depends[profile_SyncAlternateLastUpdateWithPrimary]
sp_depends[profile_sr_UpdateBucketInfo]
sp_depends[profile_sr_GetVocValueInfo]
sp_depends[profile_UpdateOrgColleagues]
sp_depends[DBO.profile_GetDataServicePropMapping]
sp_depends[proc_MSS_GetConfigurationProperty]
sp_depends[proc_MSS_QLog_GetSiteId]
sp_depends[proc_ar_BumpMetadataObjectVersionCounter]
sp_depends[proc_ar_TypeDescriptorContainsFlagsFixup]
sp_depends[dbo.Orgle_job_UpdateLastTouchTime]
sp_depends[Orgle_sr_UpdateOrgleSiteList]
sp_depends[Orgle_sr_UpdateReportChain]
sp_depends[Orgle_RunOrgleRules]
sp_depends[profile_GetViewerRights]
3.Export data from old database new database, getting the below error:
ITLE: Microsoft.SqlServer.DtsTransferProvider
------------------------------
ERROR : errorCode=-1073548784 description=Executing the query "CREATE TABLE [Test7].[dbo].[ANLHitsByResourceByDay] (
[ResourceId] int NOT NULL,
[SiteGuid] uniqueidentifier NOT NULL,
[WebGuid] uniqueidentifier NOT NULL,
[DocName] nvarchar(260),
[FullUrl] nvarchar(260) NOT NULL,
[DayId] int NOT NULL,
[HitCount] bigint
)
" failed with the following error: "There is already an object named 'ANLHitsByResourceByDay' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
Thank you
December 9, 2008 at 3:26 am
Hi,
Is ANLHitsByResourceByDay the first of all tables listed in the Import and Export Wizard or does some objects go through OK and then it stops? There appear to be some known issues with the same error number but I am not sure how to get around it with the wizard. One way is of course to use BCP to get the data in and then BULK INSERT to get it into the new database. Or even INSERT INTO.... SELECT.
Which build of SQL Server are you running (SELECT @@version)? It might be a good idea to get on the latest CU which I believe is CU 9 (build 2005.90.3282.0, http://support.microsoft.com/default.aspx/kb/953752).
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply