none
Gather all Errorlogs to one table via PS

    Question

  • Dear all,

    I'm new to Powershell.
    My PS version is:
    Major  Minor  Build  Revision
    5      0      10586  117  

    I'm trying to gather all the SQL Server Errorlogs from all our servers into one table in our DBA SQL Server (SQL Server 2012).

    I'm constantly getting an error

    Write-SqlTableData : The given value of type String from the data source cannot be converted to type datetime of the specified target column.
    +     Write-SqlTableData -ServerInstance $SqlInstanceDBARepositoty -Dat ...
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : WriteError: ([dbo].[PS_SQL_ErrorLog]:Table) [Write-SqlTableData], InvalidOperationException
        + FullyQualifiedErrorId : WriteToTableFailure,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData

    The repository table Creation script:

    CREATE TABLE [dbo].[PS_SQL_ErrorLog](
    	[LogID] [int] IDENTITY(1,1) NOT NULL,
    	[Server] [varchar](100) NOT NULL,
    	[LogDate] [datetime] NULL,
    	[Text] [varchar](1000) NULL,
     CONSTRAINT [PK_PX_SQL_ErrorLog] PRIMARY KEY CLUSTERED 
    (
    	[Server] ASC,
    	[LogID] ASC
    )


    The PowerShell script to harvest all the errorlogs to one repository table.:

    $SqlInstanceDBARepositoty = "servername";
    $DatabaseDBARepositoty = "DBADB";
    $SQLErrorLogTableDBARepositoty ="PS_SQL_ErrorLog"
    $InstanceNames = "SELECT ServerName = [Server instance]
    			FROM SERVERList";
    
    $results = Invoke-Sqlcmd -Query $InstanceNames -ServerInstance $SqlInstanceDBARepositoty -Database $DatabaseDBARepositoty ;
    
    foreach ($result in $results) 
    {
       $Server = $result.ServerName;
        
        Get-SqlErrorLog -ServerInstance $Server -Since LastWeek |select ServerInstance , Date  , Text |
            Where-object {   ($_.text  -notlike '*found 0 errors*')`
                    -and ($_.text  -notlike '*without errors*')`
    				#More filters here.
                         }  |    
       Write-SqlTableData -ServerInstance $SqlInstanceDBARepositoty -DatabaseName $DatabaseDBARepositoty  -SchemaName dbo -TableName $SQLErrorLogTableDBARepositoty -force
    }

    What am I doing wrong? How can I overcome this conversion issue ?

    Thanks in advance,

    Roni.

    Monday, May 14, 2018 11:44 AM

Answers

  • Hi Roni Vered Adar,

    What I see is you create a log table which has four columns and you just inserted 3 columns values into the table.

    Since you use the parameter -force, you don't need to create the table at first. The Powershell script will generate the table automatically.

    Based on my test, it should be able to load the SQL error log into the table and the type of Date column is datetime2 .

    $dt=Get-SqlErrorLog -ServerInstance "MachineName\SQL2017" -Since LastWeek |select ServerInstance,Date,Text
    
    Write-SqlTableData -ServerInstance MachineName\SQL2017 -InputData $dt -DatabaseName UseCases -SchemaName dbo -TableName PS_SQL_ErrorLog -Force

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, May 16, 2018 7:24 AM

All replies

  • Hi Roni Vered Adar,

    What I see is you create a log table which has four columns and you just inserted 3 columns values into the table.

    Since you use the parameter -force, you don't need to create the table at first. The Powershell script will generate the table automatically.

    Based on my test, it should be able to load the SQL error log into the table and the type of Date column is datetime2 .

    $dt=Get-SqlErrorLog -ServerInstance "MachineName\SQL2017" -Since LastWeek |select ServerInstance,Date,Text
    
    Write-SqlTableData -ServerInstance MachineName\SQL2017 -InputData $dt -DatabaseName UseCases -SchemaName dbo -TableName PS_SQL_ErrorLog -Force

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, May 16, 2018 7:24 AM
  • Thanks!

    I just noticed it myself today. The problem was the Identity column.

    I wonder how can I specify the column list (like in an Insert command), to still have the identity column and be able to insert the results correctly into the table.

    Roni.

    Wednesday, May 16, 2018 7:40 AM