Email Password Remember me?  Lost your password?

• Download executable - 9.83 Kb
• Download source code - 11 Kb

Introduction

If you are using a source control system to manage your project's source code, you have probably wondered if you could do the same with your database. You can! Here's my take at it: ScriptDB4Svn does what its name suggests: it scripts your database for usage in a source control system. Because I use SVN with the TortoiseSVN shell extensions, I've targeted this project to be used with SVN, but you can probably use it with other source control systems.

Please note that the tool relies upon the Scripting tool from Microsoft which was shipped with SQL Server 2000, and will only have been installed on your system if you chose for backward compatibility with SQL Server 7.0 during the installation. If you are missing the tool (scptxfr.exe) it can be found on the SQL Server 2000 installation CD.

The tool was tested on SQL Server 2000 only. I don't know if it works with SQL Server 2005.

What does ScriptDB4Svn do exactly?

The tool scripts Microsoft SQL Server tables, relationships, views, stored procedures, user defined types, defaults, rules, triggers, functions and data into individual .sql files. Those individual files can then be added to your source control system and become available for versioning. The tool can also be used to automatically create a clean database from generated scripts, which can be very useful for integration in any tools like NAnt, MSBuild, CC.net etc.

What are the benefits?

There are numerous benefits; these are just the ones I came up with. If you have any additions, let me know!

• You are aware of other developer�s database changes and they are aware of yours. Your databases will remain in-sync with each other.
• See who added what table, column, trigger, or broke your stored procedure. (�SVN Blame� shows what was added/changed when and by whom)
• Create clean databases from generated scripts for easy deployments to test and production environments.
• Add database script execution to continuous integration scripts.
• If you lose anything in your database, you have multiple scripted backups so you can easily restore it.
• Tagging: When releasing a new version of your program, include the exact version of the database and its data within the same SVN Tag / branch!

Working with ScriptDB4Svn - Manual

Put a copy of ScriptDB4SVN.exe and its .config file in the folder where you want your database scripted. Open the .config file and make proper changes to it. The file contains comments that will tell you what the different settings are for.

If you are going to use the tool in your development team, I recommend you to have everyone in your team read this article. I will save you some headaches!

How to manage database changes

1. If any project-member makes a change to anything in their version of the database, they simply run the scripting tool to regenerate the scripts, and then commit the changes to the SVN repository.
2. Upon executing an SVN Update command, any updated scripts are copied to other team members� local versions. Once they run the scripting tool again, the database changes are reflected in Windows Explorer by TortoiseSVN. By viewing the TortoiseSVN Diff view of the table/view/whatever, the exact changes made by the developer become visible.
3. The other team members make the appropriate changes to their local databases, either by executing the new script, or by making the changes manually in SQL Server.
4. Once the tool generates scripts identical to the ones in the repository, the databases are in-sync again!

A paradoxical thing about this approach is that changes made by other developers only become visible after the scripts are regenerated locally. This way, it appears that the remote database changes have been �undone� locally, while in fact the remote changes haven�t been processed (copied) yet. Any �uncommitted� changes a developer has made to their local database, will appear in the same way remote developers� �unprocessed� changes appear in Windows Explorer.

Handling/resolving data differences

Resolving data conflict can be a pain because of relationships that assure the relational integrity of the data is maintained. If the data in more than one table has changed, simply executing a delete statement or inserting data can cause errors. In these situations it is quite useful to remove those constraints, update the data and then recreate the relationships. When done, the dropped constraints can easily be re-created from the Relationship scripts. Follow these steps to avoid annoyances:

1. SVN Revert your version of the data script to the HEAD ('their') revision.
2. Open the script in Query Analyzer, connect to the right database
3. Execute the script
4. If step 3 generated an error, find out what constraint caused the error, above the DELETE FROM the_current_table line, add: ALTER TABLE table_that_contains_the_constraint DROP CONSTRAINT FK_the_name_of_the_annoying_constraint, now try step 3 again. Repeat this until the script executes without errors.
5. Repeat steps 1 to 4 until all data changes have been made.
6. Rescript your database. If you dropped any constraints in step 3, recreate them by executing the appropriate relationship scripts (SVN Revert the file, strip the lines for constraints that still exist, execute)

Important note: Watch out for relationships that have CASCADE DELETE's enabled! Deleting data in a source controlled table could then result in the loss of data of other -possibly not source controlled- tables!!

Handling/resolving minor script differences

There are a couple of situations where differences in generated scripts can occur, when they aren�t really different. Here�s how you can resolve them:

1. Index/PK names: To resolve a difference in the name of a Primary key or index, in SQL Query Analyzer, execute: sp_rename 'myname', 'theirname'
2. CASE differences in table & column names: execute sp_rename 'dbo.Table.someColumn', 'SomeColumn'
3. CASE differences in CREATE statements: for some odd reason SQL Server remembers the case of the CREATE statement when it was executed. For any other object than tables, SVN Revert your version of the .sql file to the HEAD revision, open the file in Query Analyzer and execute it.
4. COLLATION differences: these can be a real pain. If the developers in a team are using different collation settings in SQL Server, any textual columns like varchar and text will contain different collations. Make sure to all use the same collation settings! If you aren�t bothered by different collation settings, you can optionally turn �IgnoreCollation� on in the .config file of the tool.

Handling conflicts

Sometimes conflicts can occur in the script files. Usually this is the result of not committing the generated scripts after local database changes have been made. The best way to resolve conflicts is:

1. Regenerate your scripts, any conflict files will be removed automatically.
2. Now, check the Diff and make any pending changes to your database.
3. Regenerate the scripts again and commit your file.

Best practices

Follow these guidelines to prevent yourself from getting into problems.

• Commit local database changes to the SVN repository as soon as possible, but only if any programming code that relies upon the DB change is committed at the same time. If the change can break code other developers are using, provide code that fixes those problems within the same commit.
• Schedule an automatic SVN Update, and the script tool to be executed at least once a day so you will notice committed database changes as soon as possible. 9 AM is a nice time for this: new day, new scripts.
• Process other users� database changes immediately when you notice them. Waiting to do so will make your life hard.
• Put a copy of the ScriptDB4SVN.exe in the folder that contains the scripts and Add it to the repository. If an updated version becomes available, you can simply overwrite it with the new version. Other developers automatically take over the new version upon SVN Update.
• If you deleted a table/view/anything from your database, don�t remove the corresponding script file! The contents of the script file will automatically be cleared by the tool. This way the deletion will be noticed by other developers. Once -everybody- has deleted the item, the file can be removed from SVN.

History

• October 8, 2006: Original article posted.

You must Sign In to use this message board.

Per page 102550

FirstPrevNext

SQL Comments Result in Missing Objects/Scripts Eric Dagenais 12:48 17 Aug '09   Hi, I applied the "Moving from 2000 to 2005" thread patches to get this working with SQL 2005. The problem I'm having is that 3/4 of my stored procedures are not being saved to separate files. They show up in the large single file but the RegEx expression is not detecting them. I've compared the SQL scripts of those that are working and those that are not -- it turns out that 3/4 of our stored procedures are using comments right after the "EXEC dbo.sp_executesql @statement = N'" line. We have a dozen databases with 1000s of stored procs, so moving the comments elsewhere is not feasible. e.g. IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stored_proc_name]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author:         John Doe -- Create date: March 27, 2008 -- Description:     Get current preferences and details -- ============================================= CREATE PROCEDURE [dbo].[stored_proc_name] Anyone know how to modify the following WriteGroup's RegEx in order to match SP's like the one above? // Loop through all the SP's WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?&lt;Statement&gt;CREATE\\s+PROC(?:EDURE)?\\s+(&lt;Owner&gt;[^\\.\\s]*?\\.)?(?&lt;Name&gt;[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner); Cheers, Eric. Sign In·View Thread·PermaLink Re: SQL Comments Result in Missing Objects/Scripts ken matesich 20:22 20 Sep '09   The way that I handled this case is with the following additions to the Regex and WriteGroup method. // Alternate method for Stored Procedures with leading comments. WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'(?<Comment>.*?)(?<Statement>CREATE\\s+PROC(?:EDURE)?\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner); Now the comment(s) is held in the match with a named identifier, and can be concatenated with the staement in WriteGroup(). string comment = match.Groups["Comment"].Value; // Add the openning comment, if it exists. statement = comment + dropStatement + statement; I also found this statement useful. // Replace escaped single quotes placed around literals by wizard. statement = statement.Replace("''", "'"); Hope this helps someone. Ken Sign In·View Thread·PermaLink PROBLEM with SQL 2005/8 ForeignKeys and RegExp Bernd Wessels 19:55 29 Apr '09   Hello together, I have a problem using the code together with the Microsoft SQL Server Database Publishing Wizard 1.3. The code works fine for tables, but it does not extract the foreign key constraints. They are in the big SQL file like this: /****** Object: ForeignKey [FK_Song_Tag_Tag] Script Date: 04/30/2009 15:22:02 ******/ IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Song_Tag_Tag]') AND parent_object_id = OBJECT_ID(N'[dbo].[Song_Tag]')) ALTER TABLE [dbo].[Song_Tag] WITH CHECK ADD CONSTRAINT [FK_Song_Tag_Tag] FOREIGN KEY([Id_Tag]) REFERENCES [dbo].[Tag] ([Id_Tag]) GO IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Song_Tag_Tag]') AND parent_object_id = OBJECT_ID(N'[dbo].[Song_Tag]')) ALTER TABLE [dbo].[Song_Tag] CHECK CONSTRAINT [FK_Song_Tag_Tag] GO But the code does not extract them. So I tried to create a new RegExp like this: WriteGroup("ForeignKeys", "(?ALTER TABLE\\s+\\[(?.*?)\\]\\.\\[(?.*?)\\]\\s+WITH\\s+CHECK\\s+ADD\\s+CONSTRAINT\\s+\\[(?.*?)\\]\\s+FOREIGN\\s+KEY\\(.*\\)\\s+REFERENCES\\s+\\[(?.*?)\\]\\.\\[(?.*?)\\]\\s+\\(.*\\)?)\\s+GO\\s", scriptInput, outputPath, false); But this does not match. Can anybody help me with the correct RegExp or a better solution? Best regards, Bernd Sign In·View Thread·PermaLink Problems executing scripts Member 660218 6:25 20 Mar '09   Once a user has downloaded updated SQL scripts from SVN, how will they know which order to run them in? Seems like a tricky problem to me. Given the database continuously logs all changes, they ought to make it automatically keep a set of script files in the filing system up to date. Then you wouldn't even need to re-generate the files and they'd be exactly in synch with source code. Sign In·View Thread·PermaLink Looks Great... but the new tool is the Database Publishing Wizard John Albrecht 8:07 14 Jan '09   Looks like a great tool/code and something I wont to get running in our dev enviroment. Ive been doing a bit of searching around the web and discovered Microsoft Database Publishing Wizard: Microsoft Database Publishing Wizard 1.1[^] This tool also scripts a database to a single file and has command line arguments. It looks like an updated version of the orginal Microsoft Tool on the 2000 cd! Looking at your code it looks like you take a single file and split it up so in theory it shouldnt be too much work to upgrade to the latest Microsoft Tool? What do you think? Blog Post showing how the unsplit file can be Saved into SNV with[^] I think the Source Controlling Databases is something that lots of devs are missing, IMHO this is something that could be turned into a commercial application if it is given a full UI to help manage Merging... Sign In·View Thread·PermaLink 5.00/5 Using with SQL 2008 davidm99 5:04 6 Jan '09   This is pretty similar to the instructions posted by peter.stuart for getting it to work for SQl2005, the main difference is you need to make sure you are running the Database Publishing Wizard 1.3, and update the path to use that exe instead of the 1.1 exe. 1.3 is installed with Visual Studio 2008 SP1, or you can install it manually from: http://go.microsoft.com/fwlink/?LinkId=119368 [^] You can check to see if it's installed by looking in Add/Remove programs for "Microsoft Sql Server Database Publishing Wizard 1.3". After this is installed change the SqlScriptExecutablePath path in the App.config to "C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2\SqlPubWiz.exe" Then make all the changes that peter.stuart posted below, here is a summary of those changes: Change the following line from: ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "/s " + serverName + " /d " + dbName + " /f " + scriptFile + " " + switches); To: ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "script -S " + serverName + " -d " + dbName + " /f " + scriptFile); Change the regular expressions to these: // Loop through all the Tables WriteGroup("Tables", "BEGIN\\s+(?CREATE TABLE \\[(?.*?)\\]\\.\\[(?.*?)\\].*?)END\\s+GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the Views WriteGroup("Views", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?CREATE\\s+VIEW\\s+([^\\.\\s]*?\\.)?(?.*?)\\s+AS\\s+.*?)'\\s*GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the SP's WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?CREATE\\s+PROC(?:EDURE)?\\s+([^\\.\\s]*?\\.)?(?[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the Functions using a regular expression WriteGroup("Functions", "BEGIN\\s+execute\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?CREATE\\s+FUNCTION?\\s+(?[^\\.\\s]*?\\.)?(?[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the Triggers WriteGroup("Triggers", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?CREATE\\s+TRIGGER\\s+(?[^\\.\\s]*?\\.)(?[^\\s]*?)\\s+ON\\s+.*?)'\\s+GO\\s", scriptInput, outputPath, false); Another change I made was adding this right after the checking of the output path if it contains spaces, this way it will create the output path if it doesnt already exist (good for first time ran): if (!Directory.Exists(outputPath)) { Directory.CreateDirectory(outputPath); } I also didn't like the new Guid for the script file that contains everything, so I changed: string scriptFile = outputPath + "\\" + Guid.NewGuid().ToString() + ".sql"; To this: string scriptFile = outputPath + "\\" + dbName + ".sql"; And added this right after the Directory check I added above so that it deletes the file before the Publishing Wizard runs again: if (File.Exists(scriptFile)) { File.Delete(scriptFile); } Sign In·View Thread·PermaLink Re: Using with SQL 2008 EddieSpooner 22:21 14 Apr '09   Thanks davidm99, saved me some time this morning. I had problems getting it to work first time as I use the Date datatype which is in SQL2008 but not SQL2005. I found making a slight mod made your changes work even better ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "script -S " + serverName + " -d " + dbName + " /f " + scriptFile + " -targetserver 2008"); The -targetserver 2008 seems to be poorly documented but does work. Hope this helps anyone stumbling across this page. HTH Ed Sign In·View Thread·PermaLink Moving from 2000 to 2005 rightfield 3:33 13 Aug '08   We are just about to move from 2000 to 2005. I just found this tool and would be interested in getting familiar with it now and using it in production when we move to 2005. Has anyone tried it with 2005? Warm regards, Sign In·View Thread·PermaLink Re: Moving from 2000 to 2005 beckerben 4:12 24 Aug '08   Yes, I tried it in SQL 2005, works just fine from what I can tell, scripts created and appear right! Thanks for the tool, this will be very handy and offer a layer of code protection which we didn't have before. Becker Sign In·View Thread·PermaLink 4.00/5 Re: Moving from 2000 to 2005 peter.stuart 10:48 12 Oct '08   I found that some databases under 2005 would work but others wouldn't. The root of the problem seems to be that scptxfr has trouble with 2005. A bit of googling led me to the following tool. http://download.microsoft.com/download/a/1/a/a1a0cfc2-5942-409d-abef-0b4980b9d6a0/DatabasePublishingWizard.msi Using this in connection with ScriptDB4SVN requires a minor mod to the source (Program.cs). Change the following line from: ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "/s " + serverName + " /d " + dbName + " /f " + scriptFile + " " + switches); To: ProcessStartInfo info = new ProcessStartInfo(sqlScriptExecutable, "script -S " + serverName + " -d " + dbName + " /f " + scriptFile); Where sqlScriptExecutable has now be set in the config file to be "C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" or whereever you have put the publishing wizard. I hope this helps. I am still familarising myself with ScriptDB4SVN but so far it looks really promising. Thanks! Sign In·View Thread·PermaLink 3.67/5 Re: Moving from 2000 to 2005 peter.stuart 2:56 14 Oct '08   Further to my last message it seems the regular expressions need tweaking if you use the publish wizard as the script script differs slightly fom that generated by scptxfr <pre>// Loop through all the Tables WriteGroup("Tables", "BEGIN\\s+(?<Statement>CREATE TABLE \\[(?<Owner>.*?)\\]\\.\\[(?<Name>.*?)\\].*?)END\\s+GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the Views WriteGroup("Views", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+VIEW\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>.*?)\\s+AS\\s+.*?)'\\s*GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the SP's WriteGroup("StoredProcs", "BEGIN\\s+EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+PROC(?:EDURE)?\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the Functions using a regular expression WriteGroup("Functions", "BEGIN\\s+execute\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+FUNCTION?\\s+(?<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner); // Loop through all the Triggers WriteGroup("Triggers", "EXEC\\s+dbo\\.sp_executesql\\s+@statement\\s+=\\s+N'\\s*(?<Statement>CREATE\\s+TRIGGER\\s+(?<Owner>[^\\.\\s]*?\\.)(?<Name>[^\\s]*?)\\s+ON\\s+.*?)'\\s+GO\\s", scriptInput, outputPath, false);</pre> Sign In·View Thread·PermaLink 5.00/5 Re: Moving from 2000 to 2005 hammonddb 6:32 18 Nov '08   Thanks for the upgrade instructions. Would be useful to know of any other tweaks. For info, I've added the -schemaonly switch to the sqlpubwiz invocation which could save a lot of unnecessary output. Still early days for me but, yes, this tool looks very useful. Sign In·View Thread·PermaLink 2.00/5 Re: Moving from 2000 to 2005 sunweichao 23:50 3 Feb '09   thanks you! Sign In·View Thread·PermaLink Re: Moving from 2000 to 2005 Koroshiya 11:21 17 Mar '09   This works for me for the Tables and Stored Procedures. Also works for the views if there are no header comments... I'm sure I can figure out that. What doesn't work for me is the foreign keys. I can't seem to figure out how to resolve it. They are no longer scripted with the table using the SqlPubWiz.exe from what I can tell. so maybe this calls for a new WriteGroup case statement? Sign In·View Thread·PermaLink Re: Moving from 2000 to 2005 Member 1924962 2:58 6 Jul '09   Hi, I am trying to use this utility for a sql server 2005 database, but I am unable to locate <b>scptxfr.exe</b>, Please suggest. Regards, Venk Sign In·View Thread·PermaLink Bug fix (System.NullReferenceException:) White X Dragon 4:34 9 Apr '08   in file Program.cs function Main line 125 should change if (tables2Script.Length > 0) to if (!string.IsNullOrEmpty(tables2Script)) to prevent program crash if key "ScriptTableData" is absent in the config file. Peoples should think. Machines shold work. Sign In·View Thread·PermaLink Has anyone tried SQL Server 2005 ?? budtse 3:13 1 Apr '08   Has anyone tried to use this tool with SQL Server 2005 ? (I'll have a closer look at it myself as soon as i have the time). We are looking for a way to store SCADA applications in SVN, so this would be quite handy for us. A few SCADA systems we use (like Siemens WinCC) are based on SQL Server these days, which makes the use of SVN hard since the whole database must be detached, checked in and re-attached with every change, and most of that data is useless anyway. If anyone has experience with it, i would like to share idease with him/her. regards, Peter Sign In·View Thread·PermaLink 1.00/5 Need an option in the config for sql delete exclusions toxaq 16:03 19 Mar '08   Your program deletes all previous *.sql files in all directories (well deletes in the root and blanks in subfolders). This is assuming that everything in the project folder where you run this executeable is owned by the executable. I had created a version update file for migration (surely one of the reasons to have versioned control databases) and this got nuked before I committed it. Doah! To cope with this I've hard coded a it so it won't delete from a folder called updates now on but it would be cool if you could have an exclusions folder in the config. Just looked and found out it has been nuking my livedata files, my test scripts too... lucky for version control! Thanks though for creating it. It's been very useful otherwise! Sign In·View Thread·PermaLink varbinary(max) problems Member 3858438 9:25 21 Feb '08   I have a "varbinary(max)" data-type in a table def, and scriptdb4svn is generating the following on an exportdb: [value] [varbinary] (-1) NULL , which is invalid sql I can't just substitute an actual number in the column def, since anything over 8000 is rejected. SQL Server won't take an actual length > 8000, hence the "max" keyword. "varbinary(max)" means anything well into gigabyte range. How can I have a varbinary and have scriptdb4svn handle it correctly? Sign In·View Thread·PermaLink Re: varbinary(max) problems Ezweb25 8:34 10 Mar '08   It appears the SQL scripter from SQL Server 2000 doesn't support the varchar(MAX). I hacked together a fix several months ago. In the Program.cs near the bottom, I added the following lines. // Trim trailing \n so output of different servers is the same statement = statement.TrimEnd(new char[] {'\n', '\r'}); if (includeOwner && owner.Length > 0) objectname = owner + "." + objectname; string varbinary = "[varbinary] (-1)"; if (groupName == "Tables" && statement.Contains(varbinary)) statement = statement.Replace(varbinary, "[varbinary] (max)"); string varchar = "[varchar] (-1)"; if (groupName == "Tables" && statement.Contains(varchar)) statement = statement.Replace(varchar, "[varchar] (max)"); string filename = outputPath + "\\" + groupName + "\\" + objectname + ".sql"; Sign In·View Thread·PermaLink 5.00/5 Tries scripting CREATE statements that have been commented out flipdoubt 11:39 6 Feb '08   I have a user-defined function that contains the following in a comment: /* SCRIPTING NOTE: When generating a SQL script for the database, the script creates this function before some of the objects it depends on. To avoid errrors, move the "CREATE FUNCTION dbo.GETUSERFUNCTIONDEPARTMENTS" before "CREATE VIEW dbo.View1". */ Your script detects the creation of a new view and starts scripting a view that contains an error. How would you update this code to ignore create statements that have been commented out? I believe you need to add something to the regular-expression, but I'm not a RegExpert. Any ideas? Sign In·View Thread·PermaLink 1.00/5 Re: Tries scripting CREATE statements that have been commented out flipdoubt 12:02 6 Feb '08   I've found the utility does not like comments in triggers to appear before the "AS". If comments occur before "AS", the comment is included in the object's name. Sign In·View Thread·PermaLink 5.00/5 Regex Issue with View name used for creating the script file... Member 2074228 6:06 18 Jan '08   Some old sql view's are not identified well and makes the code stop processing the database: The issue is: Normal view looks like this: Create View view_name AS Select ... View can also be declared this way: Create View view_name(column_name1, column_name2, ...) AS Select ... Some people prefer to enlist the result column layout in the view declaration section instead of writing the wanted (alias) name in the select statement at the appropriate column/line (example: Select table_name1.column_name1 As col_1) Now the code is prepared for the simple version only, and breaks on the one which lists the columns Since I am not really familiar with regex I cannot really make an appropriate regex for the views. (I do not have VS2005 either) Could you make some modification to detect this declration type as well in the views? Compile a new version or at least give me some hint what to change in the regex and then I will try to get VS2005 and compile it for myself. Thanks a lot, Andrew Sign In·View Thread·PermaLink Re: Regex Issue with View name used for creating the script file... Beetlebub 13:38 4 May '09   I've adjust the Views RegEx to this (includes the WriteGroup method for reference: WriteGroup("Views", "(?<Statement>CREATE VIEW (?<Owner>[^\\.\\s]*?\\.)?(?<Name>.*?)\\s+(?:AS|\\()\\s+.*?)\\s+GO\\s", scriptInput, outputPath, includeOwner); Sign In·View Thread·PermaLink RegEx issue with scripted statement permissions Member 4493550 2:55 10 Dec '07   ?<Statement>CREATE VIEW (?<Owner>[^\\.\\s]*?\\.)?(?<Name>.*?)\\s+AS\\s+.*?)\\s+GO\\s The RegEx for VIEWS above does not exclude statement permissions like these: GRANT CREATE TABLE , CREATE VIEW , CREATE PROCEDURE , DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO [SlydellB] GO I don't have any experience with RegEx. How to fix that? Best Wishes, Robin Sign In·View Thread·PermaLink

Last Visit: 23:08 20 Nov '09     Last Update: 23:08 20 Nov '09 12 Next »