I'm trying to figure out a good way to run insert/select/update statements into a MSSQL database through PowerShell scripting and log it thoroughly. I have managed to use Invoke-Sqlcmd which does the inserts and select statements pretty fine. But my concern is to capture the logs some where as an output file. That is when doing inserts the log should capture the no. of rows affected/inserted with the data that was inserted.
Invoke-Sqlcmd used:
PS C:\Users\moshink\Desktop\Powershell SQL> Invoke-Sqlcmd -Server $server -Database $db -Username $user -Password $pass -InputFile ".\test.sql" | Out-File -FilePath ".\test_$datetime.rpt" >> $LogFile
test.sql file query below:
use TCS_MIS
select * from tblMasterAccountType where acctType in ('0121') and intCat in ('0020')
--insert into tblMasterAccountType values ('DEP','0121','0020','PARENTHOOD ASSISTANCE ACCOUNT')
the test.sql file is called in the Invoke-Sqlcmd. This file can be dynamic with any query going in there.
The Out-File ".\test_$datetime.rpt" does capture the select statement outputs if the data exits matching the criteria, but it will be blank if no data.
Is there something that can be ran to capture instantly when running a Insert .sql file/script? i.e. which will say 20 rows inserted and listing out the data inserted. Basically what I'm after is a thorough logging when running any .sql scripts through PowerShell. It should capture no of rows affected with the data inserted/updated/deleted and the user who performed it.