Examples running SQL Stored Procedures from Powershell with Output Parameters

PowerShell Help
1

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
2

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!

3

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.