 |
 | Sync'ing SQL server and SVN can be scripted
unick | 6:46 23 Nov '09 |
|
|
|
| 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*(?<Statement>CREATE\\s+PROC(?:EDURE)?\\s+(<Owner>[^\\.\\s]*?\\.)?(?<Name>[^\\s]*?)[(\\s]+.*?)'\\s+END\\s*GO\\s", scriptInput, outputPath, includeOwner);
Cheers,
Eric.
|
|
|
|
|
|
|
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
|
|
|
|
|
 | 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:
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
|
|
|
|
|
| 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.
|
|
|
|
|
| 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...
|
|
|
|
|
| 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:
WriteGroup("Tables", "BEGIN\\s+(?CREATE TABLE \\[(?.*?)\\]\\.\\[(?.*?)\\].*?)END\\s+GO\\s", scriptInput, outputPath, includeOwner);
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);
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);
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);
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);
}
|
|
|
|
|
|
|
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
|
|
|
|
|
| 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,
|
|
|
|
|
|
|
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
|
|
|
|
|
|
|
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!
|
|
|
|
|
|
|
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>
|
|
|
|
|
|
|
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.
|
|
|
|
|
|
|
|
|
|
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?
|
|
|
|
|
|
|
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
|
|
|
|
|
| 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.
|
|
|
|
|
| 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
|
|
|
|
|
| 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!
|
|
|
|
|
| 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?
|
|
|
|
|
|
|
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.
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";
|
|
|
|
|
| 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:
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?
|
|
|
|
|
|
|
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.
|
|
|
|
|
| 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
|
|
|
|
|
|
|
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);
|
|
|
|
|
