Author Posts

April 24, 2014 at 12:46 pm

Does anyone have a good example of calling a SQL Server stored procedure from powershell with output parameters that returns a string with more than one character? I have a stored procedure with an output parameter that returns a char(3) value and I am only getting one character returned.

Here is my code. @CanGrow should return a value of "Yes" or "No" but all I get is Y or N. @Reason should return a string of about 30 characters but I only get the first letter.


$connString = "Server=$CentralmanagementServer;Database=$CMSdb_name;Trusted_Connection=True"

$connection = new-object System.Data.SqlClient.SqlConnection $connString

$connection.Open()

$Command = new-Object System.Data.SqlClient.SqlCommand("abc_can_datafile_be_grown", $connection)

$Command.CommandType = [System.Data.CommandType]'StoredProcedure'

$Command.Parameters.Add("@Serverid", [System.Data.SqlDbType]"int")

$Command.Parameters["@Serverid"].Value = [int]$DB.Serverid

$Command.Parameters.Add("@Databaseid", [System.Data.SqlDbType]"int")

$Command.Parameters["@Databaseid"].Value = [int]$DB.DatabaseId

$Command.Parameters.Add("@DatabaseName", $DB.DatabaseName)

$Command.Parameters.Add("@MonitorType", "GROWDATAFILE")

$Command.Parameters.Add("@CanGrow","")

$Command.Parameters["@CanGrow"].Direction = [system.Data.ParameterDirection]::Output

$Command.Parameters["@CanGrow"].DbType = [System.Data.DbType]'String';

$Command.Parameters.Add("@Reason","") | out-null

$Command.Parameters["@Reason"].Direction = [system.Data.ParameterDirection]::Output

$Command.ExecuteNonQuery() | Out-Null

$NotWhitelisted = $Command.Parameters["@CanGrow"].value

$WhitelistedReason = $Command.Parameters["@Reason"].value

$connection.Close() | Out-Null

$Command.Dispose() | Out-Null

$connection.Dispose() | Out-Null

May 18, 2014 at 11:53 am

Sorry – seems like nobody's been trying that here. This is really just .NET Framework – I'm betting a search or post on StackOverflow.com would help!

May 19, 2014 at 7:10 pm

I haven't tried this out with running a stored procedure, usually I've just written the SQL query into the commandText property of the sqlcommand object, but there definitely is a difference in executing the query against your connection by using the ExecuteNonQuery() method and the ExecuteReader() method. Have a look at the two different methods in the methods list from the sqlcommand class on technet:

SQLCommand

replace this:

$Command.ExecuteNonQuery() | Out-Null
$NotWhitelisted = $Command.Parameters["@CanGrow"].value
$WhitelistedReason = $Command.Parameters["@Reason"].value

with this:

$results = $command.ExecuteReader()
$data = new-object System.Data.DataTable
$data.Load($results)
write-output $data

Try that out and let's see if it works.