Delphix Products

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
------------------------------

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
------------------------------

Original Message:
Sent: 10-28-2020 03:54:31 AM
From: Rahim Cetinel
Subject: PowerShell and SQL Server Hooks and Logging

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
------------------------------