powershell with sql server help

  • vsamantha35

    SSChampion

    Points: 11170

    Hi All,

    I wanted to load windows application log into sql server table. I am using below command but it was throwing error.

    Can anyone help me on how to fix this problem.

    Followed below article to load the windows event log to a table. I created below table and ran below powershell command

    https://www.red-gate.com/simple-talk/blogs/storing-windows-event-viewer-output-in-a-sql-server-table-with-powershell/

    CREATE TABLE [dbo].[EventViewer]

    (

    [Index] [int] NULL,

    [Time] [datetime] NULL,

    [EntryType] [varchar](MAX) NULL,

    [Source] [varchar](MAX) NULL,

    [InstanceID] [varchar](MAX) NULL,

    [Message] [varchar](MAX) NULL

    )

    GO

    Get-EventLog -ComputerName <machine name> -LogName Application -After "30-03-2020" | select index,TimeGenerated,EntryType,Source,InstanceID,Message | Out-DataTable | Write-DataTable -ServerInstance <machine name> -Database master -TableName EventViewer

    Error message

    Out-DataTable : The term 'Out-DataTable' is not recognized as the name of a cmdlet, function, script file, or operable

    program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

    At line:1 char:159

    + ... Generated,EntryType,Source,InstanceID,Message | Out-DataTable | Write ...

    + ~~~~~~~~~~~~~

    + CategoryInfo : ObjectNotFound: (Out-DataTable:String) [], CommandNotFoundException

    + FullyQualifiedErrorId : CommandNotFoundException

    Powershell version

    ====================

    PS C:\> Get-Host | Select-Object Version

    Version

    -------

    5.1.18362.628

    Regards,

    Sam

  • Ken McKelvey

    SSCoach

    Points: 18305

    Out-DataTable is not built into Powershell. You have to download it from:

    https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

    Then the first line of your Powershell script will be something like:

    . "C:\YourScriptPath\Out-DataTable.ps1"

  • vsamantha35

    SSChampion

    Points: 11170

    Thank you Ken. I will check on it.

  • vsamantha35

    SSChampion

    Points: 11170

    Ken,

    I tried including "Out-DataTable" as well as "Write-DataTable" functions code within my powershell script, still I am getting error.

    https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

    https://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

     

    My observation is that, in Write-DataTable function , there is a positional parameter 4 which is $data and I expecting the windows log data should be fed be fed as input to that parameter $Data but don't know why it was throwing below error message.

    Any help in fixing this below error ?

     

    cmdlet Write-DataTable at command pipeline position 4

    Supply values for the following parameters:

    Data:

    Write-DataTable : The input object cannot be bound to any parameters for the command either because the command does

    not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

    At E:\scripts\LoadEventviewerToSQL.ps1:174 char:154

    + ... DataTable | Write-DataTable -ServerInstance <machinename> -Database ...

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : InvalidArgument: (System.Data.Dat...em.Data.DataRow:PSObject) [Write-DataTable], Paramet

    erBindingException

    + FullyQualifiedErrorId : InputObjectNotBound,Write-DataTable

    PS E:\scripts>

     

  • vsamantha35

    SSChampion

    Points: 11170

    my ps script code looks something like below :

    function Out-DataTable

    {

    #.....

    }

    function Write-DataTable

    {

    #.....

    }

    Get-EventLog -ComputerName srv1 -LogName Application | select index,TimeGenerated,EntryType,Source,InstanceID,Message | Out-DataTable | Write-DataTable -ServerInstance srv1 -Database master -TableName EventViewer

     

     

  • Ken McKelvey

    SSCoach

    Points: 18305

    I have never used Write_DataTable as I am normally reading multiple csv files and do the BulkCopy bit directly in ADO.net.

    You could look at loading the sqlserver module and using Write-SqlTableData. Example 1 below looks similar to what you want:

    https://docs.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps

     

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

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