Parameterized SQL Statements

Question

I'm trying to use a simple insert query to fill a SQL table. Currently, my script creates the query by concatenating strings but I would like to parameterize the query in order to avoid getting the following error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "String or binary data would be truncated.

Currently I am setting up my connection, like so:

$conn = New-Object System.Data.SqlClient.SQLConnection 
$ConnectionString = "Server=$SQLServer;Database=$SQLDatabase;Integrated  Security=True;Connect Timeout=0"
$conn.ConnectionString = $ConnectionString 
$conn.Open()

and my un-parameterized query looks like this:

$commandText = "INSERT INTO Servers (Name, OS, Notes, OSOwner) VALUES('$myvm', '$operatingSystem', '$desc', '$owner')"
$command = $conn.CreateCommand()
$command.CommandText = $commandText
$command.ExecuteNonQuery()

I've seen people discuss different solutions to this in other posts but I'm not sure what the best approach is.

score 1 · Answer 1 · answered Jan 04 '16 at 15:15

1

Parameterization won't remove the problem, it'll just move it to a different section of code.

Your Servers table is designed such that one or more of the fields is smaller than the value you're attempting to insert into it. Fix that first - you've made an incorrect assumption about your data sizes.

answered Jan 04 '16 at 15:15

alroc

27,574
6
51
97

Hmm...thats what I thought originally, but didn't spot any data that looked truncated out of the 2,000 some rows. I'll look a bit closer, thanks man. – Brennan Morell Jan 04 '16 at 15:25
To debug, output `$commandtext`, then copy & paste it into SSMS. It should throw the same error. – alroc Jan 04 '16 at 15:32

nimizen · Answer 2 · 2016-01-05T20:21:33.503

alroc is correct in that the error you're seeing is related to data sizes; to provide an answer on parameterisation, here's what I would suggest:

function execSQL($sqlQuery) {

try {
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Server=$SqlServer; Database=$sqlDB; Application Name=$appName; Trusted_Connection=True"
    $sqlCmd.CommandTimeout = 60
    $sqlCmd.CommandText = $sqlQuery
    $sqlCmd.Connection = $sqlConnection
    $sqlCmd.ExecuteNonQuery()

}
catch [system.exception] {
    "$_ `r`nSQL Servername: $SqlServer" #send to log file
}
finally {
    $SqlConnection.Close()
    $SqlConnection.Dispose()
}

}


$appName = "brief description of what the SQL connection is being used for"

$sqlQuery = @"
INSERT INTO Servers (Name, OS, Notes, OSOwner) 
VALUES(@myvm, @operatingSystem, @desc, @owner)
"@

$sqlCmd = New-Object System.Data.SqlClient.SqlCommand

$sqlCmd.Parameters.AddWithValue("@myvm", $myvm) | Out-Null
$sqlCmd.Parameters.AddWithValue("@operatingSystem", $operatingSystem) | Out-Null
$sqlCmd.Parameters.AddWithValue("@desc", $desc) | Out-Null
$sqlCmd.Parameters.AddWithValue("@owner", $owner) | Out-Null

execSQL $sqlQuery

Supplying an application name in the connection string helps your DBA to understand what the connection is if they're auditing connections. Wrapping the SQL stuff in a try/catch/finally construct allows us to catch errors but more importantly, even if an error is thrown, the SQL connection will be tidied up.

Parameterized SQL Statements

2 Answers2