December 2, 2014 at 8:46 am
Sometimes I need to run a script in another database. So sometimes USE is added as a first line to a script. This does not work very wel when you want to run a script which creates for example a View or a Stored procedure.
So I have build a stored procedure SP_ExcecuteSQL2.Two parameters can be used.One for the name of the database where the script should run.One for the script itself.The stored procedure is vunarable for code insertion.The code might have other issues. (Length, no proper error handling).I intend to extend the code with not a single database name but with databasenames which are pipe sepperated and can contain wildcard. But will leave this till a later moment.
This looks to me a rather 'cumbersome' solution.
Especially for stored procedures when they are long.
So suggestions and other solutions are very welcome.There are some examples to call the stored procedure at the end of the code.
The actual 'kernal' of the code is between the double bars. (Wrapper).
---------------------------------------------------------------------------------
-- Procedure template --
---------------------------------------------------------------------------------
use master -- System sp
go
-- =============================================
-- Author: Stef ten Bras /AKA Ben Brugman
-- Create date: 20141202
-- Description: SP_ExecuteSQL with parameters for the database and the script.
-- Context : Extention of the SP_ExecuteSQL statement.
-- =============================================
-- If the master database is used for general access,
-- The stored procedure name should begin with 'sp_'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_ExcecuteSQL2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) -- System sp
drop procedure [dbo].[SP_ExcecuteSQL2] -- System sp
GO
CREATE PROCEDURE SP_ExcecuteSQL2
@database varchar(300) = 'HELP'
,@Script varchar(max) = 'Print ''-- No script command was supplied. Database:''+DB_name(DB_ID())+'' '' '
AS
BEGIN
IF @DATABASE = 'HELP'
BEGIN
print '-- Generated :'+convert(varchar, getdate(), 120)
print '-- INStance :'+convert(varchar(32), serverproperty('servername'))
print '-- Database :'+DB_name(DB_ID())
print '-- user :'+user_name()
PRINT '--'
PRINT '--'
Print '-- HELP '+convert(varchar(30),getdate(),126)
PRINT '-- SP_ExcecuteSQL2'
PRINT '--'
PRINT '-- The functionality is intended to be the same as SP_ExcecuteSQL Help -- displays this text'
PRINT '-- A script should be supplied.'
PRINT '-- A databasename should be supplied.'
PRINT '--'
PRINT '-- With this procedure it should be more easy to run a script in a --specific-- database, from another database.'
PRINT '-- Some scripts can not be run we a USE command before them, like the creation of a Stored Procedure or a View.'
PRINT '-- In this way we can parameterise the name of the database.'
PRINT '--'
PRINT '-- Help -- displays this text'
PRINT '--'
PRINT '-- Database -- The database where this script should be executed.'
PRINT '-- -- Future: a pipe delimeted list. Where wildcards are allowed.'
PRINT '-- -- Local, is the current database.'
PRINT '-- Script -- The script to be executed.'
PRINT '--'
PRINT '-- Quotes :'
PRINT '-- The script can contain quotes, internally they are ''doubled''.'
PRINT '-- Build the script, to get the script in a string, qoutes should be double quoted.'
Print '-- For printing Hello '
PRINT '-- SET @SCRIPT = ''PRINT ''''HELLO'''' '' '
PRINT '-- SET @DATABASE = ''TestDB'' '
PRINT '-- SP_ExcecuteSQL2 @DATABASE, @SCRIPT'
PRINT '--CleanUp:'
PRINT '-- Stored procedure SP_ExcecuteSQL2 should be removed. See first command in the stored procedure script. '
END -- IF @action = 'HELP'
ELSE
BEGIN
---------------------------------------------------------------------------------
-- Wrapper technologie.
---------------------------------------------------------------------------------
-- 20141202
-- Execute a statement
-- In a database of choice.
--
DECLARE @WRAPPER Nvarchar(max) =
'
DBDBDB.dbo.sp_executesql N''SSSSSS''
'
IF @database = 'Local' SET @database = DB_name(DB_ID())
SET @Script = REPLACE(@Script,'''','''''')
exec sp_Split @database, @SGroup = 'Databaseset'
-- select * from tempdb.information_schema.tables
-- select * from tempdb.dbo.sp_tools_split
-- select * from tempdb.dbo.sp_tools_split where spid = @@spid and sgroup = 'Databaseset'
DECLARE @databasenameOne as varchar(300)
DECLARE @limit as int = 10000
DECLARE database_cursor CURSOR FOR
-- select item from tempdb.dbo.sp_tools_split where spid = @@spid and sgroup = 'Databaseset' order by itemnumber
SELECT D.name FROM master.dbo.sysdatabases D JOIN
tempdb.dbo.sp_tools_split S ON D.name like S.item where S.spid = @@spid and S.sgroup = 'Databaseset'
and DATABASEPROPERTYEX(name, 'Status')= 'online'
order by itemnumber
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @databasenameOne
WHILE @@FETCH_STATUS = 0 AND @LIMIT > 0
BEGIN
-- PRINT '-- De TABELNAAM = '+@tabel_naam
---------------------------------------------------------------------------------
-- Wrapper technologie.
---------------------------------------------------------------------------------
SET @WRAPPER=
'
DBDBDB.dbo.sp_executesql N''SSSSSS''
'
IF @database = 'Local' SET @database = DB_name(DB_ID())
-- print @wrapper
--PRINT '--------------------------------------------'
--PRINT @DATABASENAMEone
--PRINT '--------------------------------------------'
set @WRAPPER = REPLACE(@WRAPPER,'SSSSSS',@Script)
set @WRAPPER = REPLACE(@WRAPPER,'dbdbdb',@databasenameOne)
-- print @wrapper -- Leave for testing. Or options.
-- -- For long scripts (more than 8000, use a appropriate routine).
-- -- I have build a routine called SP_Print which can print long scripts.
EXECUTE (@Wrapper)
------------------------------------------------------------------------------
--
------------------------------------------------------------------------------
SET @LIMIT = @LIMIT-1
FETCH NEXT FROM database_cursor
INTO @databasenameOne
END
CLOSE database_cursor
DEALLOCATE database_cursor
END -- IF @action = 'ACTION1'
-- Insert statements for procedure here
/*
-- TEST SECTION OF THE STORED PROCEDURE
Examples:
exec SP_ExcecuteSQL2 'HELP'
exec SP_ExcecuteSQL2
exec SP_ExcecuteSQL2 'tempdb'
exec SP_ExcecuteSQL2 'tempdb|master|dct660%', 'PRINT convert(varchar(30),getdate(),126)+'' ---- ''+DB_name(DB_ID())'
*/
END -- Create SP_ExcecuteSQL2''
GO
-- make the stored procedure available from all databases.
sp_ms_marksystemobject 'SP_ExcecuteSQL2' -- System sp
GO
--
-- To drop the stored procedure :
--
-- USE master
-- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_ExcecuteSQL2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) -- System sp
-- drop procedure [dbo].[SP_ExcecuteSQL2] -- System sp
GO
December 2, 2014 at 10:21 am
That seems over complicated when you're just adding the database to sp_executesql which can be done directly instead of calling an additional procedure and you remove the functionality of using parametrized queries.
Without the advantage of giving additional permissions by adding EXECUTE AS, I can't see the point of it.
December 2, 2014 at 2:57 pm
Luis Cazares (12/2/2014)
.... over complicated.....
Yes I think it is over complicated, hense the question.
But I did not find a less cumbersome solution.
The specific usage was to add a stored procedure to every database in an instance where the database name contains for example '%ben%'.
I did use several constructions, but could not come up with a 'clearer' one than the above.
Target databases are not known at the time of design.
So if there is a better/simpler/easier solution please show me how to implement that.
Thanks for your time and attention,
Ben
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply