How to execute nested scripts in Sql Server 2005+

Question

Do any sql ninja's know how to execute a script (.sql) from within another script? Specifically for the Sql Server 2005+ platform. E.g. psudeo code follows:

ScriptA contents

RESTORE DATABASE [MyDbName]
    FROM  
     DISK = N'path' WITH  FILE = 1
.......
GO
Exec c:\scripts\ScriptB.sql

ScriptB contents

USE [MyDbName]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTableName]') AND type in (N'U'))
CREATE TABLE [dbo].[MyTableName]
........
GO

Answer 1

Hi,

The following article provides examples of how you can achieve this.

http://www.sqlservercentral.com/articles/Administering/scriptrunner/292/

Here is a free tool that can run multiple scripts but not from within an existing script.

http://www.xsqlsoftware.com/Product/ProductDetails.aspx?ProdID=ScriptExecutor

Here is an example of executing a script from within another script by calling the isql utility using the xp_cmdshell stored procedure.

http://www.sqlservercentral.com/articles/Administering/scriptscheduling/450/

Should be what you need to get going but drop me a line if you need further assistance.

Cheers, John

Answer 2

Core MS SQL doesn't do this, but some tools have some macro capabilities (i.e. done client site).

SQLCMD supports ":r" to import another file.

Answer 3

As far as i can see you dont have to combine the two scripts. You can just execute the first and then the other.

You can do this by making a vbscript that loads the .sql files and then uses an ADODB.Connection to execute the two scripts.

This vbscript should do it:

Set con = CreateObject("ADODB.Connection")
con.ConnectionString = "*** Database connection string here ***"
con.Open 

Set fso = CreateObject("Scripting.FileSystemObject")

Set sqlFile = fso.OpenTextFile("scriptA.sql", 1)
con.Execute sqlFile.ReadAll
sqlFile.Close

Set sqlFile = fso.OpenTextFile("scriptB.sql", 1)
con.Execute sqlFile.ReadAll
sqlFile.Close

Set fso = Nothing 
Set con = Nothing

Answer 4

Hi. You can turn SQLCMD mode on from SSMS or run your script in SQLCMD.exe. SQLCMD supports script variables and script references.

---script1-----
create table ....
:r path/to/another/script (which can reference others)
---------------

hope this helps