Skip to content

Instantly share code, notes, and snippets.

@ashdar

ashdar/Demo-SqlCmd2WithPersistantConnection.ps1

Created August 14, 2018 18:20
Show Gist options
  • Save ashdar/a97eeb5d07249b85ae6469b27f0069bb to your computer and use it in GitHub Desktop.
Save ashdar/a97eeb5d07249b85ae6469b27f0069bb to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ashdar created this gist Aug 14, 2018.
    22 changes: 22 additions & 0 deletions Demo-SqlCmd2WithPersistantConnection.ps1
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,22 @@
    # Create the SqlConnection object
    $s = New-Object System.Data.SqlClient.SqlConnection
    # Build an appropriate string, using the string builder from DbaTools
    $s.ConnectionString = New-DbaSqlConnectionStringBuilder -DataSource hal9000 -ApplicationName 'MyAppName' -InitialCatalog 'tempDB' -WorkstationId dstraitv7 -ColumnEncryptionSetting Disabled -IntegratedSecurity $true
    # Run a query to show that we are not yet in a transaction.
    # Since this is the first use of $s, this will open the connection if it isn't open already
    Invoke-SqlCmd2 -Connection $s -Query "select 0 'QueryNumber', @@TRANCOUNT as CountOfTranBeforeBegin"
    # Start a transaction
    Invoke-SqlCmd2 -Connection $s -Query "begin tran"
    # Queries 1 and 2 are just contrived nonsense
    Invoke-SqlCmd2 -Connection $s -Query "select 1 'QueryNumber', @@servername as ServerName"
    Invoke-SqlCmd2 -Connection $s -Query "select 2 'QueryNumber', getdate() as RightNow"
    # Query 3 shows that we are still in a transcation
    Invoke-SqlCmd2 -Connection $s -Query "select 3 'QueryNumber', @@TRANCOUNT as CountOfTranBeforeRollback"
    # Next, we rollback the transaction, which will will close it. We could just as easily COMMIT.
    # If the transaction does not persist across invocations of Invoke-sqlcmd2, this will FAIL
    Invoke-SqlCmd2 -Connection $s -Query "rollback tran"
    # Show that we are no longer in a transaction
    Invoke-SqlCmd2 -Connection $s -Query "select 4 'QueryNumber', @@TRANCOUNT as CountOfTranAfterRollback"
    # clean up
    $s.Close()
    $s = $null