usp_exec_tsql_strfile

,

Executes a file containing T-SQL code without using OSQL. The file can be up to 80,000 characters. Each line in the file should be no more than 256 characters. (Customize the code as needed.) The DOS Type command is used to retrieve the code. Embedded "go"s are removed so don't expect termination behavior. The only parameter is a fully qualified file path passed as a 256 varchar string. Uses dynamic SQL to run the file. Runs in the context of the database your connected to when you call the proc unless the file code redirects it. Holding tables are real tables created in Tempdb and then dropped at the end of execution. This could cause problems in code that repeatedly calls the proc. Use at your on risk.

if exists (select * 
             from dbo.sysobjects 
            where id = object_id(N'[dbo].[usp_exec_tsql_strfile]') 
              and OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[usp_exec_tsql_strfile]
GO

CREATE procedure usp_exec_tsql_strfile
       @filepath varchar(256) = null

as

/*******************************************************************/
--Name        : usp_exec_tsql_strfile
--Server      : Generic
--Description : Executes a large T-SQL string file locally.
--            : The file must be less than 80,000 charaters. Each
--            : line should be less than 256 characters. Embedded 
--            : 'go's cause problems and are removed before
--            : execution begins.
--
--Variables   : @filepath is the file path of the file containing the 
--              T-SQL code to be executed.
--
--Date        : 07/13/2004
--Author      : Clint Herring
--
--History     : 
--
/*******************************************************************/

-- create a temp holding table.
If (Select object_id('tempdb.dbo.strfile')) > 0 
   Exec ('drop table tempdb.dbo.strfile')
Exec('Create table tempdb.dbo.strfile(id int identity(1,1) not null, script varchar(256) null)')

-- declare some variables (10-8k variables = 80,000 total caharaters)
declare @cnt int
declare @loop int
declare @str0 varchar(8000)
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @str3 varchar(8000)
declare @str4 varchar(8000)
declare @str5 varchar(8000)
declare @str6 varchar(8000)
declare @str7 varchar(8000)
declare @str8 varchar(8000)
declare @str9 varchar(8000)

-- Initializes variables
set @loop = 1
set @str0 = ''
set @str1 = ''
set @str2 = ''
set @str3 = ''
set @str4 = ''
set @str5 = ''
set @str6 = ''
set @str7 = ''
set @str8 = ''
set @str9 = ''

-- Get the file
Set @filepath = 'type ' + @filepath
Insert into tempdb.dbo.strfile
Exec master.dbo.xp_cmdshell @filepath 

--Clear null lines and embedded 'go's
Delete from tempdb.dbo.strfile where script is null or script = 'go'

-- Get the max id corresponding to the last executable line
Select @cnt = max(id) from tempdb.dbo.strfile

-- Build a multi-variable string cmd set (7744 is 8000-256)
While @loop <= @cnt
   Begin
      If datalength(@str8) >= 7744 and datalength(@str9) < 7744
         Select @str9 = @str9 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str7) >= 7744 and datalength(@str8) < 7744
         Select @str8 = @str8 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str6) >= 7744 and datalength(@str7) < 7744
         Select @str7 = @str7 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str5) >= 7744 and datalength(@str6) < 7744
         Select @str6 = @str6 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str4) >= 7744 and datalength(@str5) < 7744
         Select @str5 = @str5 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str3) >= 7744 and datalength(@str4) < 7744
         Select @str3 = @str3 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str2) >= 7744 and datalength(@str3) < 7744
         Select @str3 = @str3 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str1) >= 7744 and datalength(@str2) < 7744
         Select @str2 = @str2 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str0) >= 7744 and datalength(@str1) < 7744
         Select @str1 = @str1 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      If datalength(@str0) < 7744
         Select @str0 = @str0 + script + char(13) + char(10) from tempdb.dbo.strfile where id = @loop
      Select @loop = min(id) from tempdb.dbo.strfile where id > @loop 
   End
--Print (@str0+@str1+@str2+@str3+@str4+@str5+@str6+@str7+@str8+@str9)  -- for test
--Select * from tempdb.dbo.strfile -- for test

-- Exec the multi-variable string cmd
Exec (@str0+@str1+@str2+@str3+@str4+@str5+@str6+@str7+@str8+@str9)
Drop table tempdb.dbo.strfile
go

Rate

Share

Share

Rate