Compartilhar via

Part II - Versioning SQL Server Stored Procedures and Other db Objects in VSS

  • Artigo

Step
1: Since I can only add files to a VSS database, the first step is to figure out
how to [programmatically] transform my SQL Server database objects (stored
procedures, UDFs, table schema, etc) into text files. One idea is to write
a C# class that connects to a database and use isql.exe for this task. Can
I do it with one line of code? AHA! I can!

isql
-S servername -U
sqluserid -P
sqluserpassword -d
databasename -q "exec
sp_helptext databaseobjectname " > drive letter :\DBO Text Files\
databaseobjectname .txt

sp_helptext,
what a lovely, lovely name.

Of course, in Visual Studio .NET, I can do the virtually
the same thing with a click of the button. In the Server Explorer window,
you can simply select a database object and click 'Generate Create
Scripts'. 

IMPORTANT: When generating individual script files of a
SQL Server dbo for addition to a VSS db, elect to save the file
in Windows Text (ANSI) format rather than the default Unicode enconding.
Doing so will allow you to Diff versions of the file. Currently, VSS is
unable to diff unicode files.

You can also perform this same task
using SQL Server's Enterprise Manager. Mike, of Rosey's Blog, writes:

"SQL
Server actually has a couple of "features" when generating scripts that make
this a little easier. You can select all the objects in Enterprise Manager
you want to script, right click and select Generate SQL Script - which launches
a wizard that will walk you through the process. You also want to change
the default option of scripting to a single file - to one of script each object
to it's own file. Then, when SQL Server is done, you just navigate to
the folder where you put the scripts, select the files, and check them into
VSS."

Ce message est fourni en l’état, sans garantie d’aucune
sorte, et ne vous confère aucun droit. Vous assumez tous les risques liés à son
utilisation.

Comments

  • Anonymous
    July 11, 2003
    Also, remember to be logged in as a database owner when generating SQL scripts. If you don't, you could end up with duplicate objects. For example, I have a DB and generate a script while not logged in as DBO. My script contains a lot of DROPS before CREATES, but SQL recognizes the object dbo.StoredProc as different than StoredProc, so it creates 2 objects with the same name.This was the #1 deployment problem that we experienced when using generated scripts to push changes to a production environment.

  • Anonymous
    July 17, 2003
    The comment has been removed

  • Anonymous
    October 27, 2003
    re Doug Thews' comment re being logged in as dbo. You can avoid this problem by naming the script with the owner prefix, e.g. dbo.myScriptName. Of course, you still need to have privileges to drop and create scripts though.

  • Anonymous
    July 28, 2004
    The comment has been removed

  • Anonymous
    September 02, 2005
    Super site!

  • Anonymous
    March 21, 2006
    Your site is very informational for me. Nice work.

  • Anonymous
    June 04, 2006
    i like your website very much but please do get us more information about it

  • Anonymous
    September 15, 2006
    [URL=http://http://replicarolexwatch.ir.pl]replica-rolex-watch[/URL]<a href="http://http://replicarolexwatch.ir.pl">replica rolex watch</a>

  • Anonymous
    September 16, 2006
    I am so [url=http://access.2surf.eu]lucky[/url] on having what I have! And good luck in yours [url=http://2access.2surf.eu]search[/url].
    Just visit [url=http://access.122mb.com]my site[/url].









  • Anonymous
    June 08, 2009
    PingBack from http://toenailfungusite.info/story.php?id=6520

  • Anonymous
    June 19, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=24302