Click here to Skip to main content
Click here to Skip to main content
Search within:



 
Comments & Discussions (7)

Run Scripts Add-In for SQL Server 2005 Management Studio

By , 20 Nov 2008
 

Sample Image - SSMSScriptRunner.jpg

Introduction

The new SQL Server 2005 Management Studio is a great improvement over older SQL Server tools, and even allows one to create a SQL Script project type to organize your SQL scripts with. While this is handy, there is no way to execute all the scripts in your project without opening each one and hitting 'Execute'. I have some projects with quite a lot of scripts, so this can get quite tedious. I decided to write a little add-in for SSMS to do this for me. To write an SSMS add-in is quite easy; just write a console application that accepts string arguments from SSMS and then add it as a external tool on the Tools->External Tools menu.

How the code works

When the console application is invoked, the current project is sent as an argument using the $(ProjectFileName) global variable. The application then makes a backup of the file before opening it. The project file is an XML format file with the extension 'ssmssqlproj'. The application looks for the first Connection object in the file and uses this connection to execute the scripts. It then loops through all the <FileNode> tags to get the SQL scripts included in the project and executes each one. The output is written to the SQL output window using Console.WriteLine().

Installing the add-in

To install the add-in:

  1. Click on the Tools->External Tools menu item
  2. Add a new tool using the Add button
  3. Give it a title such as 'Run Project Scripts'
  4. From the 'Command' edit box, browse to the RunScriptsInProject.exe file and select it
  5. In the 'Arguments' edit box, select $(ProjectFileName)
  6. In the 'Initial Directory' edit box, select $(ProjectDir)
  7. Check the 'Use Output Window' checkbox

Installing

For more information, look at this article where I got most of the idea from. It will also tell you how to add a toolbar button for your external tool.

Conclusion

This is a simple but useful add-in extension for SSMS that might make life a little easier for you. The source code can be modified to create your own add-ins for the IDE to maybe overcome some limitation. Any comments or suggestions are more than welcome.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

 Michael Erasmus
Web Developer
Netherlands Netherlands
Member
I'm 27 years old and currently I'm working as a full time software developer/consultant in Amersfoort, Netherlands

Article Top
 
Sign Up to vote   Poor Excellent
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

 
You must Sign In to use this message board.
Search this forum  
    Spacing  Noise  Layout  Per page   
First Prev Next
GeneralSelecting a database to run a projectmemberCeesCap15 Jul '09 - 1:42 
Is there a possibility to select a database to run a project against?
Sign In·View Thread·Permalink
GeneralNew versionmemberMichael Erasmus20 Nov '08 - 22:57 
Thanks andr5 and Member 4304651. I have incorporated your changes and uploaded a new version of the project.
@Member 4304651 - The reason I used a do..while is because some of my scripts do not have a GO statement. I have kept the loop like it is, but i do now check if the text is empty before I execute it.
Sign In·View Thread·Permalink
GeneralTwo small enhancements:memberandr529 Jul '08 - 0:08 
I got two enhancements to use relative script paths and to treat my üöä characters right:
 
I replaced:
 
fileName = node.Attributes["Name"].Value;
//Console.WriteLine("Running File: {0}", node.Attributes["Name"].Value);
using (StreamReader read = new StreamReader(Directory.GetCurrentDirectory() + "\\" + fileName))
 
with:
 
fileName = node.SelectSingleNode("FullPath").InnerText;
//Console.WriteLine("Running File: {0}", node.Attributes["Name"].Value);
using (StreamReader read = new StreamReader(Directory.GetCurrentDirectory() + "\\" + fileName,Encoding.Default))
 
Relative script paths cannot be stored in Sql Management Studio but you can edit the project file manually and afterwards Management Studio understands it. I definitly need relative paths for code management.
 
It's a great tool!
 
Thanks's
Andreas
Sign In·View Thread·Permalink
GeneralBug fix for case sensitive "GO" and do while checkmemberMember 430465113 May '08 - 3:01 
I've fixed two small bugs, one with the case sensitive string search for "GO", and the other for a do while loop that should be a while loop. Also reformatted output slightly to improve readability. I've posted the code for Program.cs below:
 
Thanks for the Addin - will prove very handy.
 
regards
 
Nigel Meakins
//*******************
 

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.XPath;
using System.Text.RegularExpressions;
using System.Threading;
 
namespace RunScriptsInProject
{
class Program
{
static void Main(string[] args)
{
 
try
{
//check we have correct argument(s)
if (args.Length != 1)
{
Console.WriteLine("Invalid arguments - No Project selected?");
return;
}
string projectPath = args[0];
if (File.Exists(projectPath) == false)
{
Console.WriteLine("Invalid arguments - file not found : {0}", projectPath);
return;
}
XmlTextReader reader = new XmlTextReader(projectPath);
XmlDocument doc = new XmlDocument();
doc.Load(reader);
reader.Close();
 
XmlNode connNode = doc.SelectSingleNode("//Items/ConnectionNode");
SqlConnection conn = GetConnection(connNode);
int count=0;
DateTime timeStart = DateTime.Now;
string fileName = "";
foreach (XmlNode node in doc.SelectNodes("//Items/FileNode"))
{
try
{
fileName = node.Attributes["Name"].Value;
Console.WriteLine( "===============================================" + "\n" +
"Running File: {0}" + "\n" +
"===============================================", node.Attributes["Name"].Value);
using (StreamReader read = new StreamReader(Directory.GetCurrentDirectory() + "\\"
+ fileName))
{
string fileText = read.ReadToEnd();
conn.Open();
ExecuteTextBatches(fileText,conn);
}
Console.WriteLine( "===============================================" + "\n" +
"Executed file: {0} successfully" + "\n" +
"===============================================" + "\n", fileName);
count++;
}
catch (SqlException ex)
{
Console.WriteLine("Sql Execption Occurred While Executing Script {0} - {1}",fileName, ex.Message);
}
finally
{
conn.Close();
}
}
TimeSpan ts = (DateTime.Now - timeStart);
Console.WriteLine("{0} out of {1} Files executed succesfully in {2} seconds", count,
doc.SelectNodes("//Items/FileNode").Count, ts.Milliseconds / 1000.0);

}
catch (Exception ex)
{
Console.WriteLine(ex.Message + "\n" + ex.Source + "\n" + ex.StackTrace);
}
}
 
private static void ExecuteTextBatches(string fileText, SqlConnection conn)
{
//Break Text into batches with 'GO' in between, execute each batch
SqlCommand comm = conn.CreateCommand();
comm.CommandType = CommandType.Text;
int goPos = fileText.ToUpper().IndexOf("GO");
while (goPos > 0)
{
comm.CommandText = fileText.Substring(0, goPos - 1);
Console.WriteLine(comm.CommandText);
comm.ExecuteNonQuery();
fileText = fileText.Replace(fileText.Substring(0, goPos + 2),"");
goPos = fileText.ToUpper().IndexOf("GO");
};
}
 
private static SqlConnection GetConnection(XmlNode connNode)
{
SqlConnection conn= null;
if (connNode != null)
{
SqlConnectionStringBuilder consb = new SqlConnectionStringBuilder();
consb.DataSource = connNode.SelectSingleNode("Server").InnerText;
consb.IntegratedSecurity = connNode.SelectSingleNode("Authentication").InnerText == "Windows Authentication" ? true : false;
if (!consb.IntegratedSecurity)
{
consb.UserID = connNode.SelectSingleNode("UserName").InnerText;
}
consb.InitialCatalog = connNode.SelectSingleNode("InitialDB").InnerText;
Console.WriteLine("Using Sql Connection {0}", consb.ConnectionString);
conn = new SqlConnection(consb.ConnectionString);
}
else
{
Console.WriteLine("No Connection found in project. Please add a connection first.");
}
return conn;
}
}
}
Sign In·View Thread·Permalink
Generalerror with Add-In - No connectiound found...memberka375130 Oct '07 - 9:11 
No Connection found in project. Please add a connection first.
0 out of 0 Files executed succesfully in 0 seconds
 
This is the error I get trying to run the add-in. Any ideas?
 
ka3751
Sign In·View Thread·Permalink
AnswerRe: error with Add-In - No connectiound found...memberMichael Erasmus27 Nov '07 - 6:24 
You need to add a connection to your project first. In the Solution Explorer go to the connections folder, right click and choose "New Connection".
Sign In·View Thread·Permalink
GeneralRe: error with Add-In - No connectiound found...memberWil Hutton23 May '08 - 6:40 
I get an issue with the password not being saved. When I try to run scripts I get "Login failed for user 'sa'" Is there a workaround for this? I tried editing ssmssqlproj to add the password, but I don't think SSMS looks there for the password anyway. I really like your tool, it looks like it's going to do what we want.
Sign In·View Thread·Permalink
Last Visit: 31 Dec '99 - 18:00     Last Update: 23 May '13 - 10:22Refresh1

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

About Article
An add-in for SQL Server 2005 Management Studio that will run all the scripts in the selected project.
Type Article
Licence CPOL
First Posted 21 Jun 2006
Views 41,667
Downloads 568
Bookmarked 27 times

, +
Top News

The Next Version of Android - Some of What's Coming

Get the Insider News free each morning.
Related Videos
Video #3 - Virtual Machines in Hyper - V
JavaScript Programming - 0202 How To Add Scripts To Your Page
SMO Tutorial 3 of n - Scripting
Script SQL Server 2005 diagrams to a file
Moving SQL Server 2005 Databases to SQL Server 2000
HowTo: Install the Northwind and Pubs Sample Databases in SQL Server 2008 Express
Document SQL Server 2000/2005 Database
C# Script: The Missing Puzzle Piece
HowTo: Install the Northwind and Pubs Sample Databases
Automatic Script SQL Server 2005 Objects and Commit under Subversion
Migrate MySQL to Microsoft SQL Server
SQL Server 2000 Collation Changer
SQL Server 2005 Distilled - Chapter 3: Enterprise Data Management
SQL Server to SQL Server Compact Edition Database Copy Utility
Creating Managed Objects in SQL Server 2005
Dynamically creating Excel File, ExcelSheets and Exporting Data from SQL Server using SSIS 2005
Creating SQL Server Login programmatically
Using SQL Server 2012 T-SQL New Features
Script Running Wizard Add-In for SQL Server Management Studio
Integrating Database Development in Visual Studio
SQL Server Database Backup Utility using VB.NET and SQL-DMO (New version)
SQL Server 2008 - Backup and Restore Databases using SMO
Permalink | Advertise | Privacy | Mobile
Web03 | 2.6.130523.1 | Last Updated 21 Nov 2008
Article Copyright 2006 by Michael Erasmus
Everything else Copyright © CodeProject, 1999-2013
Terms of Use
Layout: fixed | fluid