question

Anne-0140 avatar image
0 Votes"
Anne-0140 asked Anne-0140 commented

create a log file for a process that query SQL and export csv

I need to create powershell script on SQL server A.

The script will connect to a remote SQL server B and run a query there and then export it as a csv file on a network directory.
I know I can use something like this:
Invoke-Sqlcmd -Query $SqlQuery -ServerInstance $SourceServer |
Export-Csv -Path "$destOutputPath\myfile.dat" -NoTypeInformation -Encoding UTF8

But I also want to create a log file that log the process of this powershell script, how can I do it?

Thanks

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered Anne-0140 commented

Hi,

Please see if Start-Transcript meets your needs.
https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.host/start-transcript

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, I tried that, but it seems it does not log Invoke-Sqlcmd command. how to log Invoke-Sqlcmd as well? thanks

0 Votes 0 ·

The transcript records all commands you type and all output on the console. Did you run the command from a ps1 file? If so you may have to create a log file yourself, like

 Invoke-Sqlcmd -Query $SqlQuery -ServerInstance $SourceServer | Export-Csv -Path "$destOutputPath\myfile.dat" -NoTypeInformation -Encoding UTF8
 "Invoke-Sqlcmd" | Out-File C:\temp\log.txt




0 Votes 0 ·

yes, all these are in a ps1 script file.
I wrote something like below:
start-transcript -path "$logFilePath\$logFile" -force

write-host "$(get-date -f G) - Executing transcript on $ScriptServer"

$SqlQuery = "SELEC..."

run query on remote server

write-host "$(get-date -f G) - Invoke-SQLCMD to execute SQL on $SourceServer"

Invoke-Sqlcmd -Query $SqlQuery -ServerInstance $SourceServer |

Export-Csv -Path "$destOutputPath\$OutputFile" -NoTypeInformation -Encoding UTF8

ConvertTo-Csv -NoTypeInformation | foreach-object {$_.Replace('"','')} | Out-File "$destOutputPath\$OutputFile"
write-host "$(get-date -f G) - Finished executing transcript on $ScriptServer"

stop log

stop-transcript

done.


I don't see the invoke-sql cmd that records that it connects sql server information, probably the command does not show any of that information

0 Votes 0 ·
Show more comments
IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered Anne-0140 commented

Hi,

The start-transcript only records the commands you typed. Try adding "Set-PSDebug -Trace 1" in your script instead. The output can be redirected to a file like this

 Start-Process powershell.exe -ArgumentList C:\test\sql.ps1 -RedirectStandardOutput C:\test\sql.log

Best Regards,
Ian Xue
============================================
If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you!
So does that mean I need another powershell script that has start-process to call my original powershell script?
I already had a .bat file to call my original script: and that is required by a job system.
in the .bat file I have:
Powershell.exe -c d:\PowershellFiles\spMy_Extract.ps1 -SourceServer myserverA -DestinationServer My serverB ScriptServer MyserverC
exit /b %errorlevel%

So not sure that is a good approach that I need t add another powershell script in between them.

0 Votes 0 ·