IT:AD:SQL Server:HowTo:SQL:DDL:Deployment/Log [Notes]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
# IT:AD:SQL Server:HowTo:SQL:DDL:Deployment/Log #




<callout type="Navigation" class="small">

* [[../|(UP)]]
{{indexmenu>.#2|nsort tsort}}

* See also:
    * [[IT/AD/SQL Server/HowTo/SQL/DDL/Stored Procs/Retrieving]]


</callout>


<panel title="Summary">

We’ve been having trouble sniffing out what’s going on when delivering code to a very secure environment.


</panel>


## Solution ##

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!