Examples running SQL Stored Procedures from Powershell with Output Parameters

Welcome Forums General PowerShell Q&A Examples running SQL Stored Procedures from Powershell with Output Parameters

This topic contains 2 replies, has 3 voices, and was last updated by

 
Participant
4 years, 10 months ago.

  • Author
    Posts
  • #14804

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

    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

    Participant
    Points: 0
    Rank: Member

    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.

The topic ‘Examples running SQL Stored Procedures from Powershell with Output Parameters’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort