How do we run multiple sql scripts in one transaction, where each script may have the same variable defined?

file1:

declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '1apples' convert(varchar(28), getdate(),121)
select @query = 'select name into '   quotename(@tableName)   ' from sys.objects'
print @query
exec (@query)
go

file2:

declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '2apples' convert(varchar(28), getdate(),121)
select @query = 'select name into '   quotename(@tableName)   ' from sys.objects'
print @query
exec (@query)
 go

I am executing the files like this in PowerShell:

try {

  $files = Get-ChildItem -Path $SqlFilesDirectory -File -Filter *.sql
  $filesSorted = $files |Sort-Object -Property Name
  $filesconcatenated = @(
      $files |Get-Content -Raw
  ) -join [Environment]::NewLine
  $filesconcatenated = 'SET XACT_ABORT ON '   $filesconcatenated   ' SET XACT_ABORT OFF '   [Environment]::NewLine
    $cmd = New-Object System.Data.SqlClient.SqlCommand ($filesconcatenated), $conn
    $cmd.Transaction = $tran
    [void]$cmd.ExecuteNonQuery()
   
  $tran.Commit()
}
catch {
  $tran.Rollback()
  Write-Host "Record not Inserted ->"
  $_.exception.message
}
finally {
  $conn.Close()
}

I'm getting the following exception:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near the keyword 'declare'. The variable name '@tableName' has already been declared. Variable names must be unique within a query batch or stored procedure. The variable name '@tableName' has already been declared. Variable names must be unique within a query batch or stored procedure. Incorrect syntax near the keyword 'SET'."

How do we run multiple SQL scripts in one transaction, where each script may have the same variable defined?

Here's the complete script it's trying to run:

SET XACT_ABORT ON 
declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '1apples' convert(varchar(28), getdate(),121)
select @query = 'select name into '   quotename(@tableName)   ' from sys.objects'
print @query
exec (@query)
go
declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '2apples' convert(varchar(28), getdate(),121)
select @query = 'select name into '   quotename(@tableName)   ' from sys.objects'
print @query
exec (@query)
go
declare @tableName varchar(28), @query nvarchar(1000)
set @tableName = '3apples' convert(varchar(28), getdate(),121)
select @query = 'select name into '   quotename(@tableName)   ' from sys.objects'
print @query
exec (@query)
go
 SET XACT_ABORT OFF 

CodePudding user response：

You cannot have the same variable twice in a single batch

So execute them as separate commands, by looping the array

$connString = "Server=tcp:$ServerName.database.windows.net,1433;Database=$DBName;User Id=$SvcAdminAccount@$ServerName;Password=$SvcAdminPassword;MultipleActiveResultSets=true;Persist Security Info=true;";
$conn = New-Object System.Data.SqlClient.SqlConnection $connString try
{
  $conn.Open()
  $files = Get-ChildItem -Path $SqlFilesDirectory -File -Filter *.sql
  $filesSorted = $files |Sort-Object -Property Name
  $tran = $conn.BeginTransaction()
  foreach ($file in $filesSorted)
  {
    $query = "SET XACT_ABORT ON; "   $file
    $cmd = New-Object System.Data.SqlClient.SqlCommand ($query, $conn, $tran)
    [void]$cmd.ExecuteNonQuery()
  }
  $tran.Commit();
}
catch {
  Write-Host "Record not Inserted ->"
  $_.exception.message
}
finally {
  $tran.Dispose()
  $conn.Dispose()
}

Note the way Dispose is used, rather than Rollback, because Dispose will never throw an exception. It's also in the finally rather than the catch