Red Gate forums :: View topic - Source Control Scripting in SC ver3
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 2
SQL Source Control 2 forum

Source Control Scripting in SC ver3

Search in SQL Source Control 2 forum
Post new topic   Reply to topic
Jump to:  
Author Message
mdgraves



Joined: 21 Sep 2007
Posts: 42

PostPosted: Thu Dec 15, 2011 9:54 pm    Post subject: Source Control Scripting in SC ver3 Reply with quote

When I first link to a new database in SC ver3, and add my dbase to Source Control, the program scripts out the objects. How can I control the script template?

Example.
I want each object script to have pre-steps to test/DROP...CREATE

IF OBJECT_ID('<<###>>') IS NOT NULL
DROP PROCEDURE <<###>>
GO
CREATE PROCEDURE <<###>>
Back to top
View user's profile Send private message
peter.peart
Site Admin


Joined: 02 Sep 2008
Posts: 362
Location: Top floor, RG towers with the cool kids

PostPosted: Mon Dec 19, 2011 2:56 pm    Post subject: Reply with quote

Thanks for your post. Unfortunately, there isn't any way to perform this sort of task on an object by object basis.

You could however, if you wanted to create a migrations script that performed a DROP IF EXISTS etc. on the entire DB.

HTH!

Pete
_________________
Peter Peart
Red Gate Software Ltd
+44 (0)870 160 0037 ext. 8569
1 866 RED GATE ext. 8569
Back to top
View user's profile Send private message Send e-mail
David Atkinson



Joined: 05 Dec 2005
Posts: 1121
Location: Twitter: @dtabase

PostPosted: Mon Dec 19, 2011 4:46 pm    Post subject: Reply with quote

Can I ask why you want the object definition script to also include a drop statement?

These creation scripts are saved in source control for the sole purpose of defining the state of the schema. These scripts are not designed to be executed.

If you want to deploy from these scripts, you should use SQL Compare Pro to generate the script. This has an option to add a drop/create to the deployment script.

David Atkinson
Product Manager
Red Gate
Back to top
View user's profile Send private message Send e-mail
mdgraves



Joined: 21 Sep 2007
Posts: 42

PostPosted: Wed Dec 28, 2011 5:48 pm    Post subject: DROP STATEMENT Reply with quote

David,
I was under impression Source Control scripts were OK for execution, and I have tested it as link to get scripts for all of my schema and objects into TFS source control, and as interface between TFS and SSMS.

Our standard is to use DROP & CREATE statement combinations, as opposed to ALTER. This factors into 'creation date' and system auditing. On the DBA team, when development (who owns the user databases) sends us a case to promote, we use the scripts that hopefully they are managing in TFS..

Will the Source Control tool be a stand along product, or a bolt-on for SQL Compare Pro?

- Regards,
Martin Graves
Database Technical Lead
Georgia-Pacific Building Products LLC
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1121
Location: Twitter: @dtabase

PostPosted: Wed Dec 28, 2011 6:23 pm    Post subject: Reply with quote

They are okay for execution but aren't designed to be executed.

Please explain the logic behind the creation date and system auditing. I'd be interested to understand this further.

Both SQL Source Control and SQL Compare Pro are standalone tools, but they complement each other a lot when used together. SQL Compare Pro is the supported mechanism to deploy from source controlled scripts. This is recommended over executing the CREATE statements.

David
Back to top
View user's profile Send private message Send e-mail
mdgraves



Joined: 21 Sep 2007
Posts: 42

PostPosted: Wed Dec 28, 2011 9:01 pm    Post subject: creation date and system auditing Reply with quote

"creation date and system auditing"

In the past, queries on the "sysobjects" table for the "creation" date have been utilized by auditors to track when objects changed in user database, then in turn look for change documentation for that object and date. No other date column in table works correctly (per MS). The ALTER by-passes this date field, therefore creating an untrackable event.

I haven't checked this fact using the newer 'system views' (sys.objects), but willing to be it is same.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1121
Location: Twitter: @dtabase

PostPosted: Thu Dec 29, 2011 1:43 pm    Post subject: Reply with quote

You might want to consider using DDL triggers to log schema changes, rather than relying on the creation date. That way you'll be sure to catch all modifications.

David
Back to top
View user's profile Send private message Send e-mail
mdgraves



Joined: 21 Sep 2007
Posts: 42

PostPosted: Thu Dec 29, 2011 3:31 pm    Post subject: auditing Reply with quote

Thanks for suggestion.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group