66 lines
4.5 KiB
PowerShell
66 lines
4.5 KiB
PowerShell
Remove-Variable * -ErrorAction SilentlyContinue
|
||
$Error.Clear()
|
||
cls
|
||
Set-Variable -Scope Global -Name Timestamp3 -Value $(Get-Date -Format 'ddMMyyyy_HHmmssffff')
|
||
|
||
$StackID = "1013_2100841558 (1)1013_2100841558 (1)"
|
||
$filename = "test1_$Timestamp3.json"
|
||
|
||
function ConnectionString() {
|
||
return "Data Source=SQL-SRV;Initial Catalog=DD_ECM;Trusted_Connection=True;TrustServerCertificate=true;";
|
||
}
|
||
|
||
function Start-SQLDB-StoredProcedure-withLogging {
|
||
|
||
Param (
|
||
[Parameter(Position=0,Mandatory=$true,HelpMessage='Give the SQL Servername or Server name incl. Instance name')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[STRING]$value,
|
||
|
||
[Parameter(Position=1,Mandatory=$False,HelpMessage='Give the database user name')]
|
||
[ValidateNotNullOrEmpty()]
|
||
[STRING]$value2
|
||
)
|
||
|
||
Write-Host "param1 $value"
|
||
Write-Host "Param2 $value2"
|
||
|
||
$connection = ConnectionString;
|
||
$query = "[PRCUST_EXPORT_OCR_TO_WAWI]";
|
||
|
||
$sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection
|
||
$sqlConnection.Open()
|
||
|
||
$sqlCmd = new-object System.Data.SqlClient.SqlCommand("$query", $sqlConnection)
|
||
|
||
$sqlCmd.CommandType = [System.Data.CommandType]"StoredProcedure"
|
||
|
||
$sqlCmd.Parameters.AddWithValue("@pSTACK_ID", $value) | out-null
|
||
$sqlCmd.Parameters.AddWithValue("@pFILE_NAME", $value2) | out-null
|
||
|
||
$sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int") #| out-null
|
||
$sqlCmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue"
|
||
|
||
$sqlCmd.ExecuteNonQuery() | out-null
|
||
$sqlConnection.Close()
|
||
|
||
$rc = [int]$sqlCmd.Parameters["@ReturnValue"].Value
|
||
return $rc
|
||
}
|
||
|
||
$out = Start-SQLDB-StoredProcedure-withLogging -value $StackID -value2 $filename
|
||
|
||
Write-Host $out
|
||
|
||
[String]$ConnString = "Data Source=SQL-SRV;Initial Catalog=DD_ECM;Trusted_Connection=True;TrustServerCertificate=true;"
|
||
[String]$SQLQuery = "SELECT * FROM [DD_ECM].[dbo].[TBCUST_EXPORT_OCR_TO_WAWI_ARCHIVE] WHERE [INSIDERS_STACK_ID] = '$StackID' AND [FILE_NAME] = '$filename'"
|
||
|
||
|
||
$SQLResult = Invoke-Sqlcmd -Query $SQLQuery -ConnectionString $ConnString
|
||
|
||
#Write-Host $SQLResult[0]
|
||
|
||
foreach($row in $SQLResult)
|
||
{
|
||
$row.Item("FILE_CONTENT") | Out-File "E:\DataFiles\$filename"
|
||
} |