|
Great job, saved me a lot of time and effort
|
|
|
|
|
Was working perfectly for me ...
I have a question can you explain why is this check for ??
<br />
objectproperty(o.object_id,'IsMSShipped')=0
Thanks in advance!!
Sathy
|
|
|
|
|
'IsMSShipped' means "Object created during installation of SQL Server 2005". Possible output is 1 - True, 0 - False. For more information see the SQL Server 2005 BOL.
|
|
|
|
|
First of all GREAT TOOL !!!!!!!!!!!!!!
Only one little problem that occurs ( not a big issue but it would make it perfect ).
If you have FieldA and FieldB, and FieldC = FieldA + ' ' + FieldB for example.
The tool tries to alter the collation but offcourse fails.
Also if a field is the result of a UDF based on another field in the same table.
If it would ignore alle computed fields, it would be perfect ...
|
|
|
|
|
Alex,
It's an excelent job.
Trying to change a database I had the same problem "Collate clause cannot be used ..."
I followed your suggestions and recompiled the scripts
Now I have two other error messages
1. "450.Code Page translations are not suported for the text data type. From 1252 To 1253"
This message exists in the script blocks that copy the data from existing text fields to the ___temp field.
2. On some "Alter table ... column ..." commands I have the error message
"8152.String or binary data would be truncated. 3621.The statement has been terminated"
Can you help on these ?
Best regards
MVourakis
|
|
|
|
|
MVouraksi,
Thanks for the feedback. A couple of questions - are you using SQL 2000 or 2005? Also could you let me know the origional column definition including collation & the collation order you are trying to convert to?
Many thanks
Alex
s
|
|
|
|
|
Hi,
I'm going to change collation on 30 databases, and your program have saved me from hours of work... Thank you for sharing this fantastic program
I'm having some problems while generating the srcript on curtain databases, and hope you can help me find out why!
--script drop of indexes-we will also populate a temp table that helps recreate the indexes later
create table #spindtab
(....)
--generate sql to do indexes
declare @ix_indid smallint, --the index id of an index
@ix_groupid smallint,.....
Cannot insert the value NULL into column 'groupname', table 'tempdb.dbo.#spindtab_______________________________________________________000000001A19'; column does not allow nulls....
The statement has been terminated.
Do you which to continue running the script anyway?
Do you know why this error occur?
Camilla
|
|
|
|
|
Camilla,
I have had a couple of people metion this issue and have not been able to track it down as yet. I would be greatful if you could spare a little time here and do the following for me:
- Open a copy of the script file "004 Drop Indexes + Relations.sql" in query analyzer / SQL Management Studio
- Locate the create table #spindtab statment at the beginning of the script and modify the column groupname so it allows nulls
- Look for the text {2} and replace it with the number 1
- Locate the comment "--drop constraints" about half way down the script and delete the comment and all lines of the script below it
- Add a new line to the end of the script
SELECT * FROM #spindtab WHERE groupname is null
- Run the script. You should see 1 or more tables listed
- Use Query Analyzer / SQL Management Studio to generate a create script for one of the tables listed - Could you let me know which file group the script has created the indexes on?, Could you also let me know the results of running the query
select * from sysfilegroups
Alex
|
|
|
|
|
Thank you so much for helping me with this problem!
I think I found out why the script failed. I had one table that did not have any primary keys or indexes, and this table was the result of the statement:
SELECT * FROM #spindtab WHERE groupname is null
Could your script be changed to allow groupname = NULL, or will this have consequence for other things? But I think it would be nice as a qualitycheck to give the user a warning to notify wich tables that does not have any primary key or indexes though!
This was the result of running select * from sysfilegroups :
groupid = 1
allopolicy = 0
status = 16
groupname = PRIMARY
Do you think the missing index is the solution to this problem?
Camilla
|
|
|
|
|
Is it ok to run this program on system databases like master and tempdb as well?
Camilla
|
|
|
|
|
(Please excuse my english)
This tool saves me hours of handwork.
Tested with 2 DB with 240 tables each one.
Worked perfect!!
Thanks again.
Best regards,
Homero.
|
|
|
|
|
Not sure if they are specific to my setup, but:
"CREATE unique nonclustered INDEX" specifies "ignore duplicate keys" which fails parse, but "IGNORE_DUP_KEY" works OK as a substitute
Leading square bracket was missing from a CREATE FUNCTION object name - i.e.
CREATE FUNCTION [dbo].Some_UDF]
It also forgot to recreate a lot of UDFs, which is MUCH more worrying - i.e. there is a DROP and a GRANT SELECT, but no CREATE.
But looks very interesting...
Marc
|
|
|
|
|
Version:
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) Mar 9 2006 11:38:51 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
|
|
|
|
|
Marc,
Thanks for the feedback - a few questions:
- Do you use SQL 2005?
- I have seen the error with the square bracket missing from the function name before but have been unable to reproduce it. I have not seen missing create function scripts though. Would you be prepared to e-mail me either a backup of part of your database or at least a script that will create a few of your schema elements to test with? my e-mail is alexb at csl-uk.com
Many thanks
Alex Baker
|
|
|
|
|
database is 2000 (as per follow-up post)
please disregard missing function create; I have no idea what happened, but it works fine repeatedly now... I think maybe I went a little mad. Sorry.
Backup would be very hard to do, however I think I found it:
The square bracket issue appears to be when the UDF is created with square-brackets in the first place, i.e. the original script is:
CREATE FUNCTION [Some_UDF] ...
I think this then makes the name offset a little bit wonky
Marc
|
|
|
|
|
Oh, and *huge* thanks; there is light at the end of the collation tunnel thanks to your efforts. Much appreciated.
|
|
|
|
|
Hi,
Still an excellent tool
Some portions of the generated script contain complete garbage: a bunch of chinese text, see below!
Is this perhaps caused by User Defined Functions that are encrypted?
If encryption is the cause, the best solution would probably be:
(a) not drop such functions
(b) not create them, obviously
(c) warn the user.
I have the function sources, so I can re-generate them manually!
Thanks,
Peter
Attachment:
Alter table [dbo].[PRODUCTION] Alter Column [CODING] [char] (6) COLLATE DATABASE_DEFAULT NOT NULL
GO
[dbo].㐥뢠톎摿ꋕ詅㷓鑖陒⡶屲泧ꅵ橢ꘑ첼蕗�悛⣠飏㯡陴䦉솒㜾廙⚝㊅돬ፙ䦷良읒䧝㏢㭥╙붞鶙鏼ܭ廯ᘧ
踋ঙ㷋䕆霏铔啧ᅦ躍祍�Ꝑ⦞孡珁鑔町ᰤᜢ쨴㘇㉩쮏稟鮩넑ੰ㉞忭ᘮ䥤䤾雖Ɑ翤⛨颣彩墩럻맘启Ǡ
恎鵆휹⡱ﳷᛜⓈ㹬蹬긙昶驉袓┩륔Ꭿ햁擹嚠㱹툈䠻’붑ᥴ㏫藪縪콆൜�敏焙魦鵈뗄⼣@ԭ詨覑侌麯壽櫣
쎩ᙜ曪鬨㽨앥ꡚꮦ鏼薈蘈꧁僞쟬섺ಡ펆ꕁপ㝜ጝ뎢攣ₙꦊฺvꤽ̕㍕훎憠ꮗ鮆顼壐걆�摑瀰㹩噠懮짮
GO
GRANT REFERENCES on [dbo].[fn_GetDays] to [public]
GO
|
|
|
|
|
Thanks for the feedback.
I will include in the next version in a couple of weeks.
Alex
|
|
|
|
|
Hi,
Excellent tool!!!
Yet somehow, with one of my databases, the #sql temp table ends up with some rows containing NULL instead of text, which isn't accepted by the C# statement reader.GetString(0).
The obvious simple fix:
while (reader.Read())
if (!reader.IsDBNull(0))
script.Add(new ScriptStep(reader.GetString(0)));
However - it is probably worth investigating where those NULLs are coming from, and then prevent them. Perhaps a few sql ISNULL(... , '') statements might be needed? I tried to debug and find the cause, but did not succeed, so far.
I also found another issue, which I will post separately.
Thanks,
Peter
|
|
|
|
|
Definatly should not get a null in the script - this will result in corruption of your database - could you perhaps give a little context of where the error occurs?
I would recommend adding the code you have suggested above but when a null is detected do:
script.Add(new ScriptStep("--NULL DETECTED"));<code><br />
<br />
now if you run the application and generate the script we can see where the error is occuring - Could you do this and then post the generated script or at least about 10 lines before and after the --NULL DETECTED code.<br />
<br />
Many thanks<br />
<br />
<br />
<br />
<div class="ForumSig">Alex</div>
|
|
|
|
|
Hi,
Thanks, it now seems this might be related to the garbage output caused (perhaps?) by encryption:
GO
[dbo].<< chinese text 1 >>
GO
[dbo].<< chinese text 2 >>
GO
GRANT REFERENCES on [dbo].[fn_GetDays] to [public]
GO
GRANT SELECT on [dbo].[fn_GetDays] to [public]
GO
--NULL DETECTED
GO
--NULL DETECTED
GO
--NULL DETECTED
GO
GRANT REFERENCES on [dbo].[fn_Split] to [public]
GO
GRANT SELECT on [dbo].[fn_Split] to [public]
GO
--NULL DETECTED
GO
--NULL DETECTED
GO
--NULL DETECTED
GO
ALTER TABLE [dbo].[SETTINGS] ADD CONSTRAINT .... (etc)
|
|
|
|
|
Hi,
I modified the various sql files to exclude/skip all items with syscomments.encrypted=1.
The resulting script is "clean".
Unfortunately that isn't sufficient, because running the script produces this:
5075 - The column 'fn_GetDays.Item' is dependent on database collation.
5075 - The column 'fn_Split.Item' is dependent on database collation.
5072 - ALTER DATABASE failed. The default collation of database 'Test' cannot be set to Latin1_General_CI_AS.
This might mean that the encrypted functions MUST be dropped. And then it will be up to the user to add them again afterwards.
Bye,
Peter
|
|
|
|
|
I simply loved this code, however, a problem I'm having with it on our customer databases is that we use a lot of STRING_50 user-defined datatypes. Basically this is a varchar(50).
The tool fails on these columns with
"COLLATE Clause cannot be used on user-defined data types"
Any ideas for this?
|
|
|
|
|
Looks like i have introduced an error in to the 2 "101 Alter Column Collation.????.sql" files. if you look in the 2 files you should see the following code near the end of the file:
if @is_user_type=0<br />
begin<br />
-- <br />
-- some code<br />
--<br />
<br />
end<br />
<br />
set @sql_segment = @sql_segment COLLATE DATABASE_DEFAULT + ' COLLATE DATABASE_DEFAULT ' + @null_text COLLATE DATABASE_DEFAULT <br />
<br />
insert into #sql values (@sql_segment)
modify the code in the 2 sql files as follows:
if @is_user_type=0<br />
begin<br />
-- <br />
-- some code<br />
--<br />
set @sql_segment = @sql_segment COLLATE DATABASE_DEFAULT + ' COLLATE DATABASE_DEFAULT '<br />
end<br />
<br />
set @sql_segment = @sql_segment COLLATE DATABASE_DEFAULT + @null_text COLLATE DATABASE_DEFAULT <br />
<br />
insert into #sql values (@sql_segment)
Alex
|
|
|
|
|
Yeap, this worked excellent!
/Ola
|
|
|
|