Infamous SQL "upsert" within Powershell

Tagged: , ,

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 4 months, 1 week ago.

  • Author
    Posts
  • #61671
    Profile photo of Creed Cordonier
    Creed Cordonier
    Participant

    Hello. I am sure there may be 1000 better ways to do this. However, this is where I am at.

    I am trying to achieve an 'UPSERT' using SQL, IF NOT EXISTS, in my $sqlcommandtext within Powershell. Currently the script errors with an, "Exception calling "ExecuteNonQuery" ... : "String or binary data would be truncated"

    All columns are varchar(50). All data types are string. All have value length < 50. This script works if I exclude the SQL ELSE statement...

      ELSE
                       BEGIN
    	            UPDATE NetworkInfo
    	                SET MacAddress = '$info.MacAddress',
                            IpAddress = '$info.IpAddress',
                            DefaultGateway = '$info.DefaultIPGateway',
                            ApMAC = '$apMAC',
                            LastUser = '$user',
                            SerialNumber = '$serialNumber',
                            Manufacturer = '$manu'
                                WHERE Computer = '$computer'
    		                    END"

    Here is the full script. Any advice much appreciated.

    # load the sqlps module
    Import-Module Sqlps -DisableNameChecking;
    
    # dump the 'netsh wlan show interface' command output into $wlanraw 
        $wlanraw = netsh wlan show interface
    
    # create an object as "empty"
        $objWlan = "" | Select-Object BSSID
    
    # populate the object from the output, processing 1 line at a time
        ForEach ($Line in $wlanraw) {if ([regex]::IsMatch($Line,"    BSSID")) {
        	 	$objWlan.BSSID = $Line -Replace"    BSSID                  : ",""
    		}
    }
    
    # dump result into own variable
    $apMAC = $objWlan.BSSID
    
    # create computer specific variables
    $computer = $env:COMPUTERNAME
    $user = $env:USERNAME
    $serialNumber = Get-WmiObject Win32_Bios | select -ExpandProperty SerialNumber
    $manu = Get-WmiObject win32_computersystem | select -ExpandProperty Manufacturer
    
    # function to create objects from each property in win32_networkadapterconfiguration
    function netInfo() {
    
    get-wmiobject Win32_NetworkAdapterConfiguration -filter "IPEnabled=TRUE" -computername $computer | foreach-object {
      new-object PSObject -property @{
        Computer = $computer
        MACAddress = $_.MACAddress
        IPAddress = $_.IPAddress[0]
        DefaultIPGateway = $_.DefaultIPGateway[0]  
      } | select-object Computer,MACAddress,IPAddress,DefaultIPGateway}
      }
    
    # open SQL Connection
    $DBServer = "SERVER\INSTANCE"
    $DBName = "DBNAME"
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"
    $sqlConnection.Open()
    
    # check if the connection is open, exit if not
    if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {
        "Connection to DB is not open."
        Exit
    }
    
    # load netInfo function into a variable 
    $info = netInfo
    foreach($i in $info){
    
    $sqlCmdtxt = "  IF NOT EXISTS (SELECT * FROM NetworkInfo WHERE Computer = '$computer')
                    BEGIN
                    INSERT INTO dbo.NetworkInfo (Computer,MacAddress,IpAddress,DefaultGateWay,ApMAC,LastUser,SerialNumber,Manufacturer) 
                    VALUES ('$($i.Computer)','$($i.MacAddress)','$($i.IpAddress)','$($i.DefaultIPGateway)','$apMAC','$user','$serialNumber','$manu')
                       END
                       ELSE
                       BEGIN
    	            UPDATE NetworkInfo
    	                SET MacAddress = '$info.MacAddress',
                            IpAddress = '$info.IpAddress',
                            DefaultGateway = '$info.DefaultIPGateway',
                            ApMAC = '$apMAC',
                            LastUser = '$user',
                            SerialNumber = '$serialNumber',
                            Manufacturer = '$manu'
                                WHERE Computer = '$computer'
    		                    END"
    
        $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
        $sqlCommand.Connection = $sqlConnection
        $sqlCommand.CommandText = $sqlCmdtxt
        $sqlCommand.ExecuteNonQuery()
    }
    
    # Close connection when finished
    if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {
        $sqlConnection.Close()
    }
  • #61674
    Profile photo of Creed Cordonier
    Creed Cordonier
    Participant

    Hello,

    I got this working by using a transaction. Altered my SQL command like so...

    $sqlCmdtxt = "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
     BEGIN TRANSACTION
     UPDATE dbo.NetworkInfo SET IpAddress = '$($i.IpAddress)', DefaultGateway = '$($i.DefaultIPGateway)', ApMAC = '$apMAC', LastUser = '$user' WHERE Computer = '$computer';
     IF @@ROWCOUNT = 0
     BEGIN
     INSERT INTO dbo.NetworkInfo (Computer,MacAddress,IpAddress,DefaultGateWay,ApMAC,LastUser,SerialNumber,Manufacturer) VALUES ('$($i.Computer)','$($i.MacAddress)','$($i.IpAddress)','$($i.DefaultIPGateway)','$apMAC','$user','$serialNumber','$manu')
     END
     COMMIT TRANSACTION"

    It works!

    Thank you

  • #61680
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    You're previous method would work, but I would highly recommend using a here string and leveraging SQL variables to clean things up. I've pasted an example from a previous project below. This is clean and if you simply echo $sqlCMD you can copy and paste it into SQL Management Studio\Toad\Whatever to troubleshoot syntax issues.

    Another thing I noticed is that you are Importing SQLPS. If you are importing that module and it's available where you are executing the script, you can use Invoke-SQLCmd versus the $sqlConnection.

    #********************#
    # ** DO NOT IDENT ** #
    #********************#
    $sqlCMD = @"
    Declare @TITLE nvarchar(50) = '$($Title)'
    Declare @DESCRIPTION nvarchar(250) = '$($Description)'
    Declare @COMMANDLINE nvarchar(250) = '$($CommandLine)'
    Declare @WORKINGDIR nvarchar(250) = '$($WorkingDir)'
    Declare @ADMINISTRATIVE_NOTE nvarchar(MAX) = '$($AdministrativeNote)'
    Declare @ENABLED bit = '$($Enabled)'
    Declare @APP_ID smallint = '$($ApplicationID)'
    
    IF EXISTS (Select TITLE From [Inventory].[dbo].[tblMyApps] Where Title = @TITLE)
    	BEGIN
            UPDATE [dbo].[tblMyApps]
               SET [TITLE] = @TITLE
                  ,[DESCRIPTION] = @DESCRIPTION
                  ,[COMMANDLINE] = @COMMANDLINE
                  ,[WorkingDir] = @WORKINGDIR
                  ,[ADMINISTRATIVE_NOTE] = @ADMINISTRATIVE_NOTE
                  ,[ENABLED] = @ENABLED
                  ,[APP_ID] = @APP_ID
             WHERE Title = @TITLE
    	END
    ELSE
    	BEGIN
            INSERT INTO [dbo].[tblApps]
                ([TITLE]
                ,[DESCRIPTION]
                ,[COMMANDLINE]
                ,[WorkingDir]
                ,[ADMINISTRATIVE_NOTE]
                ,[ENABLED]
                ,[APP_ID])
            VALUES
                (@TITLE
                ,@DESCRIPTION
                ,@COMMANDLINE
                ,@WORKINGDIR
                ,@ADMINISTRATIVE_NOTE
                ,@ENABLED
                ,@APP_ID)
    	END
    "@
    

You must be logged in to reply to this topic.