Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a set of scripts that need to be run in a certain order. I would like to create a "master file" that lists all of the other files and their correct order. Basically like an include file from C++ or ASP/VBScript.

share|improve this question
add comment

migrated from stackoverflow.com Feb 24 '12 at 19:42

This question came from our site for professional and enthusiast programmers.

5 Answers

up vote 10 down vote accepted

If you are using SQLCMD, you can use :r FileName to include a separate .sql file.

:r FileName

Parses additional T-SQL statements and SQLCMD commands from the file specified by FileName into the statement cache. FileName is read relative to the startup directory for Visual Studio. The file is read and executed after a batch terminator is encountered. You can issue multiple :r commands. The file can include any SQLCMD command, including the batch terminator that is defined in Tools, Options. You can use :r in a pre-deployment or post-deployment script to include other scripts.

share|improve this answer
    
Are you using this feature yourself? –  bernd_k Feb 25 '12 at 10:43
add comment

I agree, a T-SQL preprocessor is badly needed. I developed my own one in C#, which took me like an hour. Besides control over order of execution of SQL scripts, it also allows me to have macros that look like scalar UDFs and are convenient to use, but perform as fast as inline UDFs.

share|improve this answer
    
+1 - I've done that sort of thing with templating tools on a number of occasions. Although sqlcmd has some facilities for variable substitution I would give someone who implemented a decent macro pre-processor plugin for SSMS a big kiss on the bottom. –  ConcernedOfTunbridgeWells Feb 28 '12 at 16:52
add comment

Along with the SQLCMD mode in Management Studio, you can also use a batch file to use as a master file to arrange and call all your sql files by using the SQLCMD utility.

The SQLCMD mode is a simulation of the utility usage inside the Management Studio, so there's not big difference between the modes. Except that sometime I prefer to run batches (no more opening of M Studio...loading correct db...etc). I prefer to configure everything in the batch, including output files.

share|improve this answer
add comment

In the sense of the ancient C preprocessors definitely no

  • T-SQL by itself has no such concept
  • sqlcmd and sqlcmdmode of SSMS do not handle relative file paths
  • sqlcmd and sqlcmdmode of SSMS do not support conditional including

If you have a set of files with given absolute paths, which you want to include in a unconditional order, you can use the command line tool sqlcmd or use SSMS in sqlcmdmode as mentioned by @mfredrickson and @Marian.


But if you want relative paths or conditional includes you need to use some wrapper calling sqlcmd (or one of its deprecated predecessors isql or osql).

Currently at my job in this situation, I'm reviewing and extending old vbs and hta code. I'm not completely happy, but it is a pragmatic decision. It is working and the end user is at home with the hta GUI and no additional software installation is required.

For a fresh design I would start thinking about using WPF and PowerShell to wrap calls of sqlcmd, but with our current customers I can't presume the presence of PowerShell V2.

In even former times we wrote simple cmd batches for this purpose, ome of them still in use.

I guess there are current tools, especially those targeting Workflows, which might be suitable here. I'm not familiar with those.

share|improve this answer
add comment

I like bernd_k's answer. Depending on how you named your scripts, like including a number, SQL Server PowerShell (SQLPS) could be used if you were running SQL Server 2008 or higher. Then even if you were using SQL 2005 you could utilize PowerShell and the SMO for 2005.

Side note: I believe SQLCMD will eventually be put on the depreciated list and is being replaced with SQLPS, PowerShell.

There are a few other options as well.

  1. Setup each script as a step in a SQL Agent job. You can set the order they are executed.
  2. Create an SSIS package to call each file. This can offer a little bit more control and offer a little bit more logging options. I like this method if the outcome of one script needs to be verified prior to the next one executing.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.