Post reply

INSERT INTO SQL Server Table

  • May 17, 2017 at 3:16 pm

    #402071

    Hi, Help is required with the next step for the code below.  The code connects to SQL Server, executes a stored procedure which outputs a list of perfmon counters.  The values are fed into 'get-counter' command to return the values for the counters.

    Now I want to store these values in a SQL Server table and I want to understand the best approach to take.

    Any advice or information will be appreciated.

    Code

     $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "integrated security=SSPI; data source=MyServer,Port; initial catalog=My_db;"

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

    $SqlCmd.CommandText = "dbo.mySP"

    $SqlCmd.Connection = $SqlConnection

    $SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure

    $p1=$SqlCmd.Parameters.Add("@param", "")

    $p1.Direction = [system.Data.ParameterDirection]::Output

    $p1.Size=4000

    $SqlConnection.Open()

    $SqlCmd.ExecuteNonQuery()

    $output = $p1.SqlValue.Value

    [string[]] $counterlist = @()

    $counterlist = $output -split ","

    $Results = get-Counter -Counter $counterlist

    Example OUTPUT

    \\MyServer\processor(_total)\% privileged time :

    0

    \\MyServer\processor(_total)\% processor time :

    0.00540970733483581

  • May 18, 2017 at 11:31 am

    #1943162

    If I read the code correctly, you're calling a stored procedure? Have it insert the data at the same time it retrieves it.

    Or, are you trying to store that in a different server? If so, you should not execute a non-query as you have there. You want a result set. Use SQLCommand or Invoke-Sqlcmd to get one. I prefer the latter.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply