insert the errorlog in sql table

Question

insert the errorlog in sql table

joujousagem2006 1602

SSCommitted

Points: 1779
More actions
August 14, 2018 at 12:18 pm

#376055

Hello everyone
I am looking for a method to insert the contents of this script into a sql server table
Import-Module sqlps -DisableNameChecking $serveurs=@("localhost\sqlprod","locahost\sqlprod02","localhost\analyse") $query="exec xp_readerrorlog 0 ,1" foreach($server in $serveurs) { $results=invoke-sqlcmd -Query $query -ServerInstance $server -Database master foreach($result in $results) { $result|Select-Object LogDate,ProcessInfo,Text|Select-Object -Property @{n="server";e={$server}},LogDate,ProcessInfo,Text
} }
for that I created the following table
CREATE TABLE [dbo].[#TmpErrorLog] (servername varchar(max) , [LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(20) NULL, [Text] VARCHAR(MAX) NULL ) ;
Then I want to insert the content in the following table
INSERT INTO TmpErrorLog ([LogDate], [ProcessInfo], [Text])EXEC [master].[dbo].[xp_readerrorlog] 0 ;
Who can help me please on this need
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

joujousagem2006 1602 - Tuesday, August 14, 2018 12:18 PM
Hello everyone
I am looking for a method to insert the contents of this script into a sql server table
Import-Module sqlps -DisableNameChecking $serveurs=@("localhost\sqlprod","locahost\sqlprod02","localhost\analyse") $query="exec xp_readerrorlog 0 ,1" foreach($server in $serveurs) { $results=invoke-sqlcmd -Query $query -ServerInstance $server -Database master foreach($result in $results) { $result|Select-Object LogDate,ProcessInfo,Text|Select-Object -Property @{n="server";e={$server}},LogDate,ProcessInfo,Text
} }
for that I created the following table
CREATE TABLE [dbo].[#TmpErrorLog] (servername varchar(max) , [LogDate] DATETIME NULL, [ProcessInfo] VARCHAR(20) NULL, [Text] VARCHAR(MAX) NULL ) ;
Then I want to insert the content in the following table
INSERT INTO TmpErrorLog ([LogDate], [ProcessInfo], [Text])EXEC [master].[dbo].[xp_readerrorlog] 0 ;
Who can help me please on this need
thanks

You just need to add the pound sign before the table name since it's a temp table you created for this. And the extended stored procedure is in sys schema rather than dbo.
INSERT INTO #TmpErrorLog EXEC master.sys.xp_readerrorlog 0

Sue

joujousagem2006 1602 SSCommitted Points: 1779 More actions · Answer 2

but I'm trying to insert the result of the variable $result

Sue_H SSC Guru Points: 90860 More actions · Answer 3

joujousagem2006 1602 - Wednesday, August 15, 2018 9:09 AM
but I'm trying to insert the result of the variable $result

If you want to insert data from Powershell into a SQL Server table, take a look at the Write-SQLTableData cmdlet available in the SQLServer or SQLPS module. Refer to the following for examples:
PowerShell Invoke-SQLCmd outputs DataTables you can INSERT into SQL Server
New PowerShell cmdlets to read and write SQL Server tables

Sue