Delphix Products

Expand all | Collapse all

PowerShell and SQL Server Hooks and Logging

  • 1.  PowerShell and SQL Server Hooks and Logging

    Posted 10-28-2020 03:55:00 AM
    Hello everyone,

    I want to run masking SQL script against  a database running in MS SQL Server. Normally we can use Hooks in Delphix to run PowerShell scripts which run the SQL script that is located in a directory.

    A simple example is below:

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    $SQLInstance = "TestServerOne"
    $db1 = "TestDB3"

    Invoke-Sqlcmd -ServerInstance $SQLInstance -Database $db1 -InputFile "C:\Files\TSQL\run.sql"

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    What I want to learn is how we can get the output for the queries that are run in this SQL script?

    The second thing is that, I would like to get the info from SQL script whether any one of the queries running in the script get any error or the script finished everything successfully and use this info in Configure Clone so that I can stop the vDB if any error happened during the script run.

    Do any of you prepared similar scenarios like above? Do you have any example scripts that I can use as a starting point?

    BR,

    ------------------------------
    Rahim Cetinel
    Solution Architect | Delphix Blackbelt
    ------------------------------


  • 2.  RE: PowerShell and SQL Server Hooks and Logging

    Posted 10-28-2020 04:33:00 AM
    Hi Rahim,

    Mainlly two ways for the first part :

    Spool from sql script directlly
    add this into your sql script

    spool "C:\ScriptFolder\MySqlCmd.rpt" and at the end of script spool off

    or

    Add OutputSqlErrors flag and -IncludeSqlUserErrors to the powershell command and pipe the command to an output file

    Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" -OutputSqlErrors $true -IncludeSqlUserErrors | Out-File -FilePath "C:\ScriptFolder\MySqlCmd.rpt"

    Second part :

    Test if the script suceeded from powershell or check for success message from the spool and rake the appropriate action.

    Regards,

    Mouhssine 


    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------