Hi,
If I have a database with collation Sequence X and I change the
collation sequence of database to Collation Sequence Y , do I have to
migrate the data of tables with collation Sequence X to collation
Sequence Y or SQl server takes care of migrating the data internally.
Thanks in advance.
-Kalyan
Scenario
Recently moved a SQL2000 database to SQL2005.
Collation on SQL 2000 database server was SQL_Latin1_General_CP1_CI_AS
Colaltion on SQL 2005 database server is Latin1_General_CI_AS
After restoring the SQL 2000 database on SQL 2005 we observed that the database collation was SQL_Latin1_General_CP1_CI_AS. We have altered the database collation to Latin1_General_CI_AS. We understand this will work for all new objects created on the database but existing objects (prior to the collation change) will continue to have the 'SQL_Latin1_General_CP1_CI_AS' collation.
What is the best method to change the collation of the older columns to 'Latin1_General_CI_AS' -
1) Run ALTER TABLE ALTER COLUMN <colname> <datatype> COLLATE Latin1_General_CI_AS <nullability>
on each varchar/char column
or
2) For each table
BCP out data
DROP TABLE
RECREATE TABLE (taking the new collation Latin1_General_CI_AS)
BCP in data
3) Other methods?
Thanks in advance
David.
I changed the default collation of a database and every table within that except sysDiagrams , which I can't even through the designer .
View 9 Replies View RelatedWe ran into weird/interesting issue with below details.
Version:Â Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)Â Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
Hi. Wondered if I might get a bit of advice on a minor collation problem I've experienced after migrating a database form SQL Server 2000 to 2005?
Users reported an error appearing in pages in a web-based application and I traced the problem to a stored procedure that was using a temporary table. I retained the original collation - SQL_Latin1_General_CP1_CI_AS - after migration and the error showing up was 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.' This makes sense as I guess temporary tables are assuming tempdb's collation. Resolution in this case was fairly simple, which was to apply an explicit collation to one of the columns in the temporary table (it only had three columns, only one requiring the collation). However the longer-term question is should I: (a) resolve similar issues in the same way as and when they arise; (b) change the collation of the migrated database to that of the default for system and new databases under 2005 - i.e. Latin1_General_CI_AS; or (b) change the collation of tempdb and the other system databases to match that of the migrated databases (sounds risky)?
I would probably go for (a) or perhaps (b) but could use some expert advise on the matter.
Regards,
YaHozna.
Hi.
I had an SQL 2000 database with Turkish collation. (Note: Default database collation was Latin)
Today i transfered data from that to SQL 2005 with Latin collation.
There is no problem with old data. I transfered them correctly. But when i insert/update at new database, Turkish characters change to Latin. (Like i->i, g->g)
When i put an N before the value, it works.
For example INSERT INTO T1 (A) VALUES (N'A')
Must i put N to all of them? I think not. What can be the solution?
Note: Database for Snitz Forums like here.
Hi,
I'm currently working with a SQLServer instance that uses a case insensitive collation. In this instance we have a number of user databases that are created without COLLATE and hence 'inherits' the same collation as the SQL Server is installed with.
I have searched the forum and understand that I need to rebuild the master db in order to change the 'default' collation. However, I do not need to change the collation on the user databases that exist. I would just like to make sure that all new databases are created using the new collation. Can I rebuild the master database without loosing my existing user databases? And if so, will they run OK using case insensitive collation, if the master is using case sensitive?
Thanks in advance
//Roger
Its not dark yet, but it's getting there...
Is there any TSQL statement that I can run to change my collation from:
SQL_Latin_General_CP1_CS_AS to SQL_Latin_General_CP1_CI_AS
I rather not have to recreate the databases if i can avoid it.
Thanks all in advance.
I created a new server registration under my SQL Server Group. After creating the registration via EM, I looked under properties and the collation is SQL_Latin1_General_CP437_CI_AS. It needs to be Latin1_General_BIN because this is the collation order in the production server. Please bear in mind that I am not a DBA and this is all new to me, but unfortunately I'm the one who has to fix this. Please keep the terms simple in any response. Thanks.
hi all... how can i change the collation of a table that i dynamically created during runtime??i wanna change the default collation to Arabic
View 2 Replies View RelatedSQL Server 2000: Is it possible to change SQL server's default collation without reinstall ?
View 3 Replies View RelatedIs there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.
View 2 Replies View RelatedProblem is, the database collation is "SQL_Latin1_General_CP1_CI_AS" and some fields of some tables have the collation "Latin1_General_CI_AS". You know the Problem ... ;-)
As I don't want to change the columns one by one, I want to ask how can I change collation of all column in a table by 1 Transact SQL? Or how can I change the collation of all the tables?
Thanks, ON
i'm getting an error
[Microsoft][SQL Native Client][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Hebrew_CI_AS" in the equal to operation.
How can I change these tables to have the saem collation? I did not purposely make it different.
Hi Guys is there a way to change the Collation after a SQL Server has been installed.
Its currently set to.
Latin1_General_CI_AS_KS_WS
Thanks in advance.
Hi,
I'm using SQl Server 2005 and I need to know how can I change all table's column collation to the database default....It's too $%¨& to enter table by table and check if it's with other collate and then change...
Thanks
The title says it all. I've used ALTER DATABASE, and ALTER TABLE...ALTER COLUMN to change all my character fields from 'Compatibility_42_409_30003' to the default I want ('Latin1_General_CI_AI') on databases converted from SQL7. Now I just have these ntext fields to change...
Al
There have been several threads about changing a database's collation but none have come up with an easy answer before.
The suggestion before was to create an empty database with the correct collation and then copy the data across.
However this is hard work as you have to populate tables in a specific order in order not to violate foreign keys etc. You can't just dts the whole data.
There follows scripts we have written to do the job. If people use them, please could you add to this thread whether they worked successfully or not.
Firstly we change the default collation, then change all the types in the database to match the new collation.
===================
--script to change database collation - James Agnini
--
--Replace <DATABASE> with the database name
--Replace <COLLATION> with the collation, eg SQL_Latin1_General_CP1_CI_AS
--
--After running this script, run the script to rebuild all indexes
ALTER DATABASE <DATABASE> COLLATE <COLLATION>
exec sp_configure 'allow updates',1
go
reconfigure with override
go
update syscolumns
set collationid = (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
where collationid <> (select top 1 collationid from systypes where systypes.xtype=syscolumns.xtype)
go
exec sp_configure 'allow updates',0
go
reconfigure with override
go
===================
As we have directly edited system tables, we need to run a script to rebuild all the indexes. Otherwise you will get strange results like comparing strings in different table not working.
The indexes have to actually be dropped and recreated in separate statements.
You can't use DBCC DBREINDEX or create index with the DROP_EXISTING option as they won't do anything(thanks to SQL Server "optimization").
This script loops through the tables and then loops through the indexes and unique constraints in separate sections. It gets the index information and drops and re-creates it.
(The script could probably be tidied up with the duplicate code put into a stored procedure).
====================
--Script to rebuild all table indexes, Version 0.1, May 2004 - James Agnini
--
--Database backups should be made before running any set of scripts that update databases.
--All users should be out of the database before running this script
print 'Rebuilding indexes for all tables:'
go
DECLARE @Table_Name varchar(128)
declare @Index_Name varchar(128)
declare @IndexId int
declare @IndexKey int
DECLARE Table_Cursor CURSOR FOR
select TABLE_NAME from INFORMATION_SCHEMA.tables where table_type != 'VIEW'
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor
INTO @Table_Name
--loop through tables
WHILE @@FETCH_STATUS = 0
BEGIN
print ''
print @Table_Name
DECLARE Index_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
not exists(Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @Table_Name AND (CONSTRAINT_TYPE = 'PRIMARY KEY' or CONSTRAINT_TYPE = 'UNIQUE') and
CONSTRAINT_NAME = name)
order by indid
OPEN Index_Cursor
FETCH NEXT FROM Index_Cursor
INTO @IndexId, @Index_Name
--loop through indexes
WHILE @@FETCH_STATUS = 0
begin
declare @SQL_String varchar(256)
set @SQL_String = 'drop index '
set @SQL_String = @SQL_String + @Table_Name + '.' + @Index_Name
set @SQL_String = @SQL_String + ';create '
if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsUnique')) =1)
set @SQL_String = @SQL_String + 'unique '
if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsClustered')) =1)
set @SQL_String = @SQL_String + 'clustered '
set @SQL_String = @SQL_String + 'index '
set @SQL_String = @SQL_String + @Index_Name
set @SQL_String = @SQL_String + ' on '
set @SQL_String = @SQL_String + @Table_Name
set @SQL_String = @SQL_String + '('
--form column list
SET @IndexKey = 1
-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @IndexKey <= 16 and INDEX_COL(@Table_Name, @IndexId, @IndexKey)
IS NOT NULL
BEGIN
IF @IndexKey != 1
set @SQL_String = @SQL_String + ','
set @SQL_String = @SQL_String + index_col(@Table_Name, @IndexId, @IndexKey)
SET @IndexKey = @IndexKey + 1
END
set @SQL_String = @SQL_String + ')'
print @SQL_String
EXEC (@SQL_String)
FETCH NEXT FROM Index_Cursor
INTO @IndexId, @Index_Name
end
CLOSE Index_Cursor
DEALLOCATE Index_Cursor
--loop through unique constraints
DECLARE Contraint_Cursor CURSOR FOR
select indid, name from sysindexes
where id = OBJECT_ID(@Table_Name) and indid > 0 and indid < 255 and (status & 64)=0 and
exists(Select top 1 NULL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where TABLE_NAME = @Table_Name AND CONSTRAINT_TYPE = 'UNIQUE' and CONSTRAINT_NAME = name)
order by indid
OPEN Contraint_Cursor
FETCH NEXT FROM Contraint_Cursor
INTO @IndexId, @Index_Name
--loop through indexes
WHILE @@FETCH_STATUS = 0
begin
set @SQL_String = 'alter table '
set @SQL_String = @SQL_String + @Table_Name
set @SQL_String = @SQL_String + ' drop constraint '
set @SQL_String = @SQL_String + @Index_Name
set @SQL_String = @SQL_String + '; alter table '
set @SQL_String = @SQL_String + @Table_Name
set @SQL_String = @SQL_String + ' WITH NOCHECK add constraint '
set @SQL_String = @SQL_String + @Index_Name
set @SQL_String = @SQL_String + ' unique '
if( (select INDEXPROPERTY ( OBJECT_ID(@Table_Name) , @Index_Name , 'IsClustered')) =1)
set @SQL_String = @SQL_String + 'clustered '
set @SQL_String = @SQL_String + '('
--form column list
SET @IndexKey = 1
-- Loop through index columns, INDEX_COL can be from 1 to 16.
WHILE @IndexKey <= 16 and INDEX_COL(@Table_Name, @IndexId, @IndexKey)
IS NOT NULL
BEGIN
IF @IndexKey != 1
set @SQL_String = @SQL_String + ','
set @SQL_String = @SQL_String + index_col(@Table_Name, @IndexId, @IndexKey)
SET @IndexKey = @IndexKey + 1
END
set @SQL_String = @SQL_String + ')'
print @SQL_String
EXEC (@SQL_String)
FETCH NEXT FROM Contraint_Cursor
INTO @IndexId, @Index_Name
end
CLOSE Contraint_Cursor
DEALLOCATE Contraint_Cursor
FETCH NEXT FROM Table_Cursor
INTO @Table_Name
end
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
print ''
print 'Finished, Please check output for errors.'
====================
Any comments are very welcome.
Dear All,
I am facing one problem when I am trying to create some stored procedures,
Msg 468, Level 16, State 9, Procedure SG_GET_USER_SECTOR_POSITION, Line 11
Cannot resolve the collation conflict between "Arabic_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure SG_GETRANKLIST, Line 4
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Arabic_CI_AS" in the equal to operation.
I need to change these collation settings, please help me how to change them.
thanks & regards,
Shahbaz.
Pls, any one help me regarding the how to change the collation name of the server in SQL server 2005.
In SQL server 2000 we can do with rebuildm.exe, like that is there any one.
Thanks in advance
I am trying to change the "Server collation" for the all SQL 2000 server without uninstall SQL Server 2000 and reinstall it.
Is there any way for it or not. Thanks.
HiWhen we installed SqlServer2000 we left the default collation name(Sql_Latin1_General_CPI_CI_AS).The user defined databases we created afterwards were defined with adifferent collation name in order to be able to accept the character setwe use, Hebrew.We are looking into switching DTSs that we use to copy data from ourmain system , that uses an Ingres database, into OSQL scripts. Althoughthe DTSs successfully copy the Hebrew letters when I copy data with OSQLit comes over as jibberish.After looking into the matter I came to the conclusion that while DTSrefers to specific databases and uses the destination database'scollation name , OSQL refers to the remote server and destination serverand therefore uses the collation name of the server and not of thedatabase. In order for it to successfully copy the Hebrew I need tochange the default collation name of the installation.Is "rebuild master" the way to do such a thing ? (this is a productionserver so we are wary of doing a "rebuild master")Has anyone else run into similar problems when transfering data betweenservers using OSQL ?ThanksDavid Greenberg
View 3 Replies View RelatedThe default collation for SSISDB is SQL_Latin1_General_CP1_CI_AS. I had a question from a client today who asked if its possible to change it to French_CI_AS as they have issues with importing data from flat files using SSIS package. The issue they are facing is with different french characters and also numeric values with a scale... All their "user" databases are French_CI_AS as is the Instance collation.I'm guessing its not supported to change the collation of SSISDB as when i attempted on a "test" instance i get.
The object 'dm_execution_performance_counters' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.The object 'get_database_principals' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'CK_Folder_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'CK_Project_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'CK_Environment_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.The object 'CK_Operation_PermissionType' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.ALTER DATABASE failed. The default collation of database 'SSISDB' cannot be set to French_CI_AS. (Microsoft SQL Server, Error: 507
If its not possible to change the collation of SSISDB, Adding collate statement to each join in the ssis package??I cant find anything in documentation about this but maybe overlooked something...Â
Hi
My table’s data is deleted by unknown person.
I can detect some thing about it i.e. user name, time, etc of this event?
Is there any way to rollback changes though logs?
Wishes
Jawad
I use Chinese Windows XP+SQL 2005 express+Microsoft Visual Web Developer 2005 Express Edition
In aspnet_Profile table , the Collation of PropertyValuesString field is <database default>
I hope to replace <database default> with SQL_Latin1_General_CP1_CI_AS ,but I get the following error, why?
The following error was encountered while changing the collation:
Setting for Length must be from 16 to 16.
Our SQL Server 2008 r2 has collation Latin general.
And my database 'DB1' , 'DB2' has collation set to Japanese unicode.
My sql team has informed they cannot change system collation as it hosts other db's as well.
My Query: What will be the impact of changing collation from JP to Latin.
I need a SQL Server 2005 SP2 instance with a collation of SQL_Latin1_General_CP850_BIN2. After installing SP2, the collation is SQL_Latin1_General_CP850_BIN, and none of the patches I can find seem to want to work (they are all apparently written for SQL Server 2000).
Any assistance is greatly appreciated.
I use this command to change collation on SQL 2005 cluster.
start /wait <CD or DVD Drive>setup.exe /qn VS=<VSName> INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine SKUUPGRADE=1 REBUILDDATABASE=1 ADMINPASSWORD=<StrongPassword> SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation> SQLACCOUNT=<domainuser> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domainuser> AGTPASSWORD=<DomainUserPassword>
The rebuild master database failed with the following message. Please help.
Microsoft SQL Server 2005 Setup beginning at Tue Oct 23 07:32:48 2007
Process ID : 3112
C:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe Version: 2005.90.3042.0
Running: LoadResourcesAction at: 2007/9/23 7:32:48
Complete: LoadResourcesAction at: 2007/9/23 7:32:48, returned true
Running: ParseBootstrapOptionsAction at: 2007/9/23 7:32:48
Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapxmlrw.dll Version:2.0.3609.0
Complete: ParseBootstrapOptionsAction at: 2007/9/23 7:32:48, returned true
Running: ValidateWinNTAction at: 2007/9/23 7:32:48
Complete: ValidateWinNTAction at: 2007/9/23 7:32:48, returned true
Running: ValidateMinOSAction at: 2007/9/23 7:32:48
Complete: ValidateMinOSAction at: 2007/9/23 7:32:48, returned true
Running: PerformSCCAction at: 2007/9/23 7:32:48
Complete: PerformSCCAction at: 2007/9/23 7:32:48, returned true
Running: ActivateLoggingAction at: 2007/9/23 7:32:48
Complete: ActivateLoggingAction at: 2007/9/23 7:32:48, returned true
Running: DetectPatchedBootstrapAction at: 2007/9/23 7:32:48
Complete: DetectPatchedBootstrapAction at: 2007/9/23 7:32:48, returned true
Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchPatchedBootstrap__3112" returned false.
Running: PerformSCCAction2 at: 2007/9/23 7:32:48
Complete: PerformSCCAction2 at: 2007/9/23 7:32:48, returned true
Running: PerformDotNetCheck at: 2007/9/23 7:32:48
Complete: PerformDotNetCheck at: 2007/9/23 7:32:48, returned true
Running: ComponentUpdateAction at: 2007/9/23 7:32:48
Complete: ComponentUpdateAction at: 2007/9/23 7:32:49, returned true
Running: DetectLocalBootstrapAction at: 2007/9/23 7:32:49
Complete: DetectLocalBootstrapAction at: 2007/9/23 7:32:49, returned true
Action "LaunchLocalBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchLocalBootstrap__3112" returned false.
Running: PerformDotNetCheck2 at: 2007/9/23 7:32:49
Complete: PerformDotNetCheck2 at: 2007/9/23 7:32:49, returned true
Running: InvokeSqlSetupDllAction at: 2007/9/23 7:32:49
Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlspars.dll Version:2005.90.3042.0
<Func Name='DwLaunchMsiExec'>
Examining 'sqlspars' globals to initialize 'SetupStateScope'
Opening 'MachineConfigScope' for [DSQL1N1]
Trying to find Product Code from command line or passed transform
If possible, determine install id and type
Trying to find Instance Name from command line.
Instance Name = DC1
Trying to find install through Instance Name
Install Type = 1
If possible, determine action
*******************************************
Setup Consistency Check Report for Machine: DSQL1N1
*******************************************
Article: WMI Service Requirement, Result: CheckPassed
Article: MSXML Requirement, Result: CheckPassed
Article: Operating System Minimum Level Requirement, Result: CheckPassed
Article: Operating System Service Pack Level Requirement, Result: CheckPassed
Article: SQL Compatibility With Operating System, Result: CheckPassed
Article: Minimum Hardware Requirement, Result: CheckPassed
Article: IIS Feature Requirement, Result: CheckPassed
Article: Pending Reboot Requirement, Result: CheckPassed
Article: Performance Monitor Counter Requirement, Result: CheckPassed
Article: Default Installation Path Permission Requirement, Result: CheckPassed
Article: Internet Explorer Requirement, Result: CheckPassed
Article: Check COM+ Catalogue, Result: CheckPassed
Article: ASP.Net Registration Requirement, Result: CheckPassed
Article: Minimum MDAC Version Requirement, Result: CheckPassed
Article: Edition Upgrade Check, Result: CheckPassed
<Func Name='PerformDetections'>
0
Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.3042.0
<EndFunc Name='PerformDetections' Return='0' GetLastError='997'>
*******************************************
Setup Consistency Check Report for Machine: DSQL1N1
*******************************************
Article: WMI Service Requirement, Result: CheckPassed
Article: MSXML Requirement, Result: CheckPassed
Article: Operating System Minimum Level Requirement, Result: CheckPassed
Article: Operating System Service Pack Level Requirement, Result: CheckPassed
Article: SQL Compatibility With Operating System, Result: CheckPassed
Article: Minimum Hardware Requirement, Result: CheckPassed
Article: IIS Feature Requirement, Result: CheckPassed
Article: Pending Reboot Requirement, Result: CheckPassed
Article: Performance Monitor Counter Requirement, Result: CheckPassed
Article: Default Installation Path Permission Requirement, Result: CheckPassed
Article: Internet Explorer Requirement, Result: CheckPassed
Article: Check COM+ Catalogue, Result: CheckPassed
Article: ASP.Net Registration Requirement, Result: CheckPassed
Article: Minimum MDAC Version Requirement, Result: CheckPassed
Article: Edition Upgrade Check, Result: CheckPassed
*******************************************
Setup Consistency Check Report for Machine: DSQL1N2
*******************************************
Article: WMI Service Requirement, Result: CheckPassed
Article: MSXML Requirement, Result: CheckPassed
Article: Operating System Minimum Level Requirement, Result: CheckPassed
Article: Operating System Service Pack Level Requirement, Result: CheckPassed
Article: SQL Compatibility With Operating System, Result: CheckPassed
Article: Minimum Hardware Requirement, Result: CheckPassed
Article: IIS Feature Requirement, Result: CheckPassed
Article: Administrative Shares Requirement, Result: CheckPassed
Article: Pending Reboot Requirement, Result: CheckPassed
Article: Performance Monitor Counter Requirement, Result: CheckPassed
Article: Default Installation Path Permission Requirement, Result: CheckPassed
Article: Internet Explorer Requirement, Result: CheckPassed
Article: Check COM+ Catalogue, Result: CheckPassed
Article: ASP.Net Registration Requirement, Result: CheckPassed
Article: Minimum MDAC Version Requirement, Result: CheckPassed
Article: Edition Upgrade Check, Result: CheckPassed
The CheckSCCResult returned for cluster install is 0
Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='0' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/9/23 7:33:41, returned true
Running: SetPackageInstallStateAction at: 2007/9/23 7:33:41
Complete: SetPackageInstallStateAction at: 2007/9/23 7:33:43, returned true
Running: DeterminePackageTransformsAction at: 2007/9/23 7:33:43
Complete: DeterminePackageTransformsAction at: 2007/9/23 7:33:43, returned true
Running: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43
Complete: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43, returned true
Running: OpenPipeAction at: 2007/9/23 7:33:43
Complete: OpenPipeAction at: 2007/9/23 7:33:43, returned false
Error: Action "OpenPipeAction" failed during execution.
Running: CreatePipeAction at: 2007/9/23 7:33:43
Complete: CreatePipeAction at: 2007/9/23 7:33:43, returned true
Running: RunRemoteSetupAction at: 2007/9/23 7:33:43
<Func Name='CProcessCtrl::GetInstallPath'>
<EndFunc Name='CProcessCtrl::GetInstallPath' Return='0' GetLastError='0'>
Error: 0x80070050 TaskScheduler::NewWorkItem for SQL Server Remote Setup
Error: 0x80070005 TaskSchedulerWorkItem failed to save the task [SQL Server Remote Setup ]
Complete: RunRemoteSetupAction at: 2007/9/23 7:33:43, returned false
Error: Action "RunRemoteSetupAction" failed during execution. Error information reported during run:
Attempting to determine log files for remote install.
Connection to remote computer's scheduler service.
Creating new workitem.
Deleting existing work item and trying again...
Starting remote setup onSQL1N2
Error: 80070005 Access is denied.
Running: PopulateMutatorDbAction at: 2007/9/23 7:33:43
Complete: PopulateMutatorDbAction at: 2007/9/23 7:33:43, returned true
Running: GenerateRequestsAction at: 2007/9/23 7:33:43
SQL_Engine = 3
SQL_Data_Files = -1
SQL_Replication = -1
SQL_FullText = -1
SQL_SharedTools = -1
SQL_BC_DEP = -1
Analysis_Server = -1
AnalysisDataFiles = -1
AnalysisSharedTools = -1
RS_Server = -1
RS_Web_Interface = -1
RS_SharedTools = -1
Notification_Services = -1
NS_Engine = -1
NS_Client = -1
SQL_DTS = -1
Client_Components = -1
Connectivity = -1
SQL_Tools90 = -1
SQL_WarehouseDevWorkbench = -1
SDK = -1
SQLXML = -1
Tools_Legacy = -1
TOOLS_BC_DEP = -1
SQL_SSMSEE = -1
SQL_Documentation = -1
SQL_BooksOnline = -1
SQL_DatabaseSamples = -1
SQL_AdventureWorksSamples = -1
SQL_AdventureWorksDWSamples = -1
SQL_AdventureWorksASSamples = -1
SQL_Samples = -1
Complete: GenerateRequestsAction at: 2007/9/23 7:33:44, returned true
Running: CreateProgressWindowAction at: 2007/9/23 7:33:44
Complete: CreateProgressWindowAction at: 2007/9/23 7:33:44, returned false
Error: Action "CreateProgressWindowAction" failed during execution.
Running: ScheduleActionAction at: 2007/9/23 7:33:44
Complete: ScheduleActionAction at: 2007/9/23 7:33:45, returned true
Skipped: InstallASAction.11
Waiting for actions from remote setup(s)
Breaking wait state and aborting package due to cancel code received: 1602
Remote setup(s) are ready
Notify package action is determined: 1602
Error Code: 0x800700e9 (233)
Windows Error Text: No process is on the other end of the pipe.
Source File Name: remotemessageliboverlappedpipelistener.cpp
Compiler Timestamp: Sat Oct 7 09:43:54 2006
Function Name: sqls:verlappedPipeListener::writePipe
Source Line Number: 294
Notification failed to send.
---- Context -----------------------------------------------
sqls::HostSetupPackageInstallerSynch::installAction
Removing machine from list of targets to sync.
Skipped: Action "InstallASAction.11" was not run. Information reported during analysis:
All installs have been cancelled, so package: "sqlsupport", referred by package: "as", will not be installed.
Skipped: InstallASAction.18
Skipped: Action "InstallASAction.18" was not run. Information reported during analysis:
All installs have been cancelled, so package: "owc11", referred by package: "as", will not be installed.
Skipped: InstallASAction.22
Skipped: Action "InstallASAction.22" was not run. Information reported during analysis:
All installs have been cancelled, so package: "bcRedist", referred by package: "as", will not be installed.
Skipped: InstallASAction.9
Skipped: Action "InstallASAction.9" was not run. Information reported during analysis:
All installs have been cancelled, so package: "msxml6", referred by package: "as", will not be installed.
Skipped: InstallDTSAction
Skipped: Action "InstallDTSAction" was not run. Information reported during analysis:
All installs have been cancelled, so package: "dts", will not be installed.
Skipped: InstallDTSAction.11
Skipped: Action "InstallDTSAction.11" was not run. Information reported during analysis:
All installs have been cancelled, so package: "sqlsupport", referred by package: "dts", will not be installed.
Skipped: InstallDTSAction.12
Skipped: Action "InstallDTSAction.12" was not run. Information reported during analysis:
All installs have been cancelled, so package: "sqlncli", referred by package: "dts", will not be installed.
Skipped: InstallDTSAction.18
Skipped: Action "InstallDTSAction.18" was not run. Information reported during analysis:
All installs have been cancelled, so package: "owc11", referred by package: "dts", will not be installed.
Skipped: InstallDTSAction.22
Skipped: Action "InstallDTSAction.22" was not run. Information reported during analysis:
All installs have been cancelled, so package: "bcRedist", referred by package: "dts", will not be installed.
Skipped: InstallDTSAction.9
Skipped: Action "InstallDTSAction.9" was not run. Information reported during analysis:
All installs have been cancelled, so package: "msxml6", referred by package: "dts", will not be installed.
Skipped: InstallNSAction
Skipped: Action "InstallNSAction" was not run. Information reported during analysis:
All installs have been cancelled, so package: "ns", will not be installed.
Skipped: InstallNSAction.11
Skipped: Action "InstallNSAction.11" was not run. Information reported during analysis:
All installs have been cancelled, so package: "sqlsupport", referred by package: "ns", will not be installed.
Skipped: InstallNSAction.12
Skipped: Action "InstallNSAction.12" was not run. Information reported during analysis:
All installs have been cancelled, so package: "sqlncli", referred by package: "ns", will not be installed.
Skipped: InstallNSAction.18
Skipped: Action "InstallNSAction.18" was not run. Information reported during analysis:
All installs have been cancelled, so package: "owc11", referred by package: "ns", will not be installed.
Skipped: InstallNSAction.22
Skipped: Action "InstallNSAction.22" was not run. Information reported during analysis:
All installs have been cancelled, so package: "bcRedist", referred by package: "ns", will not be installed.
Skipped: InstallNSAction.9
Skipped: Action "InstallNSAction.9" was not run. Information reported during analysis:
All installs have been cancelled, so package: "msxml6", referred by package: "ns", will not be installed.
Skipped: InstallRSAction.11
Skipped: Action "InstallRSAction.11" was not run. Information reported during analysis:
All installs have been cancelled, so package: "sqlsupport", referred by package: "rs", will not be installed.
Skipped: InstallRSAction.18
Skipped: Action "InstallRSAction.18" was not run. Information reported during analysis:
All installs have been cancelled, so package: "owc11", referred by package: "rs", will not be installed.
Skipped: InstallRSAction.22
Skipped: Action "InstallRSAction.22" was not run. Information reported during analysis:
All installs have been cancelled, so package: "bcRedist", referred by package: "rs", will not be installed.
Skipped: InstallSqlAction
Clustered feature detected: SQL_Engine
Clustered feature detected: SQL_FullText
Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.3042.0
Windows Error Text: User cancelled installation.
Source File Name: sqlchainingsqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:32:00 2006
Function Name: sqls::ReportChainingResults:erform
Source Line Number: 3667
---- Context -----------------------------------------------
sqls::RunRemoteSetupAction::waitForRemoteSetupComplete
king package: "patchRS2000" as failed due to cancel code received from cancel source: 1602
sqls
Delay load of action "UploadDrWatsonLogAction" returned nothing. No action will occur as a result.
Message pump returning: 1602
Hello,
I've restored a SQL Server 2000 database with a Latin1_General_BIN collation from a .dmp file to a SQL Server 2005 server with a default collation of SQL_Latin1_General_CP1_CI_AS. When I try to change the database collation I get hundreds of the following error:
The object 'CK_PM10200_GLPOSTD_00AF8CF' is dependent on database collation. So, in this case, is it even possible to change the collation if there are objects in the database that are dependent on it?
Thanks,
Bruce
I am currently in the process of migrating data from Sybase to Sql server and would like to know how to test the data migrated.
As of now, we took one table data from both source and destination and compared it in Excel to check if the data migrated looks good (note, we used SSIS to migrate data). However, I would like to check if there are any other best & easy ways to apprach data validation post migration.
I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).
I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)
An example of my branch script :
select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
I am trying to migrate our Portals database from SQL2000 to SQL2005, but I received "SQL Type Variant Data" error during the data migration with some database. Can anyone help me with this?
Thanks,
Jay
Hello,
I'm not so new on SQL Server 2005 but i'd rather be sure on this.
I recently dumped an SQL Server 2000 database (French_CI_AS collation) and loaded it into an SQL Server 2005 instance (Latin1_General_CI_AS collation). To make things easier, we'll call this database CLIENTBOOKS. Of course CLIENTBOOKS as well as all the text columns (char, nchar, varchar, nvarchar, text, ntext) kept their original collation French_CI_AS. So I changed the database collation first using:
ALTER DATABASE clientbooks COLLATE Latin1_General_CI_AS
GO
Then after that i dropped all indexes concerned in these tables (there were only 4), i changed all table columns' collations using the following script (except for tables dbo.dtproperties and dbo.sysdiagrams):
DECLARE @@TableName NVARCHAR(100)
DECLARE @@ColumnName NVARCHAR(100)
DECLARE @@ColumnType NVARCHAR(100)
DECLARE @@ColumnLengh FLOAT
DECLARE @@SQL NVARCHAR(1000)
DECLARE @@IsNullAble NVARCHAR(50)
PRINT 'ALTERING TABLE COLUMNS...'
DECLARE my_cursor CURSOR FOR
SELECT sysobjects.name FROM sysobjects WHERE xtype='u' AND sysobjects.name <> 'sysdiagrams' AND sysobjects.name <> 'dtproperties'
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE my_column CURSOR FOR
SELECT syscolumns.name,systypes.name AS TYPE, syscolumns.length ,syscolumns.isnullable
FROM syscolumns INNER JOIN sysobjects ON sysobjects.id=syscolumns.id
LEFT OUTER JOIN sys.extended_properties ON sys.extended_properties.minor_id=syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.xtype='u' AND sysobjects.name=@@TableName AND (systypes.name='TEXT' OR systypes.name='NTEXT' OR systypes.name='NVARCHAR' OR systypes.name='VARCHAR' OR systypes.name='CHAR' OR systypes.name='NCHAR')
OPEN my_column
FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@@IsNullAble=1)
BEGIN
SELECT @@SQL =( 'ALTER TABLE ' + @@TableName + ' ALTER COLUMN ' + @@ColumnName + ' ' + @@ColumnType + '(' + CAST(@@ColumnLengh AS
NVARCHAR) + ') COLLATE ' + ' Latin1_General_CI_AS NULL')
END
ELSE
BEGIN
SELECT @@SQL =( 'ALTER TABLE ' + @@TableName + ' ALTER COLUMN ' + @@ColumnName + ' ' +
@@ColumnType + '(' + CAST(@@ColumnLengh AS NVARCHAR) + ') COLLATE ' + ' Latin1_General_CI_AS NOT NULL')
END
PRINT(@@SQL)
EXEC(@@SQL)
FETCH NEXT FROM my_column INTO @@ColumnName,@@ColumnType,@@ColumnLengh,@@IsNullAble
END
CLOSE my_column
DEALLOCATE my_column
FETCH NEXT FROM my_cursor INTO @@TableName
END
CLOSE my_cursor
DEALLOCATE my_cursor
PRINT 'ALTERATION DONE'
GO
Once i did that, i re-created the 4 indexes again (i generated the create scripts before dropping them).
Can i be sure now that the textual data (char, nchar, varchar, nvarchar, text, ntext) will not be corrupted? Will it be like if i recreated a new database with the correct collation, as well as the necessary tables, and inserted the data into the tables?
Thank you for reading downto here ;)
Take care all :)