# Invoke-Sqlcmd2

## Modeled after SQL Server 2008 Invoke-Sqlcmd, but fixes bug in QueryTimeout.

4 Star
(14)
Databases
10/5/2010

• Nice function. One caveat to watch out for with Verbose switch
2 Posts | Last post September 11, 2018
• Great little function that is very handy. Thank your for writing this.

There is one caveat I wanted users of this function to be aware of.

It has to do with error propagation when using this function with -Verbose switch. Internally, the function uses InfoMessage event to write out print statements. This has the negative side-effect of suppressing errors and instead sending them to that event as PRINTs. If your functions are acting "funny" when in -verbose mode, now you know the culprit.

This is information from SQL Server BOL (found from another link)

When you set FireInfoMessageEventOnUserErrors to true, errors that were previously treated as exceptions are now handled as InfoMessage events. All events fire immediately and are handled by the event handler. If is FireInfoMessageEventOnUserErrors is set to false, then InfoMessage events are handled at the end of the procedure. 
• I solved that problem by changing the handler to this:

#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose) {$conn.FireInfoMessageEventOnUserErrors=$true$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
if($_.Errors.Number -lt 50000){ Write-Error "$($_)" }else{ Write-Host "$($_)" } }$conn.add_InfoMessage($handler) }  • applicationintent=readonly 2 Posts | Last post January 29, 2016 • I am using Invoke-sqlcmd2 to connect to my databases and retrieve size information which I store in a central management server for capacity forecasting. Recently we added an availability group where the secondary databases are online in read-only mode. I get errors when connecting to the read-only databases: > A job step received an error at line 43 in a PowerShell script. The > corresponding line is > ' foreach-object {'. > Correct the script and reschedule the job. The error information returned > by PowerShell is: > 'Exception calling "Open" with "0" argument(s): > "The target database ('AGTest') is in an availability group and is > currently accessible for connections when the application intent is > set to read only. Is there a way to specify applicationintent=readonly when using invoke-sqlcmd2? Ken  • I brute-forced my version of invoke-sqlcmd2.ps1 with: > if ($Username)
> { $ConnectionString = "Server={0};...;applicationintent=readonly" -f ... } > else > {$ConnectionString = "Server={0};...;applicationintent=readonly" -f ... }

So all my connections are now readonly - which is OK since I'm just gathering stats on all my SQL instances.

Note that there are updated versions of invoke-sqlcmd2.ps1 at http://poshcode.org/4967.  I'll try and find some time to post an updated version there.

Ken
• Why Position for parameters
2 Posts | Last post August 25, 2015
• Why does this function use Position for the parameters.  One of the wonderful things about powershell is the use of named params.  Is it for compatibility with invoke-sqlcmd?

I ask because I modified the script to take PIPED queries but the position parameters makes it difficult to keep the old behavior at the same time.
• When you have a command that you use a lot, it's easier to use if you don't have to type the parameter names over and over ...

Personally, I would argue that you need to be more selective about it (i.e. 3 or 4 positional parameters is fine, but marking them all that way is just silly).
• Any thoughts on the changes on PoshCode.org?
1 Posts | Last post August 25, 2015
• Looks like several people have posted updates to add features or fix things...

http://poshcode.org/4967
• What does this need installed to work?
1 Posts | Last post October 09, 2014
• Hello,

I have a script that is using Invoke-SQLCMD2 but everytime I try to load the function in to my script I receive the following error.

libSQL.ps1 failed to load. Script will exit
.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The s
ystem cannot find the file specified.
At \\PATH\libSQL.ps1:189 char:16
+ catch {add-type <<<<  -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}
+ CategoryInfo          : NotSpecified: (:) [Add-Type], FileNotFoundException
+ FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Command

Management Studio is not installed on the workstation but I have installed the Native Client and Shared Management Objects. What else is required....?

mtwelve
• ok to add ConnectionString input parameter
1 Posts | Last post June 05, 2013
• Hi Chad, thanks again for your code, very helpful.  I like how I can pass in a connectionstring to the Write-DataTable.   Could Invoke-Sqlcmd2 be modified to allow that as well  ?
thanks, Bruce
• If any errors, even if results, we don't get the results.
5 Posts | Last post February 13, 2013
• I've been automating sp_blitz using invoke-sqlcmd2.  I've seen where if there are any errors with the SP, the dataset doesn't get filled, even if results are returned.

Instead, I get this (below).  Is there any way to fix?  While helpful in that I know there's a problem, it causes other problems.  Thanks.

Exception calling "Fill" with "1" argument(s): "String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
String or binary data would be truncated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated."
At C:\sql_tools\invoke-sqlcmd2.ps1:74 char:19
+     [void]$da.fill <<<< ($ds)
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
• Not really an invoke-sqlcmd2 thing. sp_blitz makes use of temp tables and it's trying to stuff a string/binary which is too large into it's temp tables. You should see the same error message in invoke-sqlcmd, sqlcmd, SSMS, etc (as long as ANSI_WARNINGS is one, the default setting. One workaround is to set ANSI_WARNINGS OFF. Alternatively you can try figure out which column definition is to small and adjust the size.
• Chad, you're absolutely correct and we're working on submitting those fixes as well.  Our concern was that while sp_blitz does choke, it still returns a valid result set, and it'd be nice to get it if possible.  Thanks!
• Understood, so as I suggested set ansi_warnings off. This will turn truncation errors into a non-terminating error. You can do this at the top of the procedure OR within your session.
• Doh!  Misunderstood that the first time.  Will do, thanks!
• Example with stored procedures?
2 Posts | Last post October 16, 2012
• Just starting out with powershell and this is very helpful. Is there an example of this executing a stored procedure and returning an output value plus a return code?
• invoke-sqlcmd2 functions the same as invoke-sqlcmd, sqlcmd.exe or osql.exe where output parameters would need to be handled in T-SQL code. You can write your own output parameter code using ADO.NET. Here's a forum post with sample code: http://powershellcommunity.org/Forums/tabid/54/aft/4042/Default.aspx
Another option: SQLPSX has an adolib Powershell module with built-in support for output params and return values for querying SQL.
• -Variable support
2 Posts | Last post May 26, 2012
• Chad, do you have any intentions of adding -Variable support to the script for sqlcmd variables?

Thanks

• I'd say there isn't a need for sqlcmd style variables when you have Powershell variables and the full power of ADO.NET. The awkward sqlcmd variables are pre-Powershell and I think .NET and Powershell offer better alternatives. In Powershell you can define a variable $spid = 1 and then use the variable in$query = "select * from sysprocesses where spid = \$spid". Also ADO.NET method allows you to define parameterized queries. Someone has already adapted invoke-sqlcmd2 to add handle them here http://poshcode.org/2950. One last thing I'd point out the SQLIse module which is part of SQLPSX http://sqlpsx.codeplex.com has soemthing called PoshMode setting which replaces variables embedded in .sql files (see the expand-string function in SQLIse.psm1.
• Saving -Verbose msgs
2 Posts | Last post December 14, 2011
• Using the Function & trying to pipe the -Verbose msgs from my Stored procedure to a file. I need to save them as a log of the run. Is there anyway to do this?  When I try to pipe them, they still only display at the console. Using:

Invoke-Sqlcmd2 -ServerInstance "MY Server" -Database "MyDB" -Query "EXEC My Stored Proc"  -Verbose | Out-file my_SP_run.log
• A couple of options here. You could use the start-transcript cmdlet to capture all input/output for a given Powershell session. Some people will use this for extensive logging.

Second, since invoke-sqlcmd2 is script you can change write-verbose to write-output or whatever you like.
1 - 10 of 12 Items