Powershell job step should throw an error is not succesfull

Hi,

I have the below code to export some tables into text files.  It is part of an sql job.  The next step (dropping those tables) should not run, unless the export was successful.

This is not a case however.  if the powershell script fails (due to a syntax error for example), the job step shows as successful and the job moves to the next step, which should not happen.

How do I make the below code fail the job step if any errors in powershell execution?

 

Thanks.

------------------------------------------------------------------------------------------

SL SQLSERVER:\SQL\"MyServer\MyInstance"\Databases\"MyDB_Archive"\Tables

$ServerName = Invoke-Sqlcmd -query "SELECT @@ServerName" 

$bcpconn = '-T'

$TableList = Invoke-Sqlcmd -query "select name from MyDB_Archive..sysobjects (nolock) where name like 'Table1_______' and type = 'U'" 

foreach($item in $TableList) {

  $table = $item.name

  $Query = '"select * from MyDB_Archive.dbo.' + $table + '"'

  $Saveas = Invoke-Sqlcmd -query "SELECT [value] FROM [SupportDB].[Support].[PartitioningConfiguration] (nolock) WHERE name = 'ArchivePath'" 

  $ReplaceServername = $ServerName.Column1 -replace '\\', '_'

  $Saveas.value = $Saveas.value + $ReplaceServername + '_MyDB_Archive_' + $table + '.txt'

  bcp $Query  QUERYOUT $Saveas.value -n $bcpconn -S $ServerName.Column1

}

 

 

$TableList = Invoke-Sqlcmd -query "select name from MyDB_Archive..sysobjects (nolock) where name like 'Table2_______' and type = 'U'" 

foreach($item in $TableList) {

  $table = $item.name

  $Query = '"select * from MyDB_Archive.dbo.' + $table + '"'

  $Saveas = Invoke-Sqlcmd -query "SELECT [value] FROM [SupportDB].[Support].[PartitioningConfiguration] (nolock) WHERE name = 'ArchivePath'" 

  $ReplaceServername = $ServerName.Column1 -replace '\\', '_'

  $Saveas.value = $Saveas.value + $ReplaceServername + '_MyDB_Archive_' + $table + '.txt'

  bcp $Query  QUERYOUT $Saveas.value -n $bcpconn -S $ServerName.Column1

}

 

 

 

 

 

 

Parents
No Data
Reply Children
No Data