IT:AD:SQL Server:HowTo:SQL:DDL:Deployment/Log

To track what was going on, we created a log file system that worked as follows.

First, we created a Bat file:

set startdir=.
set server=localhost
del ScriptRunnerUtility.log
set database=Gtm2
set logfile=ScriptRunnerUtility.log
call .\ScriptRunnerUtility.bat

that in turn called another bat:

pause
echo starting creating the Migration Scripts
rem for each *.sql in the "startdir" directory.
for %%f in ("%startdir%\*.sql") do (
echo  "" >> "%logfile%"
echo  "" >> "%logfile%"
echo ************************************************************** >> "%logfile%"
echo *** Processing %%f *** >> "%logfile%"
echo ************************************************************** >> "%logfile%"
rem run the scripts results and output messages to the logfile
sqlcmd.exe -I -E -S"%server%" -d"%database%" -i"%%f" >> "%startdir%\%logfile%" 
)
echo finished running Scripts
pause

which in turn found all *.sql files in the same folder and ran them.

Each Sql file in turn printed out things to the Console Stream, which the above Bat file

wrote to an output log file…which could be sent back, giving us a good record of how the script installation process went.

---------------------------------------------------------------------------------------------
-- CHECK EXISTING STORED PROC BEFORE OVERWRITING IT
SELECT 
    ''''+ 'someStoredProc'+ ''' after update' as comments, 
    so.object_id, 
    so.name, so.create_date, so.modify_date, 
    sc.colid , sc.text 
  FROM 
    sys.objects so 
  INNER JOIN 
    syscomments sc 
  ON 
    so.object_id = sc.id 
  WHERE 
    name = 'someStoredProc'
GO
---------------------------------------------------------------------------------------------
SELECT 'Dropping someStoredProc', @@SERVERNAME, DB_NAME()
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[someStoredProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[someStoredProc]
GO
---------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
---------------------------------------------------------------------------------------------
SELECT 'Creating someStoredProc', @@SERVERNAME, DB_NAME()
GO
-- PLACE someStoredProc 'CREATE' STATEMENT HERE...
GO
---------------------------------------------------------------------------------------------
-- CHECK THAT STORED PROC WAS CREATED
SELECT 
    ''''+ 'someStoredProc'+ ''' after update' as comments, 
    so.object_id, 
    so.name, so.create_date, so.modify_date, 
    sc.colid , sc.text 
  FROM 
    sys.objects so 
  INNER JOIN 
    syscomments sc 
  ON 
    so.object_id = sc.id 
  WHERE 
    name = 'someStoredProc'
GO
---------------------------------------------------------------------------------------------
SELECT 'Grant Execute on dbo.[someStoreProc] to rolOne,rolTwo', @@SERVERNAME, DB_NAME()
GO
GRANT EXECUTE ON [dbo].[someStoredProc] TO [rolOne] as [dbo]
GO
GRANT EXECUTE ON [dbo].[someStoredProc] TO [rolTwo] as [dbo]
GO
---------------------------------------------------------------------------------------------
-- CHECK RIGHTS
SELECT 
  TOP 10 
    SUBSTRING(so.name,1,20) as ProcName
    SUBSTRING(sdpee.name,1,20) as PrincipalName, 
    SUBSTRING(sdp.permission_name,1,20) as Permission
  FROM 
    sys.objects so 
  INNER JOIN
    sys.database_permissions sdp ON so.object_id = major_id
  INNER JOIN
    sys.database_principals sdpee ON sdp.grantee_principal_id = sdpee.principal_id
  WHERE
    so.name LIKE 'someStoredProc'
GO
---------------------------------------------------------------------------------------------------------
 

It’s a lot of script just to install one stored proc – but it was the only way to see what was going on in a scenario where we didn’t have direct access to the database, and weren’t doing the install ourselves…

Thanks Nat!