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
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
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
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
