PowerShell Development: Querying SQL Server

A quick code walk through of a custom function that executes queries against a SQL Server database. Includes coverage of key concepts.
Code available at:
github.com/bca...

[@BryanCafferky]

Code at: github.com/bcafferky/shared/tree/master/PowerShellSQL

[@gregherbert8878]

very helpful, cheers Bryan!

[@TarekFaham]

That was fantastic... How can you run multiple sql statements in multiple script files using invoke sql command in powershell?

[@BryanCafferky]

Thanks. You can run a script file using the -InputFile parameter, i.e. Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" See docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps For multiple files, you could loop over the file list and call Invoke-SsqlCmd

[@sumitshanker2829]

Could please come up with a course on using azure api using powershell.

[@alex17texas]

Curious to know the benefits or draw backs of using the SQLPS module vs SQL Native client, Also if you schedule a powershell job using SQL agent, what version of powershell is sql agent using? Can you change it? Am i left to invoking powershell and passing the .ps1 file on the job step if I want to use certain cmdlets? If I loaded a function in memory would I be able to use that function in the job step and have sql agent call it given it as permission to perform said task?

[@BryanCafferky]

Doh! Sorry but missed this until now. Not sure about the benefits of going native client but I like knowing exactly how PowerShell will connect and what it will do, i.e. not generate extra calls, or open too many threads, etc. Also, sqlps may add more than you need. The PowerShell version is determined by the SQL Server version/SQL Agent you are running. See this blog for more information. You should be able to point to a different PowerShell version by launching it from a different folder. www.sqlhammer.com/running-powershell-in-a-sql-agent-job/ A function loaded into memory by the script being executed by SQLAgent should be callable.

[@michaeldobala220]

Very useful demonstration. Can you also demonstrante an ODBC select?

[@BryanCafferky]

Hmmm... I think I did a video showing that but need to check. Thanks.

[@DDavis0226]

Really helpful. Thank you!

[@tanmaysathaye5195]

Can you provide the link for the code please?

[@BryanCafferky]

Sure. I just added it to the video description too. Thanks for watching. github.com/bcafferky/shared/tree/master/PowerShellSQL

[@philipadam8023]

Very nice video. Thanks. If anyone wants the link to the file : github.com/bcafferky/shared/blob/653e8cf4d4d0aa92972d37567af2f38c9cc463d0/PowerShellSQL/Invoke-udfSQLQuery.ps1