Tuesday 20 August 2019

SQL Server : Multiple scripts execution script

Following script will help to execute multiple scripts and save output too. We can save our time of manual execution if we need to do hundreads of scripts

/*
USAGE:
Provide source folder name where SQL scripts have been placed in
1--> SET @Source='E:\Zap\'
Provide server and database name
SET @srvname = 'Lab1'
SET @dbname = 'Test1'
*/
---------------------------------------------------------------------------------------
set nocount on
DECLARE @Source VARCHAR(500)
DECLARE @SQLPath nvarchar(500)
SET @Source='E:\Zaps\'
SET @SQLPath='dir /b "'+@Source+'*.sql"'
CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(500))
INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell @SQLPath

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

DECLARE @vFileName            VARCHAR(500)
DECLARE @vFileName_op            VARCHAR(500)
DECLARE @vSQLStmt             VARCHAR(1100)
DECLARE @srvname VARCHAR(50)
DECLARE @dbname VARCHAR(50)
DECLARE @err_count int
DECLARE @msg VARCHAR(500)

DECLARE @dest VARCHAR(500)
DECLARE @cmdpath nvarchar(500)

SET @dest=@Source+'Output\'
SET @cmdpath = 'Mkdir "'+@Source+'output"'
SET @srvname = 'Lab1'
SET @dbname = 'Test1'



PRINT 'Running SQL scripts placed in folder: '+@Source
PRINT 'Running SQL scripts on server: '+@srvname
PRINT 'Running SQL scripts on Database: '+@dbname



-----------Check/Create output folder in root folder----------
CREATE TABLE ##xp_fileexist_output (
[FILE_EXISTS] int not null,
[FILE_IS_DIRECTORY] int not null,
[PARENT_DIRECTORY_EXISTS] int not null)

insert into ##xp_fileexist_output
exec master.dbo.xp_fileexist @dest

if exists ( select * from ##xp_fileexist_output where FILE_IS_DIRECTORY = 1 )
begin
print 'Output folder already exist at: '+@dest
end
else
begin
exec master.dbo.xp_cmdshell @cmdpath
print 'Output folder created: '+@dest
end
--------------------------------------------------------------


PRINT 'Starting the execution of SQL scripts!!!'

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @vFileName_op=REPLACE('OP_'+@vFileName,'.sql','.txt')
    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S' +@srvname+ ' -d ' +@dbname+ ' -E -n -i "'+@Source+'' + @vFileName + '" -o "'+@dest+''+@vFileName_op+'"'''

SET @msg='Running '+@vFileName+' now!!!'
Print @msg

      PRINT (@vSQLStmt)
    EXECUTE (@vSQLStmt)

-----------Search output file for errors----------
Select @err_count=COUNT(1) from
master..udfReadfileAsTable(@dest,@vFileName_op)
where line like '%msg%'
and line like '%Level%'
and line like '%State%'
------------------------------------------------------
If @err_count>0
BEGIN
 select @msg= '!!! ERROR OCURRED !!! in script name: '+ @vFileName+'
 Output saved in : '+@dest+@vFileName_op
 print @msg
GOTO Abort_label
END
ELSE
BEGIN
select @msg= 'Successfully executed script name: '+ @vFileName +'
Output saved in : '+@dest+@vFileName_op
 print @msg
END
   FETCH NEXT FROM cFiles INTO @vFileName
END
GOTO Skip_label
Abort_label:
BEGIN
PRINT 'Aborted the execution of scripts. Check/Remove the errors and remove the scripts already executed from SQL folder'
select * from forcedfailure
END
Skip_label:

CLOSE cFiles
DEALLOCATE cFiles
GO
------------------Cleanup------------------------
PRINT 'Dropping temp tables!!!'
DROP TABLE ##SQLFiles
DROP TABLE ##xp_fileexist_output
GO

No comments:

Post a Comment