Scripting Stored Procedures/User Defined Functions

wil

I am trying to automate scripting a SQL Server 2005 database. I have most of the steps covered, but the one I thought would be easy is giving me some trouble.

Currently, we use the Generate Scripts task from SQL Management Studio. We select all stored procedures and user-defined functions and it scripts out great.

When I do this using SQL Compare the script doesn't work. The procs scripted first reference later procs/functions that haven't been added yet and it all blows up. Can anyone help me out?

-Thanks

Michelle T

The 'save to script folder' functionality of SQL Compare is designed to produce per-object script files, which don't automatically put themselves in the right order to run into a database.

If you want a script of an entire database that you can run straight into a database with Management Studio, in one file, the easiest way to do it is as follows:

1) Create an empty database
2) Compare the database you want to script to the empty database
3) (Optional) Select the 'Do not use transactions in synchronization scripts' option for cleaner-looking output
4) Go through the synchronization wizard until you get the 'View SQL Script...' button
5) Save the resulting SQL script to a file

This should get everything scripted out in the right order for running into an empty database.

wil

I am not using the GUI, i am using the components from code

Michelle T

In which case you do basically the same thing:

1) Create an empty database on some database server you have access to
2) Create and register Database objects for that empty database and the database you want to script
3) Get a Differences object by comparing the databases with CompareWith
4) Make a Work object and BuildFromDifferences from the Differences object (using the option Options.NoSqlPlumbing if you want cleaner-looking output with no transactions)
5) Get the ExecutionBlock from the Work object and save the contents to a file with SaveToFile

I can provide a code example if you need it, but if you're familiar with the toolkit hopefully the above overview is enough to get you going.

wil

This is mostly working. The script is blowing up though i'm not sure why. One thing I have noticed is that even though I am going thru each difference and setting the selected property accordingly (I am only selecting in this case if the DatabaseObjectType is StoredProcedure or Function), I am still getting some table changes in the resulting script.

Is there something else I have to do to only get stored procedure/user-defined function changes?

Michelle T

It sounds like you are using Options.IncludeDependancies (which is part of Options.Default). This is equivalent to ticking the 'Include Dependancies' checkbox in the UI and will include other objects if SQL Compare thinks that the selected objects might depend on the changes in other objects.

You'll need to specify each option that you want individually, instead of using Options.Default, if you want to avoid including table changes (to tables that the stored procedures and functions refer to). Note that this does mean that if your new stored procedures and functions really do depend on one of those changes, your script will blow up.

wil

OK, it is now only including the stored procedures & functions like I want.

However, I have 2 other problems I am seeing.

1. If a stored procedure has been renamed, the script fails. Example, sp_OldProc is renamed to sp_NewProc. Scripting this object out using SQL Management Studio, creates a working script. Using red-gate I get something like this:

PRINT 'Scripting object sp_NewProc'
CREATE PROC sp_OldProc .....

Is there any way to get around this?

2. Some of our procs contain special characters like, • »«, etc.
These get scripted out as ?
Anyway to avoid this behavior and have it script out correctly? This also works fine from management studio (we usually select ANSI encoding)

Michelle T

1) This ought to have been fixed in version 6 - can you check that you're using a version of the dll which begins with '6' rather than '5'? If you are, then it looks like you've found a bug with our sp_rename handling and we'll take a closer look.

2) SaveToFile has a variety of encoding options - if you use ASCII encoding you'll get ?s instead of special characters. If you're still getting this problem with Unicode encoding, it's probably a bug and we'll look into it.

http://help.red-gate.com/help/SQLCompar ... gType.html

wil

I have version 5.3.0.2
How do I get a newer version?

JonathanWatts

Hi there,

To download the new release, please use the check for updates function in your existing installation's help menu, or visit the product page.

Regards,

Jonathan

wil

When I do this manually using SQL Management Studio, it has 2 selections: ANSI and Unicode. I assumed ASCII correlated to ANSI. The SQL Management version creates the scripts without a problem in ANSI.

Michelle T

ASCII doesn't correlate precisely to ANSI. (ASCII only uses 7 bits per character, and converts anything it can't represent like that into a question mark. ANSI uses 8 bits, and so can represent more characters.)

We don't have an option to output ANSI - we only script to ASCII, UTF-8 or Unicode. (Outputting ANSI is slightly awkward, because you need to be sure you're working with the right 'code page' - which affects what that eighth bit means - and this changes between countries. As ANSI is an old standard and UTF-8 works much better in almost all circumstances, we decided not to try and support it.)

If you want your scripts to be similar to the ANSI versions, I recommend UTF-8 - Unicode will make all your files twice the size they were with ANSI encoding, whereas UTF-8 should be about the same.