Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS – Transfer SQL Server Objects Debugged


SSIS – Transfer SQL Server Objects Debugged

Author
Message
Jim Wylie
Jim Wylie
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
Super sleuthing. Thanks.
Paul Lach-293939
Paul Lach-293939
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

I ran into the same issue using Microsoft's SMO objects in C#. I opened a case with Microsoft and they confirmed that there was a bug. As you discovered, the problem is that when you rename an object whose definition is contained in sys.sql_modules the object gets renamed in sys.objects, but the source code is not modified in sys.sql_modules.

Interestingly, the people responsible for SQL Server Management Studio have "fixed" the problem in their code. Whenever you script a stored procedure through the right-click menus the new (correct) name appears in the script even though the old name appears in the sys.sql_modules record for that stored procedure. Apparently, they rebuild the definition line up to the AS keyword and then concatenate the rest of the script from sys.sql_modules. What a kludge!!!

Why not fix the underlying problem? When you rename an object whose definition appears in sys.sql_modules, replace the old name with the new name in the object definition script in sys.sql_modules!


seiiv
seiiv
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 95

From SQL server books online:

http://msdn2.microsoft.com/en-us/library/aa214392(sql.80).aspx

Topic: Modifying and Renaming a Stored Procedure

Note Renaming a stored procedure does not change the name of the stored procedure in the text of the procedure's definition. To change the name of the stored procedure in the definition, modify the stored procedure directly.





Ahmad Drshen-386043
Ahmad Drshen-386043
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 6
hey that's really great both writer and package Smile.

I used it and customized according to my needs but one thing where i am going to tuck is how i can transfer Table with all of its elements like defaults,constrains,clustered and nonclustered indexes , primray and without data , how i can generate script for tables this pkg only does for stored procedures and functions i try my best to do it through MS pakage but unable to it its internal bugg .

Reply Soon ,

Thanks in advanceWink
Mike Byrd
Mike Byrd
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 389
Thanks for the article. I too have wondered about the 'features' that Microsoft has in their SSIS objects. I just completed my first large SSIS project and ran into similar (although different) problems. I had thought about doing an article similar to yours, but you beat me to it.

I agree with your views on how Microsoft could release code for us developers to debug. Makes you really wonder about their QA staff and if they have anybody on the QA side that knows how businesses really operate.

Cheers,
Mike Byrd

Mike Byrd
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 445
Good article. I've always as a matter of habit scripted drop & create commands because of the potential problems they may cause but, over time, had forgotten exactly why I started doing it Smile Good article! I'm not really well versed in SSIS but have occasionally used the copy tasks with mixed results - at least I can keep this in mind when using it.

Also, you cannot really disable sp_rename because it's used by SQL Server diagram schema saving code generation (I'm sure it has a better name than that!) extensively. If you change the schema of a table it creates a table with a similar name and the new schema, transfers the data, drops the old table, renames the new table and recreates referential integrity, etc. If sp_rename was crippled then you'd run into interesting issues Smile



omar alsawwa
omar alsawwa
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 35
Smile Many Thanks.
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1424 Visits: 1466
Smile At last! the answer to Transfer Server Objects

Set CopySchema to True!

Many thanks I've been looking for this for a while.
scogan
scogan
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Steve, Well done!!!

I am not a SQL developer at all but have been forced into this because I have 3GL background LOL and appearently overly ambitious. So I have a new 2005 database going live in two months and have to create a monthly migration package. I am learning SSIS on my own because my Company cannot afford training. Enough of my sad story. I have some questions on your diagram because I need to do the same thing and I got the same errors and I want to make it dynamic like yours. I have questions on the Looping and passing the list of objects from the storage to the DROP FOR LOOP.

I hate beg but if you have a moment you might be able to get me on the right track.

My name is Steve Cogan
My email is scogan@zoomtown.com if you are intested in answering some questions send me an email and I will email back.

Sincerely,

Steve Cogan
Bob Clayton
Bob Clayton
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 35
Microsoft took an intuitive, easy-to-use tool (DTS), and turned into a needlessly complicated, buggy disaster called SSIS. I believe Microsoft has completely lost touch with who their customers really are. Most SQL Server users are people like me, who work on multiple projects with tight budgets and timelines. I need a tool that solves the most common problems quickly, reliably, and intuitively. SSIS is not intuitive, is not easy to use, and is buggy and unreliable.

Granted, there are a handful of SQL Server uber-studs (and studettes) who need obscure programming features and capabilities that I could probably not even dream up. However, that is a small percentage of the SQL Server user community. The rest of us need to accomplish the most common tasks quickly.

I sincerely hope that Microsoft would get re-focused on the real users of SQL Server, and make SSIS usable for for the broad community of users.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search