|
Hello and thank you. It took 6 hours and 43 minutes to run but it has fixed my issue.
Thanks again.
|
|
|
|
|
What was origional error you mentioned? Were you trying to run on SQL Server 7?
Thanks
s
|
|
|
|
|
I'm a newbie in SQL, I tried to change the database collation (from SQL_Latin1_General_CP1_CI_AS to Latin1_General_Bin) of a particular database (Northwind) not Master database using your tool and I encountered an error.
Alter table [dbo].[Orders] Alter Column [ShipName] [nvarchar] COLLATE DATABASE_DEFAULT NULL
GO
8152 - String or binary data would be truncated.
3621 - The statement has been terminated.
Please advise.
Thanks,
Ruy
|
|
|
|
|
Ah yes there is a small bug - if you look in the Script.SQL file and look for 'nVarchar' (possibly line 485) this should read 'nvarchar'
Let me know if this fixes the issue
Thanks
Alex
|
|
|
|
|
when I checked the script.sql file, there is no 'nVarchar' exists. There is only nvarchar
|
|
|
|
|
Hello,
When I found your script at first I was very happy I could now move DBs from China, Europe or the Middle East, well not so quick.
In the DB after I use the script all Data Types text are NULL, ie they loose their data.
I get the following error on all Text type fields, in the example below WC_REASON is a Text field.
Do you have this working for Data types : Text ?
(ie I am not sure if this is an issue with your application or with my DB)
Any suggestions ?
Thanks
Sandy
Error :
Server: Msg 450, Level 16, State 1, Line 1
Code page translations are not supported for the text data type. From: 1255 To: 1252.
I broke the SQL into chunks and this was given by the following code :
declare @InError bit
set @InError =0
begin transaction
-- add a temp column
exec ('Alter table [dbo].[WORK_CARD] add [____temp] [text]')
-- copy data to temp column
if @@error<>0 set @InError =1
if @@error = 0
exec ('update [dbo].[WORK_CARD] set [____temp] =[WC_REASON]')
-- readd origional column
if @@error<>0 set @InError =1
if @@error = 0
exec ('Alter table [dbo].[WORK_CARD] drop column [WC_REASON]')
-- drop origional column
if @@error<>0 set @InError =1
if @@error = 0
exec ('Alter table [dbo].[WORK_CARD] add [WC_REASON] [text] ')
-- Copy data back to origional column
if @@error<>0 set @InError =1
if @@error = 0
exec ('update [dbo].[WORK_CARD] set [WC_REASON] = [____temp] ')
-- drop temp column
if @@error<>0 set @InError =1
if @@error = 0
exec ('alter table [dbo].[WORK_CARD] drop column [____temp]')
if @@error<>0 set @InError =1
if @@error = 0
commit transaction
else
rollback transaction
|
|
|
|
|
So you are converting data from hebrew to Latin - I guess that some text in the WC_Reason column has some hebrew characters in it that can not be represented using the Latin code page - you can either update your data in the WC_Reason column to contain only latin characters before running the script OR you could make the WC_Reason column use the nvarchar data type.
does this help?
Alex
|
|
|
|
|
Hi, This is a great tool, i used it against a SQL2000 with no error.
Now i want to test it against a SQL2005, and i get this error :
Cannot insert the value NULL into column 'groupname', table 'tempdb.dbo.#spindtab____00...; ... INSERT fails.
Any idea?
Good work,
Thanks.
David
|
|
|
|
|
This is interesting - would it be possible for you to script the schema for your database or at least the part of your database that causes the problem?
Thanks
Alex
|
|
|
|
|
Muy buen trabajo!! (Very Good job!!)
Fernando Desde Argentina
|
|
|
|
|
This utility solved my problem.
You did really a great job !
Régis
|
|
|
|
|
Thanks for a great utility .;)
|
|
|
|
|
Hey.
Well, I'm trying to change the collation of the database used by an accounting program. Each time I excute it I get the following errors:
Server: Msg 5074, Level 16, State 8, Line 2
The object 'IDO_TPIECE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_TTIERS' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_TDATE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_TIERS' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_DATE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
The index 'IDO_SOUCHE' is dependent on column 'cbDO_Piece'.
Server: Msg 5074, Level 16, State 1, Line 2
and so on...
Any idea on how to circumvent this?
|
|
|
|
|
Well... I solved the problem. I had to change the order of the different steps like this:
1. Set db to single user
2. Set arithabort on
3. Drop the indexes
4. Drop the constraints
5. Drop the statistiscs
6. Drop the calculated fields
7. Alter database collation
8. Alter tables/columns collation
9. Recreate the calculated fields
10. Recreate the constraints
11. Recreate the indexes
12. Set artithabort off
13. Set db to multi user
If I change the script to execute in this order it works fine....
|
|
|
|
|
Thanks for the feedback - i have been a little tied up with work recently but will incorporate your recommended resequencing soon.
Alex
|
|
|
|
|
I have now modified the logic following your feedback.
Many thanks
Alex Baker
|
|
|
|
|
I found your gem while researching SQL Server 2000 collations. I have been looking for a tool like this for some time to correct some of the database mistakes that I've made in the past :P Anyways, thanks for the tool
My Collation blog
|
|
|
|
|
I get:
"Server: Msg 7613, Level 16, State 1, Line 2
Cannot drop index 'PK__workitem__4A8310C6' because it enforces the full-text key for table 'workitem'.
Server: Msg 3727, Level 16, State 1, Line 2
Could not drop constraint. See previous errors."
Do you have any idea why i get this error?
|
|
|
|
|
I am surely a fan of your tool, i think sql server 2005 with it's new "Integration Services" is a bit to complex for my purpose - Just to change collation for my DB and needed DTS but that isn't straight forward enymore...
Are you planning to add sql server 2005 compability to your tool? Would be nice to have a ticker to tick off 2005, or even better - find our DB version and fix the scripts on the fly...
|
|
|
|
|
I might work on a 2005 version at some time but i would not wait for me to finish it just now. I have a lot on at the moment.
Sorry
Alex
|
|
|
|
|
If you want to make this project compliant with SQL Server 2005, open the project and the Script.sql file and do the following modifications:
1) replace all instances of 'collationid is not null' with 'collationid <> 0'. In SQL Server 2005 if a column has no collation, the collationid in syscolumns is equal to zero.
2) Look for the following lines:
if @CC_TypeName COLLATE DATABASE_DEFAULT in ('nVarchar', 'varchar','char','nchar')
set @SQLSegment = @SQLSegment COLLATE DATABASE_DEFAULT +' ('+@CC_Length COLLATE
DATABASE_DEFAULT + ')'
and replace with
if @CC_TypeName COLLATE DATABASE_DEFAULT in ('nVarchar', 'varchar','char','nchar')
BEGIN
IF @CC_LENGTH = '0'
SET @CC_Length = 'max'
set @SQLSegment = @SQLSegment COLLATE DATABASE_DEFAULT +' ('+@CC_Length COLLATE DATABASE_DEFAULT + ')'
END
Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types.
When you have a max specifier, the length column in the syscolumns is equal to -1. As in this script the length column is divided by two, the max specifier is equal to zero.
I can't guarantee these modifications will take in account all the new features of SQL server 2005. But for me, it was enough.
|
|
|
|
|
I have now included your recommendations in to the latest version of this tool.
Many Thanks
Alex Baker
|
|
|
|
|
Again, as everyone else has stated fantastic tool and thanks a lot for saving me a lot of typing. On some of our databases we did come across an error that was occuring because of trigger use within the databases - preventing some tables from being updated. I included the following code in the SQL Script to overcome this issue:
(Just before the script section for dropping check constraints)
/*script out the disabling of triggers */
insert into #SQL (SQL)
select 'Alter table [' + name + '] disable trigger all'
from sysobjects
where type = 'U' and
(deltrig > 0 or instrig > 0 or updtrig > 0 or seltrig > 0)
/*script out dropping of check constraints */
...
(Again, just before the script section for recreating check constraints)
-- script out the enabling of triggers
insert into #SQL (SQL)
select 'Alter table [' + name + '] enable trigger all'
from sysobjects
where type = 'U' and
(deltrig > 0 or instrig > 0 or updtrig > 0 or seltrig > 0)
-- script out recreation of check constraints
...
Sincerely, Shay Stowe
|
|
|
|
|
Server: Msg 515, Level 16, State 2, Line 139
Cannot insert the value NULL into column 'index_keys', table 'tempdb.dbo.#spindtab___________________________________________________________________________________________________________00000000457B'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Bernieov
|
|
|
|
|
I see that there a couple of posts showing this issue. Could you run the following SQL script and send me a dump of the table created called __Test, extract to a text file or something like that and e-mail it to me at alexb at csl-uk.com.
could you also let me know the version of SQL server you are using
AND
using the query analyzer could you locate the stored procedure in the master database called sp_helpindex. If you right click on this item you will be given an option to script the stored procedure to a new window. Could you send me the code that this generates.
Many thanks
-- script drop of indexes - we will also populate a temp table that helps recreate the indexes later
create table __Test
(
objectname sysname collate database_default NULL,
index_name sysname collate database_default NULL,
stats int,
groupname sysname collate database_default NULL,
index_keys nvarchar(3000) collate database_default NULL, -- see @IX_keys above for length descr
OrigFillFactor tinyint,
IsAutoStatistic bit
)
go
--generate SQL to do indexes
declare @IX_indid smallint, -- the index id of an index
@IX_groupid smallint, -- the filegroup id of an index
@IX_indname sysname,
@IX_groupname sysname,
@IX_status int,
@IX_keys nvarchar(3000),
@IX_dbname sysname,
@IX_ObjID int,
@IX_ObjName sysname,
@IX_OrigFillFactor tinyint,
@IX_IsAutoStatistic bit
-- Check to see the the table exists and initialize @IX_objid.
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
declare ms_crs_ind cursor local static for
select id, object_name(id), indid, groupid, name, status, OrigFillFactor, case when (status & 64) = 0 then 0 else isnull(INDEXPROPERTY(id,name,'IsAutoStatistics'),0) end as IsAutoStatistic from sysindexes
where /*id = @IX_objid and */indid > 0 and indid < 255
and objectproperty(id,'ISMSSHIPPED')=0 and objectproperty(id,'IsTableFunction')=0
order by object_name(id),indid
open ms_crs_ind
fetch ms_crs_ind into @IX_objid, @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor, @IX_IsAutoStatistic
-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @IX_i int, @IX_thiskey nvarchar(133) -- 128+5
declare @rebuild_index bit
select @IX_keys = '[' + index_col(@IX_objname, @IX_indid, 1)+']', @IX_i = 2, @rebuild_index=1 --parameter from application can force all to be rebuilt
if (indexkey_property(@IX_objid, @IX_indid, 1, 'IsDescending') = 1)
set @IX_keys = @IX_keys + ' DESC'
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, 1, 'columnid')) is not null
set @rebuild_index=1
set @IX_thiskey = '[' + index_col(@IX_objname, @IX_indid, @IX_i) + ']'
if ((@IX_thiskey is not null) and (indexkey_property(@IX_objid, @IX_indid, @IX_i, 'IsDescending') = 1))
set @IX_thiskey = @IX_thiskey + ' DESC'
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
set @rebuild_index=1
while (@IX_thiskey is not null )
begin
select @IX_keys = @IX_keys + ', ' + @IX_thiskey, @IX_i = @IX_i + 1
if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
set @rebuild_index=1
set @IX_thiskey = '[' + index_col(@IX_objname, @IX_indid, @IX_i) + ']'
if ((@IX_thiskey is not null) and (indexkey_property(@IX_objid, @IX_indid, @IX_i, 'IsDescending') = 1))
select @IX_thiskey = @IX_thiskey + ' DESC'
end
select @IX_groupname = groupname from sysfilegroups where groupid = @IX_groupid
-- INSERT ROW FOR INDEX
if @rebuild_index =1
insert into __Test values (@IX_ObjName,@IX_indname, @IX_status, @IX_groupname, @IX_keys, @IX_OrigFillFactor, @IX_IsAutoStatistic)
-- Next index
fetch ms_crs_ind into @IX_objid, @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor, @IX_IsAutoStatistic
end
deallocate ms_crs_ind
select * from __Test
Alex Baker
|
|
|
|
|