How to create a SQL script that executes other SQL scripts

Yesterday I was facing the following problem..
I have several sql scripts that I want to call from a single sql script.
I am using SQL Server 2005.
I have found two ways of doing it. The first one is by directly calling the scripts one by one, and the second is by calling all the scripts in a folder.

First of all, you need to set your server to accept 'xp_cmdshell' commands.
This is done by the following code

USE MainDB

GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

Now, let's assume that you have a folder at D:\Test, which has 3 files,
Test1.sql
Test2.sql
Test3.sql

The first way, which is the easiest way to execute this files by a sql script is by the following

USE MainDB

EXEC master..XP_CMDShell 'osql -E -i D:\Test\test1.sql'
EXEC master..XP_CMDShell 'osql -E -i D:\Test\test2.sql'
EXEC master..XP_CMDShell 'osql -E -i D:\Test\test3.sql'

If you want to run all the scripts in a folder, then you can use the following script

USE MainDB

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell 'dir /b "D:\Test\*.sql"'
GO

DECLARE cFiles CURSOR LOCAL FOR
   SELECT DISTINCT [SQLFileName]
   FROM ##SQLFiles
   WHERE [SQLFileName] IS NOT NULL AND
         [SQLFileName] != 'NULL'
   ORDER BY [SQLFileName]

DECLARE @vFileName            VARCHAR(200)
DECLARE @vSQLStmt             VARCHAR(4000)

OPEN cFiles

FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -E -i "D:\Test\' + @vFileName + '"'''
   EXECUTE (@vSQLStmt)

   FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO

This code will work if you don't need a username and password to connect to your database.. If your configuration is more complicated, you will have to add some more parameters to the osql. Read more here

Graduated!

Finally, I graduated..
I am not sure if I am happy for that.
Lately I have been planning to travel with Erasmus program to Australia! Since, I have graduated, I don't have this option anymore :(

But, anyway, it's good when things that are supposed to be done, are finally done!

Now, let's study something else..
Psychology and Law seem to be the main competitors..