|
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
|
|
|
|
|
Hi,
Every time I try and run this, I get a "Must declare variable @IX_objid" and "Must declare variable @IX_objname"
Any ideas?
|
|
|
|
|
Just sussed it - the database collation is case sensitive - and some of the variables are declared "camel style", whilst used as all lower case. Changed the variables to use camel style - all appears OK now
|
|
|
|
|
Could you let me know where the problems were - i thought i had fixed the issue you mentioned
Thanks
s
|
|
|
|
|
Hi,
The database collation was (and still is) set as case sensitive. I declared camel-style, and so weren't the same as in the code, i.e:
Declare VarIable1 VarChar(25)
Select variable1 = "It ain't easy bein cheezy"
will return an error as "Variable must be declared". I should have (and did) alter the code to say:
Select VarIable1 = "It ain't easy bein cheezy"
See the difference? (capitol V and I)
Tony
|
|
|
|
|
Its still a cool tool though - saved me hours and a lot of grief
|
|
|
|
|
Hi there!
This looks like an awesome utility but we are running into problems when running it against the databases we need to switch from case-insensitive to case-sensitive collation.
When trying to generate the script, the following error appears over and over again:
Cannot insert the value NULL into column 'index_keys', table 'tempdb.dbo#spintab______________________00000000062C' column does not allow nulls. INSERT fails.
This happens against both of our databases. But running against the Northwind database the script is generated without errors and it runs fine.
One note of interest is that the generated script for the Northwind database is properly formatted and contains GO, commit and rollback statements. On the other hand, the script produced against our databases did not have any of this...It looks like it made it halfway through the process and then just died.
Any ideas?
Thank you for your time.
-Joe
|
|
|
|
|
I am encountering the same error with our DB. Anyone have any ideas why this is happening, or if there is a fix?
|
|
|
|
|
Hi, sorry about the delay in getting back to you but i did not get a notification message from the code project letting me know that you had posted a message.
I have now resolved the issue and posted an updated version of the collation change to the code project. They should have it available for you to download in the next few days
Alex Baker
|
|
|
|
|
Your script seems to work very well thanks. However it doesn't appear to update VIEWs, is this correct or am I missing something?
Nigel Rickerby
|
|
|
|
|
Nigel,
I do not think regular views need recreating with a collation order as they take the collation order used the columns they bind to. Are you refering to indexed views?
I will admit to having no experience of using indexed views so have not tested this tool with them. If you find that there is a problem and it relates to indexed views then let me know and i will make the necessary modifications.
Alex Bake
|
|
|
|
|