PowerShell –SQL Automate Tutorial-10

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

InterServer Web Hosting and VPS

Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its today need to learn at least basic of some PS cmdlets.

In this tutorial, we will walk you through most IMP cmdlets Invoke-Sqlcmd to run/execute SQL command using PowerShell.

Note:- Please click here to get 9th part of PowerShell –SQL Automate tutorial.

To get more information about PowerShell cmdlets, you can refer this link.


Installing SQL server module first on server:-

Here, we want to manage/administer SQL server using PowerShell hence we will have to first make sure that SQL server module should be installed on respective server through which you are trying to connect or run PS scripts.

Please refer this link for the same–> you will need administrator permission to install the module.

Invoke-Sqlcmd

Run a script containing statements supported by the SQL Server SQLCMD utility. .

Syntax
Invoke-Sqlcmd [-ServerInstance PSObject] [-Database String] [-EncryptConnection]
[-Username String] [-Password String] [[-Query] String]
[-QueryTimeout Int32] [-ConnectionTimeout Int32] [-ErrorLevel Int32]
[-SeverityLevel Int32] [-MaxCharLength Int32] [-MaxBinaryLength Int32]
[-AbortOnError] [-DedicatedAdministratorConnection] [-DisableVariables]
[-DisableCommands] [-HostName String] [-NewPassword String]
[-Variable String[]] [-InputFile String] [-OutputSqlErrors Boolean]
[-IncludeSqlUserErrors] [-SuppressProviderContextWarning] [-IgnoreProviderContext]
[-OutputAs OutputType] [CommonParameters]

Invoke-Sqlcmd [[-Query] String] [-QueryTimeout Int32] [-ErrorLevel Int32]
[-SeverityLevel Int32] [-MaxCharLength Int32] [-MaxBinaryLength Int32]
[-AbortOnError] [-DisableVariables] [-DisableCommands] [-Variable String[]]
[-InputFile String] [-OutputSqlErrors Boolean] [-IncludeSqlUserErrors]
[-OutputAs OutputType] -ConnectionString String [CommonParameters]

Example:

Run a script and send the output to a file:

PS C:\> Invoke-Sqlcmd -InputFile “C:\test\test.sql” -ServerInstance “SQLserver\Instance”| Out-File -FilePath “C:\test\outputlist.txt”


Education is the best friend. An educated person is respected everywhere. Education beats the beauty and the youth

Chanakya

One thought on “PowerShell –SQL Automate Tutorial-10

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s