Creating a Script from a Stored Procedure

,

A simple task, I thought, but it took me to some interesting places. The method is broadly this:

1) Create an instance of SQL-DMO SQL Server, and use the script method to save the create table text in a file.

2) Get the text from the file into a sp variable.

3) Delete the text file.

Here are the details of the method, and a summary which puts it all together:

1) Create an instance of SQL-DMO SQL Server, and use the script method to save the create table text in a file. 

Here's the usage:

exec run_script 'my_server', 'my_database', 'my_table', 74077, 'my_path_name'

And here's the sp...

CREATE proc run_script

@server varchar(100),

@database_name varchar(100),

@table_name varchar(100),

@script_id int,

@path_name varchar(200) as

--runs a sql server script and outputs it to a file.

declare @i int

declare @object int

declare @return varchar(200)

declare @q varchar(200)

declare @is_error bit

set @is_error = 0

--create sql server object

EXEC @i = sp_OACreate 'SQLDMO.SQLServer', @object OUT

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

--connect to sql server using windows nt and verify the connection EXEC

@i = sp_OASetProperty @object, 'LoginSecure', 1

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

EXEC @i = sp_OAMethod @object, 'Connect', NULL, @server

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

EXEC @i = sp_OAMethod @object, 'VerifyConnection', @return OUT

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

--run the script

SET @q = 'Databases("' + @database_name + '").Tables("' + @table_name

+

'").Script(' + cast(@script_id as varchar(10)) + ', ' + @path_name +

')'

IF NOT @i = 0 begin EXEC sp_OAGetErrorInfo @object set @is_error = 1

end

EXEC @i = sp_OAMethod @object, @q, @return OUT

IF NOT @i = 0 begin EXEC sp_OAGetErrorInfo @object set @is_error = 1

end

--destroy sql server object

EXEC @i = sp_OADestroy @object

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

return @is_error

GO

2) Get the text from the file into a sp variable. My first try was to use the FileSystemObject...

CREATE proc get_from_file @file_output varchar(8000) output,

@path_name

varchar(200) as

--outputs all the text of a file concatenated into a single string.

--Note - 255 character limitation.

DECLARE @file_output varchar(8000)

DECLARE @fso int

DECLARE @ts int

DECLARE @i int

EXEC @i = sp_OACreate 'Scripting.FileSystemObject', @fso OUT

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @fso

EXEC @i = sp_OAMethod @fso, 'OpenTextFile', @ts out, @path_name

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @fso

EXEC @i = sp_OAMethod @ts, 'ReadAll', @file_output out

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @ts

EXEC @i = sp_OADestroy @ts

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @ts

EXEC @i = sp_OADestroy @fso

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @fso

GO

This, however, has a 255 character limitation - so it was back to the drawing board. I don't much like it, but I came up with this...

declare @file_output varchar(8000)

exec get_from_file @file_output output, 'my_path_name'

select @file_output

And here's the sp (with a simple supporting sp below it)...

CREATE proc get_from_file @file_output varchar(8000) output,

@path_name

varchar(200) as

--outputs all the text of a file concatenated into a single string.

set nocount on

--get_unique_name for temporary table

declare @unique_table_name varchar(100)

exec get_unique_name @unique_table_name output

set @unique_table_name = '##' + @unique_table_name

--create concatenated string and puts it into the table

exec('

create table #t1 (c1 varchar(8000))

bulk insert #t1 from ''' + @path_name + '''

declare @s varchar(8000)

set @s = ''''

select @s = @s + isnull(c1, '''') + char(13) from #t1

select c1 = @s into ' + @unique_table_name

)

--output the single value in the table to our output variable

declare @q nvarchar(100)

set @q = 'select @p1 = c1 from ' + @unique_table_name

exec sp_executesql @q, N'@P1 varchar(8000) output', @file_output

output

--drop our temporary table

exec ('drop table ' + @unique_table_name)

set nocount off

GO

Supporting sp...

CREATE proc get_unique_name @output varchar(50) output as

--outputs a unique name based on the current user and the precise time the sp is run.

--can be used for table names / file names etc.

select @output =

replace(system_user, '\', '_') + '_' +

cast(datepart(yyyy, getdate()) as varchar(4)) + '_' +

cast(datepart(mm, getdate()) as varchar(2)) + '_' +

cast(datepart(dd, getdate()) as varchar(2)) + '_' +

cast(datepart(hh, getdate()) as varchar(2)) + '_' +

cast(datepart(mi, getdate()) as varchar(2)) + '_' +

cast(datepart(ss, getdate()) as varchar(2)) + '_' +

cast(datepart(ms, getdate()) as varchar(3))

GO

3) Delete the text file. This uses a familiar method. This time there are no limitations.

Here's the usage...

exec delete_file 'my_path_name'

And here's the sp...

CREATE proc delete_file @path_name varchar(200) as

--deletes a file

DECLARE @object int

DECLARE @i int

EXEC @i = sp_OACreate 'Scripting.FileSystemObject', @object OUT

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

EXEC @i = sp_OAMethod @object, 'DeleteFile', null, @FileSpec =

@path_name

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

EXEC @i = sp_OADestroy @object

IF NOT @i = 0 EXEC sp_OAGetErrorInfo @object

GO

Putting it all together - here's the usage...

declare @object_text varchar(8000)

exec get_create_table_script @object_text output, 'my_server',

'my_database', 'my_table'

select @object_text

And here's the sp...

CREATE proc get_create_table_script

@create_table_script varchar(8000) output,

@server varchar(100),

@database_name varchar(100),

@table_name varchar(100) as

--outputs a create table script for a sql table. To do this, it runs a script to put it into a file, then gets it from the file and deletes

the file

declare @return int

--get path name of temporary sql file

declare @path_name varchar(100)

exec get_unique_name @path_name output

set @path_name = '\\' + @server + '\c$\' + @path_name + '.sql'

--create the 'create table' script and put it into sql file

exec @return = run_script @server, @database_name, @table_name, 74077,

@path_name

--return if above step errored.

if @return = 1 return

--get script results from sql file into output variable

exec get_from_file @create_table_script output, @path_name

--delete temporary sql file

exec delete_file @path_name

GO

And there's your final stored procedure which does what we set out to do.

Rate

5 (1)

Share

Share

Rate

5 (1)