Infamous SQL "upsert" within Powershell

PowerShell Help
1

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()
}
2

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

3

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
"@