InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:59:21 GMT20http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]Gursoy Yerli (5/11/2010)[/b][hr]Fixed a bug;Your generation sproc was ignoring the schema when using "EXEC sp_helptext @ProcedureName". Added below to fix the issue. DECLARE @abc VARCHAR(MAX) SET @abc = @RoutineSchema + '.' + @ProcedureName INSERT INTO @StoredProcsDefinitionTable ( ProcedureLine ) [b]EXEC sp_helptext @abc[/b]Good article but It does not apply what we're trying to do.[/quote]Thanks, I never spotted that all my sprocs are on [dbo] schema.Tue, 11 May 2010 10:38:06 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxFixed a bug;Your generation sproc was ignoring the schema when using "EXEC sp_helptext @ProcedureName". Added below to fix the issue. DECLARE @abc VARCHAR(MAX) SET @abc = @RoutineSchema + '.' + @ProcedureName INSERT INTO @StoredProcsDefinitionTable ( ProcedureLine ) [b]EXEC sp_helptext @abc[/b]Good article but It does not apply what we're trying to do.Tue, 11 May 2010 09:00:46 GMTGursoy Yerlihttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxFor those of you who are willing to pay a bit, there's a product that does just that:http://www.nobhillsoft.com/Randolph.aspxinstall and setup is quick. more than just version control for the entire schema (tables included) it also provides clear reports, search through history, script at any point in time, rollbacks, email aleerts when things change, pushing schema into sourcesafe, subversion, TFS, and more...Mon, 10 May 2010 13:48:29 GMTyonisionhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxHave you thought about the default trace as schema changes are already recorded there and easily available without the need for a ddl trigger. In fact Microsoft provides a schema change history report available as part of the basic installation. Why re-invent the wheel?Again DDL triggers are part of the toolset and available to be used when most appropriate but a bit overkill to use a DDL trigger to get information on something that is already directly available.This has also been documented in a good article from 2008http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/Fri, 07 May 2010 12:32:17 GMTBill Galashanhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxLooks like your trigger captures more than just table changes, which is cool - presumably all sorts of alter/create statements include sprocs would find their way into that audit trail. I like it for active monitoring- obviously the drawback of our setup is that it's a scheduled job and runs daily so all manner of nonsense can happen in between. Might try and implement this on our dev box and take a look at the detail in the xml events generated. We do like the SMO stuff though, especially using a generic scripter to script a table, index, sql server job, linked server, assembly, without having to really know too much about it. Something quite clean about the powershell approach.Thanks!Fri, 07 May 2010 10:42:11 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]mike.renwick-894639 (5/7/2010)[/b][hr]I've not come across DDL triggers before- they look quite interesting and a relatively sensible place to capture changes. [/quote]I've written this trigger to record when DDL changes were made to a table, it's quite useful.The article I used as the initial source is here: [url=http://www.databasejournal.com/features/mssql/article.php/3685661/Monitoring-Changes-to-your-Database-Schema.htm]http://www.databasejournal.com/features/mssql/article.php/3685661/Monitoring-Changes-to-your-Database-Schema.htm[/url]If you look at the xml EVENTDATA() there is also the full create statement for the stored procedure under the value [i]/EVENT_INSTANCE/TSQLCommand/CommandText[/i].[code="sql"]-- ************************************************ -- TABLE DDLChangeCatalog-- DDLChangeCatalog table used by the trigger:-- ************************************************ DECLARE @ObjectName AS varchar(128)SET @ObjectName = N'DDLChangeCatalog'IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @ObjectName)BEGIN CREATE TABLE dbo.DDLChangeCatalog ( EventType nvarchar(100) NOT NULL, SchemaName nvarchar(100) NOT NULL, ObjectName nvarchar(100) NOT NULL, ObjectType nvarchar(100) NOT NULL, EventDate datetime NULL, SystemUser nvarchar(100) NULL, CurrentUser nvarchar(100) NULL, OriginalUser nvarchar(100) NULL, CONSTRAINT PK_DDLChangeCatalog PRIMARY KEY CLUSTERED ( ObjectName ASC, ObjectType ASC, EventType ASC, SchemaName ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) EXEC sys.sp_addextendedproperty @name=N'Summary', @value=N'This table stores DDL changes to the database server' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName EXEC sys.sp_addextendedproperty @name=N'Used By', @value=N'change monitoring' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'EventType' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'EventType' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SchemaName' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'SchemaName' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ObjectName' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'ObjectName' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ObjectType' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'ObjectType' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'EventDate' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'EventDate' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'SystemUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'SystemUser' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CurrentUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'CurrentUser' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'OriginalUser' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=@ObjectName, @level2type=N'COLUMN', @level2name=N'OriginalUser'ENDGOIF NOT EXISTS(SELECT * FROM sys.triggers WHERE [Name] = 'ChangeDDLCatalog')BEGIN EXEC ('CREATE TRIGGER ChangeDDLCatalog ON DATABASE FOR DDL_PROCEDURE_EVENTS AS SELECT 1')ENDGO-- **********************************************************************-- This DDL trigger uses the "DDL_DATABASE_LEVEL_EVENTS" event to identify when this trigger should fire. -- This event will fire whenever any CREATE, ALTER, or DELETE command is executed against any object -- in the database. -- This trigger makes use of a series of EVENTDATA() function calls to identify information about -- which object, and event was used to modify the database. Once all the pertinent data is gathered -- this trigger then inserted the collected data into the "DDLChangeCatalog" table. -- $Revision: 1.0 $-- **********************************************************************ALTER TRIGGER ChangeDDLCatalog ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS ASBEGIN SET NOCOUNT ON DECLARE @EventType nvarchar(100) DECLARE @SchemaName nvarchar(100) DECLARE @ObjectName nvarchar(100) DECLARE @ObjectType nvarchar(100) SET ANSI_PADDING ON -- This is needed for when subscriptions are added. SELECT @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'), @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'), @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'), @ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') IF @EventType IS NULL SET @EventType = N'Unknown' IF @SchemaName IS NULL SET @SchemaName = N'Unknown' IF @ObjectName IS NULL SET @ObjectName = N'Unknown' IF @ObjectType IS NULL SET @ObjectType = N'Unknown' -- Is the default schema used? IF @SchemaName = ' ' BEGIN SELECT @SchemaName = default_schema_name FROM sys.sysusers U INNER JOIN sys.database_principals P ON U.uid = P.principal_id WHERE U.[Name] = Current_User END --END IF IF @SchemaName IS NULL BEGIN SET @SchemaName = 'Unknown' END --END IF IF EXISTS(SELECT 1 FROM dbo.DDLChangeCatalog WHERE EventType = @EventType AND SchemaName = @SchemaName AND ObjectName = @ObjectName AND ObjectType = @ObjectType) BEGIN -- Update existing row UPDATE dbo.DDLChangeCatalog SET EventDate = GetDate(), SystemUser = SUser_SName(), CurrentUser = Current_User, OriginalUser = ORIGINAL_LOGIN() WHERE EventType = @EventType AND SchemaName = @SchemaName AND ObjectName = @ObjectName AND ObjectType = @ObjectType END ELSE BEGIN -- Update new row INSERT INTO dbo.DDLChangeCatalog ( EventType, SchemaName, ObjectName, ObjectType, EventDate, SystemUser, CurrentUser, OriginalUser ) VALUES ( @EventType, @SchemaName, @ObjectName, @ObjectType, GetDate(), SUser_SName(), Current_User, ORIGINAL_LOGIN() ) END --END IFENDGO[/code]Fri, 07 May 2010 10:29:58 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxI've not come across DDL triggers before- they look quite interesting and a relatively sensible place to capture changes. I can see these being helpful for evaluating roll-out accuracy. I think my concerns with this approach would probably start to split hairs, off the top of my head, they wouldn't capture anything that lives outside of a database but within the database server (mail profile and accounts, sql server jobs, linked servers for example), or indeed the databases themselves. Moreover, triggers are somewhat simple to disable. In our setup, we weren't as concerned about being able to undo or redo changes automatically- I guess we're a simpler setup. Also, our VCS integration is significantly more basic than the one you outline here. Mostly we're satisfied if what's in the baseline matches what comes out the source extract.Fri, 07 May 2010 10:17:16 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]mike.renwick-894639 (5/7/2010)[/b][hr] snip... how they can prove that what is in their source repository is exactly and only what is in their production environment. ...snip [/quote]One automatic way to do this is through database-level DDL triggers. That is, for every change there is a trigger that saves the code obtained from EventData(). The DDL trigger inserts these into an external db, which also collects similar changes from several databases. Then it's a matter of matching the contents of the field that stores /EVENT_INSTANCE/TSQLCommand of EventData() with the source. See an earlier post on this thread for code showing a simple DDL trigger example using EventData(). This solution will have the effect of limiting the source to be one file for each object in the db. There also has to be some consistency in naming db objects and their corresponding files in the VCS. Comparing in SQL itself is slow unless we use CLR routines. CLRs can do fast DIFF on source file and the contents of that field and produce a nice table of all objects in production that differ from the source.This method will also have the benefit of using a variety of VCS and not tied to a specific product. If you have multiple version control systems, then create one CLR for each to encapsulate that VCS's diff commands. Within each VCS also there could be different ways to compare: binary, line-by-line, UNICODE, etc. You can encapsulate these as well in the CLR. At various times, we had to integrate VSS, SVN, GIT, and Mercurial. Each time only the CLR needed tweaking while keeping the rest of the SQL side and VCS side unchanged.We can also answer the question: which version of the source matches the production? How many changes were made since then? Which objects had most changes? Since all these are tied to dates and times, simple undo and redo can be implemented as well.Fri, 07 May 2010 09:50:51 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]jacroberts (5/7/2010)[/b][hr][quote][b]Grant Fritchey (5/7/2010)[/b][hr]Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.[/quote]When we release to an environment we 'label' the files in PVCS so at a later date we can get any release that has gone into any environment just by getting the files by the relevant label.[/quote]Yep, same process here with TFS.Fri, 07 May 2010 08:59:01 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]jacroberts (5/7/2010)[/b][hr][quote][b]Grant Fritchey (5/7/2010)[/b][hr]Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.[/quote]When we release to an environment we 'label' the files in PVCS so at a later date we can get any release that has gone into any environment just by getting the files by the relevant label.[/quote]We cut a branch (we use Perforce) at "code freeze". Contained in that code branch is the application code (rich client, web tier, BLL, database changes, etc.) The schema creation (for new customers/databases) and update (for existing customers/databases) SQL scripts are then used for QA and eventual deployment. We also version stamp the schema in each database. So the upgrade script will fail right out of the gate if it is being applied to a database that is not the correct schema version. [u]ALL[/u] changes to the database are done via scripts. So we know exactly what is running in production.Fri, 07 May 2010 08:51:06 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]Grant Fritchey (5/7/2010)[/b][hr]Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.[/quote]When we release to an environment we 'label' the files in PVCS so at a later date we can get any release that has gone into any environment just by getting the files by the relevant label.Fri, 07 May 2010 08:26:53 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxMakes sense.Of course, I will add that meeting that level assurance is more important in our environment where there are only 3 IT professionals running the databases, none strictly speaking a pure-dba type (perhaps only one with any dba experience at all... no prizes for guessing who). Unfortunately it's impractical for me to be the only person to roll out scripts, as I'm not always in the office, so these sorts of setups are extremely beneficial in our smaller shop, where a high level of assurance is required, but there aren't the staff :)Fri, 07 May 2010 06:51:36 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]mike.renwick-894639 (5/7/2010)[/b][hr]Thanks for +ve comments,Anyone interested in sample code of what we did, take a look herehttp://www.mediafire.com/?eim2mydnjtiDisclaimered as you'd expect.Regarding the ongoing debate about "proper" source control usage, I think a lot of people are indeed missing something. I haven't seen anyone explain adequately how they prevent and detect accidents when rolling out changes to their production servers (e.g. authorized changes by dbo level users), nor how they can prove that what is in their source repository is exactly and only what is in their production environment. [/quote]There are no guarantees in life. But, in an attempt to meet that level of assurance you're asking for, we only deploy our code out of source control and access to databases past development are controlled. There is no dbo level access except from the DBA's. I wouldn't say we're at 100% compliance across all databases, but we're doing very well and have a high, call it 99%, assurance that what we think is in production, is in production.[quote]Another example of where this sort of automation is useful is that by scripting to a folder and running a compare against a baseline, you can evaluate the performance and accuracy of a rollout by dba staff, and also have an exact list of changes made in a rollout that you can append to your change control schedule to show that what was meant to be changed is exactly what was changed. This captures those last minute "saves" or "tweaks" that would otherwise be invisible.Finally, this also allows you to capture changes to more esoteric areas that in some environments are considered beyond the scope of a sql developers source control and falling into the reactive dba world, e.g. tweaked fill factors on indexes, statistics, etc, and can capture objects and settings that might not normally be created via a script (for example, mail profiles). I wouldn't call the process here particularly onerous- I wrote the attached scripts in a day without extensive powershell experience. [/quote]I agree as far as automation goes. Also, we make it a point of retaining the scripts that were generated for the final rollout to production, in case there's any question in the future what it was that got deployed.Fri, 07 May 2010 06:15:34 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxThanks for +ve comments,Anyone interested in sample code of what we did, take a look herehttp://www.mediafire.com/?eim2mydnjtiDisclaimered as you'd expect.Regarding the ongoing debate about "proper" source control usage, I think a lot of people are indeed missing something. I haven't seen anyone explain adequately how they prevent and detect accidents when rolling out changes to their production servers (e.g. authorized changes by dbo level users), nor how they can prove that what is in their source repository is exactly and only what is in their production environment. Another example of where this sort of automation is useful is that by scripting to a folder and running a compare against a baseline, you can evaluate the performance and accuracy of a rollout by dba staff, and also have an exact list of changes made in a rollout that you can append to your change control schedule to show that what was meant to be changed is exactly what was changed. This captures those last minute "saves" or "tweaks" that would otherwise be invisible.Finally, this also allows you to capture changes to more esoteric areas that in some environments are considered beyond the scope of a sql developers source control and falling into the reactive dba world, e.g. tweaked fill factors on indexes, statistics, etc, and can capture objects and settings that might not normally be created via a script (for example, mail profiles). I wouldn't call the process here particularly onerous- I wrote the attached scripts in a day without extensive powershell experience. Fri, 07 May 2010 03:04:26 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]Andy Llewellyn (5/6/2010)[/b][hr]We use RedGate SQL Compare to create all the scripts for a DB and this structure is then held in Subversion. We then develop using QA (or whatever suits the developer). When we need to commit back to the branch then the first step is to compare the script folder with the development DB and synchronise any scripts that need it. Then you commit to subversion in the normal way.We find this works well for our needs at the moment.[/quote]Andy,Have you had a chance to try SQL Source Control? This should save you the additional steps of having to use SQL Compare to compare your dev DB to a scripts folder and commit to SVN, as it integrates this as a single action in SQL Server Management Studio. It's in early access at the moment, so I wouldn't recommend relying on it in a production environment, but I'd really welcome any feedback on the tool and the workflow.http://www.red-gate.com/products/SQL_Source_Control/index.htmDavid AtkinsonProduct ManagerRed Gate SoftwareFri, 07 May 2010 01:53:15 GMTDavid Atkinsonhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxGreat job Mike! This will really help us.I’ve been looking for something like this. Now we can export our schema setup to a folder every night and fire off an SVN commit afterwards if there are changes. This would help keep a constantly versioned set of scripts for our databases.Up til now I'm the only dba in our company that does this (there are three of us) and only when I take the time. This way we can not only do it for our own internal databases but all those maintained by our 3rd party software. Occasionally upgrades break our own scripts and having tools like this would allow us to quickly do diffs on the scripts to find the exact changes.Thanks!Thu, 06 May 2010 14:09:35 GMTMatt Pennerhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]Jon McGhee (5/6/2010)[/b][hr]Wow, that's really labor intensive! Compare that to the integration between a visual studio c# project and visual source safe, where the checkout/checkin can be done with a mouse click.[/quote]Not really.I guess it might be labor intensive if that's all you did all day. But if we're talking several files a day maybe a minute or two maximum?I haven't used visual studio for a number of years but the J++ and C++ versions were slow and clunky. The VSS integration was shaky and the PVCS Tracker integration was worse. I find the SSIS version different enough that they aren't that comparable but I don't think it supports managing non SSIS very wel such as deployment and packaging that I would send out to a customer. As such I am using that sort of checkout anyway.I still support some stuff that uses the old MS Java VM as well. The only thing I use that visual studio for the compiling. Their editor isn't worth the headache.Thu, 06 May 2010 14:03:49 GMTbill page-320204http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxThere is a lot of good info in the posts for this discussion which makes good sense. Some good ideas to add to the arsenal.I have similarly set up many of the things mentioned here for the team I am on. I agree fully that source control is very important to a DB just like any other programming.I created a "homegrown" app. out of necessity that uses SMO to script all of our DB objects. We do this to get our custom headers for the objects that require it. Mainly so the script can be run and re-run with no alterations. (This excludes tables, as mentioned by others previously there are many issues scripting them but we do keep them in source control to compare changes at the least as is scripted) We script:AssembliesFunctionsStoredProceduresTablesTypes_XMLSchemaCollectionsViewsWe utilize SSMS solutions and projects. Where each solution represents a DB. And each project represents the different objects. (Set up this way because of SSMS's lack of robustness in its solution/project explorer)So we could have:Solution - "NameOfDB"..Project - Assemblies..Project - Functions..Project - StoredProcedures..Project - Tables..Project - Types_XMLSchemaCollections..Project - ViewsSolution - "NameOfOtherDB"..Project - Assemblies..Project - Functions..Project - StoredProcedures..Project - Tables..Project - Types_XMLSchemaCollections..Project - Views(SSMS solutions and projects[b] LACK A LOT [/b]of capabilities that you would find with other similar solutions and projects like for C# or VB.NET but it is better than nothing. But MS [b][u]should[/u][/b] have done much better.)All DB work is done from these scripted source files. Checkout to make changes, apply to development db ONLY. Check in when complete.When we are ready to migrate a release we script the whole development DB out again and compare to VSS, disregarding specifically named objects that reside on dev only. If they match we are good. If they don't the developer is notified to correct.But to actually migrate these we use SQL compare tools to create a single script to migrate version changes. This handles all the relation issues, etc. so it is the easy way out. Thu, 06 May 2010 13:49:09 GMTAdam Gojdashttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]bill page-320204 (5/6/2010)[/b][hr]Am I missing something here?We use SourceSafe. We keep each proc separate. When I need to change one, I check it out, modify it, paste it into QueryAnalyzer or Studio depending on which version of server, run it and test it. When everything is cool, I check it back in. The grants are role, not user based so there aren't that many and are in the scripts that are housed in SourceSafe. Seems pretty simple.I do essentially the same thing from java code to DTS/SSIS source to documentation. A release to a customer is built out of SourceSafe.[/quote]Essentially, the same thing that we do. For each build, we concatenate all of the changes together using a utility I developed (see earlier posting), give that to QA for proper testing, and eventually used to upgrade all customer databases.No, you're not missing something. It just appears that a number of individuals can't get their head around a proper source control system.Thu, 06 May 2010 13:35:47 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxI also home-brewed a solution with C# (SMO) and powershell... it is an important thing to do and it would be pretty awesome if I didn't need to have spent my own time creating it.This is on my top 5 list of things I wish came with SQL Server out of the box.native versioning... keep X versions of every stored proc (X being configurable)exec dbo.usp_blahblah @myparam = 100; -- would run currentexec dbo.usp_blahblah @myparam = 100 {1}; --- would run previous.exec dbo.usp_blahblah @myparam = 100 {2} ; --- would run two versions ago.... kind of like error logs.Get to work MS!Thu, 06 May 2010 13:35:21 GMTSQLBOThttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxWow, that's really labor intensive! Compare that to the integration between a visual studio c# project and visual source safe, where the checkout/checkin can be done with a mouse click.Thu, 06 May 2010 13:34:08 GMTJon McGheehttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]bill page-320204 (5/6/2010)[/b][hr]Am I missing something here?We use SourceSafe. We keep each proc separate. When I need to change one, I check it out, modify it, paste it into QueryAnalyzer or Studio depending on which version of server, run it and test it. When everything is cool, I check it back in. The grants are role, not user based so there aren't that many and are in the scripts that are housed in SourceSafe. Seems pretty simple.I do essentially the same thing from java code to DTS/SSIS source to documentation. A release to a customer is built out of SourceSafe.[/quote]It is the same thing really, this was just how to get the code into a VC system as individual files if they weren't created there in the first place. Once they are in VC you do just need to check out the file, edit it then check it back in again as you said.Thu, 06 May 2010 13:29:00 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxAm I missing something here?We use SourceSafe. We keep each proc separate. When I need to change one, I check it out, modify it, paste it into QueryAnalyzer or Studio depending on which version of server, run it and test it. When everything is cool, I check it back in. The grants are role, not user based so there aren't that many and are in the scripts that are housed in SourceSafe. Seems pretty simple.I do essentially the same thing from java code to DTS/SSIS source to documentation. A release to a customer is built out of SourceSafe.Thu, 06 May 2010 13:16:01 GMTbill page-320204http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxPersonally I used SSIS to generate an if exists drop, pull out the text from sys.syscomments, and write it all out to text files. We scripted out > 2000 objects in ~ 15mins.NateThu, 06 May 2010 11:00:10 GMTnhamiltonhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]jacroberts (5/6/2010)[/b][hr][quote][b]CirquedeSQLeil (5/6/2010)[/b][hr]I would have like to see a more in-depth article on the facets of the scripts. I think this would have added more value to the article.[/quote]I didn't want to go into too much detail about other things the script could do as I wanted to make the article simple, straight forward and brief so people could just use it to get their code in a state to put in VC. In retrospect it might have been a good idea put more detail in.[/quote]Fair enough. At least it was considered.:-DThu, 06 May 2010 10:50:46 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxI think it was a useful article and it's certainly sparked off a lively and fascinating debate.Thu, 06 May 2010 10:49:02 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]CirquedeSQLeil (5/6/2010)[/b][hr]I would have like to see a more in-depth article on the facets of the scripts. I think this would have added more value to the article.[/quote]I didn't want to go into too much detail about other things the script could do as I wanted to make the article simple, straight forward and brief so people could just use it to get their code in a state to put in VC. In retrospect it might have been a good idea put more detail in.Thu, 06 May 2010 10:47:59 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxI would have like to see a more in-depth article on the facets of the scripts. I think this would have added more value to the article.Thu, 06 May 2010 10:41:50 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]Phil Factor (5/6/2010)[/b][hr]I really enjoy seeing scripts like this, since the whole topic of scripting is fascinating. What I really like about this is that someone has gone ahead and brewed a solution that fits their requirements like a glove. It is neat, because it will work with any version of SQL Server 2000+. I wouldn't like anyone to think that this is a recommended general solution, though. SMO is the way to go! (I still occasionally use DMO)[/quote]I agree with Phil here. It is nice to see scripts designed to source control a database. I would prefer to use SMO and something more flexible.Thu, 06 May 2010 10:38:49 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]bphipps-931675 (5/6/2010)[/b][hr]But you lose the benefits of a change log that manual management of your procedures provides.[/quote]In the version of the procedure I have adds and additional call to another stored procedure at the bottom of each script which calls an 'UpdateChangecatalog' stored procedure, this logs to a table the date/time, username, sproc filename, version and a user comment for the stored procedure installed. PVCS fills in the filename and version (revision). The procedure UpdateChangeCatalog works out the username and datetime, .so no need for any manual management.:[code="sql"]-- ************************************************-- Update the Change Log-- ************************************************EXEC UpdateChangeCatalog '$Workfile:$', '$Revision:$', 'Script to install procedure myProcedure'GO[/code]Another useful thing we do is add the revision number to the start of the procedure, just above the ALTER statement so we can simply look on any database and instantly see which version of a stored procedure is installed, again PVCS maintains the version number so no manual intervention needed: e.g.:[code="sql"]IF Object_Id(N'mySproc', N'P') IS NULLBEGIN EXEC ('CREATE PROCEDURE [dbo].[mySproc] AS BEGIN SELECT 1 END')ENDGO GO -- **************************************-- $Revision: 1.0 $ -- **************************************ALTER PROCEDURE [dbo].[mySproc]( [/code]Thu, 06 May 2010 10:17:25 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxhaha- sorry didn't realise grasshopper was a forum participation level thing, sorry about that! :)That all makes sense, although it's a shame you can't subcategorise scripts into different types of objects and you end up with a giant alphabetized list (I guess prefixing tables with tbl_ etc mitigates that somewhat).More generally though, you become reliant on having a sure footed and trusted DBA in your setup, as any mistake he/she makes is going to be practically indetectable to you? An example might be an accidentally dropped stored procedure or an entirely new but unwanted object being created in the database, or worse, some changes made by a dba post your testing. I realise this is a management issue to some extent and this often heads into controversy that usually ends with sentences "but ultimately you have to trust the dba", but mistakes do happen, and in our shop it's been useful to have an objective third party process that corroborates our claim that what is in source is actually what is in production. (I'll accept that a dba can of course alter/circumvent any process that we create to maintain this control)Thu, 06 May 2010 10:10:32 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote]If you're referring to DBPro, yeah, we've been using it for years, quite successfully too. It allows us to move our databases from source control to dev/qa/staging & out to prod. Great stuff. I'm actively evaluating 2010 now.[/quote]No, everyone assumes that when I say "Database Project". I actually hated DBPro. It forced you into a methodology and didn't let you structure the project how you wanted. It was overly complex (although flexible). It didn't let you use "ALTER" statements or structure scripts the way YOU want to. I guess I have to use it now since they did away with the Database Project template.Thu, 06 May 2010 10:01:43 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxThe way we did it, is creating procedures and udfs script from generate script functionality of sql 2008, per file.The script didn't have Drop Function or proc script that we added through command script looking each file and adding drop script on the top of each file.Then used ssms solution in sql management, connect it to our TFS where all the files are and work on procs from sql management going forward.There is one cool thing we do(this is something I never did in my previous companies), with every build we drop and create all functions and procs. For this we use batch script which create a file with all the procs and udfs. basically it copies the text from all the sp and udf files into one file to execute. This way our database is always in sync with what was in the build.Thu, 06 May 2010 09:53:53 GMTveena_kediahttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]tymberwyld (5/6/2010)[/b][hr]I'm really surprised that no one has mentioned using "Database Projects" in Visual Studio (2003 - 2008). Unfortunately, Microsoft has removed this Project Template with VS 2010+ but I plan to revive it and make some Add-ins to make it more robust. Microsoft does give us the new "SQL Projects" but they are horrible and the project forces you into a specific folder structure and method of scripting.For example, I have been using Database Projects since 2002 integrated them into Source Safe, TFS, and now SVN. It's nice because we always had developers going in a mucking around in a "DEV" database and there were always times when one dev would overwrite another's changes. With DB Projects, it eliminated this because either only one dev could checkout the script at a time, or they would at least get "merge" capabilities. It was a nice template and could be used for any database (not just SQL Server) and allowed you to run the scripts right from Visual Studio (so you could test them). After we had everything in place, I just used a batch file to run the scripts, however, you could probably do something more robust these days with SMO and Powershell.[/quote]If you're referring to DBPro, yeah, we've been using it for years, quite successfully too. It allows us to move our databases from source control to dev/qa/staging & out to prod. Great stuff. I'm actively evaluating 2010 now.Thu, 06 May 2010 09:51:16 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxBut you lose the benefits of a change log that manual management of your procedures provides.Thu, 06 May 2010 09:50:20 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxNewbie,-Is there anyway of categorising into different sorts of scripts (/subfolders?) Or do they all just end up under 'queries'?This is where having multiple projects comes into play. I create a different project for different scripts. You can have multiple projects within one solution. Or if things are completely different you can have a different solution altogether.For instance, I have a solution for Accounts Payable, with just one project in it.For HR, I have a Payroll project, a separate Payroll Register project and then and final project for employee stuff. all in one solution.-How do you reconcile what is actually in the database with your project? e.g. a mistake in rolling out (wrong version), or perhaps a new object added accidentally by the deployment team?We use subversion. In production, the DBA only deploys scripts from tags that I (the Team Lead) create in subversion. No one else has authority to deploy to production, so I know that my tags are in sync with production. The dev and test envrironments are a little more lax, but the rule in the office is that what is in subversion rules. Anything else is considered a test and can be blown away with what comes out of subversion. It's up to us to ensure that we commit our changes into subversion. As MissPost stated earlier, this is a a management issue-Also, how do you handle static/reference data? Generally, we create a table and store it in there, but this is something that we haven't had to address much. Our current process for this matter needs additional work. In the future one of our projects is going to be to create an application so that this kind of stuff can be owned and maintained by the users instead of applicationDevelopment/Operations.Thu, 06 May 2010 09:47:49 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]bphipps-931675 (5/6/2010)[/b][hr]True about the walking in late, for that issue but I would still want to logically arrange the various scripts into projects to make them easier to find and keep track of...As for our script creation process, we save them as either creates or alters. When they get to production the DBA simply changes it to a create for the initial run, if necessary, but typically leave them as alters.When we first started we made them drop/create scripts but the losing security was a problem, so we switched to just using alters.[/quote]If you used the method that this script generation used no manual editing would be necessary as it creates a tiny skeleton procedure if it does not already exist then alters it to the correct version.Thu, 06 May 2010 09:42:55 GMTJonathan AC Robertshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxGrasshopper, Got a few questions about your SSMS approach - -Is there anyway of categorising into different sorts of scripts (/subfolders?) Or do they all just end up under 'queries'?-How do you reconcile what is actually in the database with your project? e.g. a mistake in rolling out (wrong version), or perhaps a new object added accidentally by the deployment team?-Also, how do you handle static/reference data?Thu, 06 May 2010 09:37:38 GMTmike.renwick-894639http://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspxTrue about the walking in late, for that issue but I would still want to logically arrange the various scripts into projects to make them easier to find and keep track of...As for our script creation process, we save them as either creates or alters. When they get to production the DBA simply changes it to a create for the initial run, if necessary, but typically leave them as alters.When we first started we made them drop/create scripts but the losing security was a problem, so we switched to just using alters.Thu, 06 May 2010 09:27:34 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic916639-2691-1.aspx[quote][b]bphipps-931675 (5/6/2010)[/b][hr]I guess this is where having a plan in place of how you are going to manage your scripts before you create 600 scripts is important. [/quote]It is also possible to end up being assigned to a project where this hasn't been planned and there are already 600 stored procedures not in source control as separate modules.[quote][b]bphipps-931675 (5/6/2010)[/b][hr]As for security, our scripts include grants at the bottom, and we generally only do alters so that security is not lost when we make updates.[/quote]When there is a new stored procedure you have to create it as it's not there to alter. Do run a separate script for initial install to subsequent updates?Thu, 06 May 2010 09:21:53 GMTJonathan AC Roberts