| Author |
 Topic  |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
 Posted - 03/14/2008 : 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
Sweden
29138 Posts |
Posted - 03/14/2008 : 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" |
Edited by - SwePeso on 03/14/2008 18:27:02 |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 03/14/2008 : 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 |
Edited by - Michael Valentine Jones on 03/14/2008 23:11:12 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 03/25/2008 : 11:41:39
|
insert MyScriptTable (ScriptName, MyScript)
select 'cannibal', 'delete from MyScriptTable'
elsasoft.org |
|
|
|
sudhir_shukla
Starting Member
Canada
1 Posts |
Posted - 01/20/2011 : 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)
USA
6997 Posts |
Posted - 01/20/2011 : 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
Brazil
1 Posts |
Posted - 03/30/2012 : 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
USA
1 Posts |
Posted - 04/18/2012 : 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 Posts |
Posted - 04/21/2012 : 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
|
|
|
| |
Topic |
|
|
|
Execute Script from a File
