Execute Script from a File - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Execute Script from a File

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-14 : 18:02:34

This script shows how to load a SQL script file into a nvarchar(max) local variable and then execute it.


-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
The script must not contain a GO Statement
Runs in SQL Server 2005
*/

set nocount on
if object_id('tempdb..#FileData','U') is not null
	begin drop table #FileData end

create table #FileData ( FileData nvarchar(max) )

declare @FileName  varchar(255)
declare @SQLLoad   nvarchar(max)
declare @Script    nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:\MyDir\MyScriptFile.sql'

-- Create command to load the file into temp table #FileData
set @SQLLoad =
N'insert into #FileData
select fd.*
from openrowset(bulk ''' + @FileName + ''',SINGLE_CLOB ) as FD'

-- Load file data into temp table #FileData
exec sp_executesql @SQLLoad

-- Load SQL into a local variable from temp table #FileData
select top 1 @Script = FileData from #FileData

-- Execute the script
exec sp_executesql @Script

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 18:13:31

I haven't tried this yet (no server available right now)
But it doesn't need a temp table. Maybe MVJ can test it?

-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
Runs in SQL Server 2005
*/

declare @FileName  varchar(255)
declare @SQLLoad   nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:\MyDir\MyScriptFile.sql'

-- Create command to load and execute the file
set @SQLLoad =
N'declare @cmd varchar(max);
select @cmd = fd.col1
from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
exec (@cmd)'

-- Load file data and execute
exec (@SQLLoad)

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-14 : 21:28:16

That also looks like a good way to execute a script file

I was using a temp table because I was concentrating on my objective of being able to load SQL scripts into a permanent table. I want to have scripts loaded into permanent tables for a few different purposes.

I would like to be able to setup jobs to run scripts that are too long to fit in the job SQL command limit without having to use osql. The job would just need a short script to load the script from a table and execute it, or it could use a general purpose stored procedure that loads scripts from a table and executes them in a selected database. Example:

declare @MyScript nvarchar(max)
select @MyScript = MyScript from MyScriptTable where ScriptName = ‘MySQLScript’
execute ( @MyScript )

Long term, I would like to be able to store scripts for automated deployment of database changes in tables. It would be nice to encrypt and sign the scripts to save them in a table for audit purposes, and be able to eliminate any doubt about what was done to the database. It would also be easier to run a large deployment inside a transaction so that it either all goes or none of it goes. Running it inside a job would also allow capturing the command output to a file and storing that in a table as an audit trail and debugging tool. There are many cases where auditors demand a verifiable trail of database changes, and I think this method has a lot of potential.

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-15 : 11:26:28

so why don't you use ddl and dml triggers for this?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-25 : 11:41:39

insert MyScriptTable (ScriptName, MyScript)
select 'cannibal', 'delete from MyScriptTable'

elsasoft.org

sudhir_shukla
Starting Member

1 Post

Posted - 2011-01-20 : 15:29:21

quote:

Originally posted by Peso

I haven't tried this yet (no server available right now)
But it doesn't need a temp table. Maybe MVJ can test it?

-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
Runs in SQL Server 2005
*/

declare @FileName  varchar(255)
declare @SQLLoad   nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:\MyDir\MyScriptFile.sql'

-- Create command to load and execute the file
set @SQLLoad =
N'declare @cmd varchar(max);
select @cmd = fd.col1
from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
exec (@cmd)'

-- Load file data and execute
exec (@SQLLoad)

E 12°55'05.25"
N 56°04'39.16"

Hi,

I tried the above on my database which is on SQL Server 2000 and i'm getting a syntax error. Below is the code I tried:

-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
*/

declare @FileName varchar(255)
declare @SQLLoad nvarchar(4000)

-- Set the file to load SQL Script from
set @FileName = 'X:\temp\Email table.sql'

-- Create command to load and execute the file
set @SQLLoad =
N'declare @cmd varchar(4000);
select @cmd = fd.col1
from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
exec (@cmd)'

print @SQLLoad

-- Load file data and execute
exec (@SQLLoad)

************
The error I get is:

declare @cmd varchar(4000);
select @cmd = fd.col1
from openrowset(bulk 'X:\temp\Email table.sql', SINGLE_CLOB) as FD(col1);
exec (@cmd)
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'bulk'.

you help is this is greatly appreciated.

Thanks,
Sudhir

Sudhir Shukla

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-01-20 : 17:30:08

quote:
Originally posted by sudhir_shukla

quote:
Originally posted by Peso

I haven't tried this yet (no server available right now)
But it doesn't need a temp table. Maybe MVJ can test it?
-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
Runs in SQL Server 2005
*/

declare @FileName varchar(255)
declare @SQLLoad nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:\MyDir\MyScriptFile.sql'

-- Create command to load and execute the file
set @SQLLoad =
N'declare @cmd varchar(max);
select @cmd = fd.col1
from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
exec (@cmd)'

-- Load file data and execute
exec (@SQLLoad)

E 12°55'05.25"
N 56°04'39.16"

Hi,

I tried the above on my database which is on SQL Server 2000 and i'm getting a syntax error. Below is the code I tried:

-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
*/

declare @FileName varchar(255)
declare @SQLLoad nvarchar(4000)

-- Set the file to load SQL Script from
set @FileName = 'X:\temp\Email table.sql'

-- Create command to load and execute the file
set @SQLLoad =
N'declare @cmd varchar(4000);
select @cmd = fd.col1
from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
exec (@cmd)'

print @SQLLoad

-- Load file data and execute
exec (@SQLLoad)

************
The error I get is:

declare @cmd varchar(4000);
select @cmd = fd.col1
from openrowset(bulk 'X:\temp\Email table.sql', SINGLE_CLOB) as FD(col1);
exec (@cmd)
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'bulk'.

you help is this is greatly appreciated.

Thanks,
Sudhir

Sudhir Shukla

Maybe you missed the comment that says:
Runs in SQL Server 2005

CODO ERGO SUM

FelipeF
Starting Member

1 Post

Posted - 2012-03-30 : 15:52:14

quote:

Originally posted by SwePeso

I haven't tried this yet (no server available right now)
But it doesn't need a temp table. Maybe MVJ can test it?

-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
Runs in SQL Server 2005
*/

declare @FileName  varchar(255)
declare @SQLLoad   nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:\MyDir\MyScriptFile.sql'

-- Create command to load and execute the file
set @SQLLoad =
N'declare @cmd varchar(max);
select @cmd = fd.col1
from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
exec (@cmd)'

-- Load file data and execute
exec (@SQLLoad)

E 12°55'05.25"
N 56°04'39.16"

I implemented the above code, but I received an error when I was running it on SQL 2008.

example:
"Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'ALTER'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'ALTER'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near 'GO'. "

So I adapted the code including a treatment of the script before the execution:
set @ cmd = replace (@ cmd,'' GO'' + char (13),'';'')

Felipe Ferreira
W5 Solutions

shrikant44
Starting Member

1 Post

Posted - 2012-04-18 : 03:08:54

This script shows how to load a SQL script file into a nvarchar(max) local variable and then execute it.

unspammed
shrikant kumar

setvij
Starting Member

1 Post

Posted - 2012-04-21 : 06:32:39

I have a sql file that has list of tables (Create Table statements) and it works fine. But when I try to execute another sql file that has list of Stored Procedures (Create Procedure statements), I get lot of error. I am attaching the information below: Please help!
I have a table to capture the records for testing purpose.

Msg 156, Level 15, State 1, Procedure ActiveInactiveBusinessUnit, Line 31
Incorrect syntax near the keyword 'procedure'.
Msg 137, Level 15, State 2, Procedure ActiveInactiveBusinessUnit, Line 45
Must declare the scalar variable "@NxtOfferStatus".
Msg 137, Level 15, State 2, Procedure ActiveInactiveBusinessUnit, Line 47
Must declare the scalar variable "@NxtCandStatus".
Msg 137, Level 15, State 1, Procedure ActiveInactiveBusinessUnit, Line 50
Must declare the scalar variable "@Result".
Msg 134, Level 15, State 1, Procedure ActiveInactiveBusinessUnit, Line 56
The variable name '@ErrMsg' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 137, Level 15, State 1, Procedure ActiveInactiveBusinessUnit, Line 59
Must declare the scalar variable "@Result".

------------------------------------------------------------------
Sql script:

declare @FileName varchar(255)
declare @SQLLoad nvarchar(max)
declare @Script nvarchar(max)
--declare @SchemaID int

-- Set the file to load SQL Script from
set @FileName = 'D:\DynamicSchema\DynamicSchemaSteps\ShyamSchemaSP.sql'

-- Create command to load the file into temp table #FileData
set @SQLLoad =
N'insert into SchemaLogTest
select fd.*
from openrowset(bulk ''' + @FileName + ''',SINGLE_NCLOB ) as FD'

--set @SchemaID = scope_identity()

-- Load file data into temp table #FileData
exec sp_executesql @SQLLoad

-- Load SQL into a local variable from temp table #FileData
select top 1 @Script = FileData from SchemaLogTest --where SchemaID = @SchemaID

--select @Script

-- Execute the script
exec sp_executesql @Script

--------------------------------------------------------------

Stored Procedure script:

/****** Object: StoredProcedure [ABCSchema].[ActiveInactiveBusinessUnit] Script Date: 04/21/2012 15:18:56 ******/
-- ==========================================================
-- Author: Vijaya Hegde
-- Create date: Jan 7 2012
-- Description: Update Active/Inactive status for selected BU
-- ==========================================================
CREATE PROCEDURE [ABCSchema].[ActiveInactiveBusinessUnit]
@BUID int,
@IsStatusActive int output
AS
BEGIN
SET NOCOUNT ON;

begin try
Update abcschema.MastBusinessUnit set Status=@IsStatusActive where BUID=@BUID
return @IsStatusActive
end try
begin catch
set @IsStatusActive = -1
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
end catch
END
/****** Object: StoredProcedure [ABCSchema].[AcceptOffer] Script Date: 04/21/2012 15:18:56 ******/
-- =============================================
-- Author: Viji Setlur
-- Create date: Nov 7 2011
-- Description: Accept Offer for Req
-- =============================================
CREATE procedure[ABCSchema].[AcceptOffer]
@OfferID int,
@NxtOfferStatus int,
@NxtCandStatus int,
@CandidateID int,
@ReqCategoryID int,
@Comments varchar(5000),
@Result int output
AS
BEGIN
SET NOCOUNT ON;
declare @ReqID int
begin try
begin transaction
Update abcschema.Offer set [Status] = @NxtOfferStatus , OfferComments = @Comments
where OfferID = @OfferID
Update C set C.[Status] = @NxtCandStatus
FROM abcschema.ReqCandTracking C
where C.ReqCategoryID = @ReqCategoryID and C.CandidateID = @CandidateID and C.OfferID=@OfferID
select @Result = 1
commit transaction
end try
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
set @Result = 0
END CATCH
END

quote:
Originally posted by FelipeF

quote:
Originally posted by SwePeso

I haven't tried this yet (no server available right now)
But it doesn't need a temp table. Maybe MVJ can test it?
-- Execute_Script_From_File.sql
/*
Load a script from a text file and execute it.
Runs in SQL Server 2005
*/

declare @FileName varchar(255)
declare @SQLLoad nvarchar(max)

-- Set the file to load SQL Script from
set @FileName = 'C:\MyDir\MyScriptFile.sql'

-- Create command to load and execute the file
set @SQLLoad =
N'declare @cmd varchar(max);
select @cmd = fd.col1
from openrowset(bulk ' + QUOTENAME(@FileName, '''') + ', SINGLE_CLOB) as FD(col1);
exec (@cmd)'

-- Load file data and execute
exec (@SQLLoad)

E 12°55'05.25"
N 56°04'39.16"

I implemented the above code, but I received an error when I was running it on SQL 2008.

example:
"Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'ALTER'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'ALTER'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near 'GO'. "

So I adapted the code including a treatment of the script before the execution:
set @ cmd = replace (@ cmd,'' GO'' + char (13),'';'')

Felipe Ferreira
W5 Solutions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources