|
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
|
|
|
|
|
No the issue is with standard views, however I have found the solution. It appears that the syscolumns table continues to report the columns as their old collation until they are recompiled. I simply ran an ALTER on each view (changing nothing) and the issue is corrected. You might want to add running an ALTER statement for each view to your script.
The following select shows any column collations that have NOT been converted to (in this case) Latin1_General_CI_AS, after you program has finished executing.
select object_name(id), name, collation from syscolumns where collation IS NOT NULL AND collation <> N'Latin1_General_CI_AS' order by object_name(id), name
Thanks
Nigel
Nigel Rickerby
|
|
|
|
|
I've been trying to do something like this myself, but never could find the time.
This solved several burning issues with my databases.
Just wanted to say thank you.
Cheers!
|
|
|
|
|
 You solved my problem with one click. Thank you
|
|
|
|
|
Hi,
Why does your script drop foreign keys for all int columns? Is it necessary to do that? Isn’t it enough to drop the FK if it’s a column that has collation?
|
|
|
|
|
When dropping a primary key you need to drop all foreign keys that relate to it as well - i decided that it was too much effort to work out what needs dropping and what can stay. Since you have made the comment i have modified the script to only drop the foreign keys and indexes that really need dropping.
I still drop all check constraints, table functions and calculated columns.
I have tested on a few of my databases but would appreciate if you could also check the change on one of your databases.
Note that you did make me review the code and i did find a problem where indexes with columns sorted in descending order would be recreated in ascending order.
Alex
|
|
|
|
|
Actually i have not updated the code yet - i need to submit it and get the code project to change it. This may take a few days.
Alex
|
|
|
|
|
Just thought that I'd like to thank you for posting such a useful bit of code. I don't need to use for the purpose you intended - but it is really useful to find a bit of code that creates a script to drop all indexes, constraints etc - and rebuild them again.
You have saved me a load of typing - thank you!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
