Examples running SQL Stored Procedures from Powershell with Output Parameters

This topic contains 2 replies, has 3 voices, and was last updated by Profile photo of Peter Jurgens Peter Jurgens 2 years, 11 months ago.

  • Author
    Posts
  • #14804
    Profile photo of Windows LiveUser61
    Windows LiveUser61
    Participant

    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

  • #15337
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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!

  • #15400
    Profile photo of Peter Jurgens
    Peter Jurgens
    Participant

    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.

You must be logged in to reply to this topic.