THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

A Couple of Invoke-SQLCMD Issues

In working on a PowerShell script to load data into SQL Server, I decided to use the Invoke-SQLCMD cmdlet included in the snapins provided with SQL Server 2008, and found a couple of issues you should know about.

First, as a matter of best practices, if a stored procedure returns a scalar value, it should do so in an output parameter. The problem is that if the execute stored procedure statement is the only query sent to Invoke-SQLCMD, there is currently no mechanism to specify output parameters as such. The workaround, as specified by Mingwu Zhang on the SQL Dev team, is to declare a Transact-SQL variable to contain the output parameter, use that variable when calling the stored procedure, then select from that variable, all in a single "query" you send to Invoke-SQLCMD. Here's an example:

PS> $result = Invoke-SQLCMD -ServerInstance "SQLTBWS\INST01" -Database "ServerInventory" -query
"declare @comp_id int; exec insComputerSystem @comp_id OUTPUT, @name="SQLTBWS"; select @comp_id as comp_id"
PS> $computer_id = $result.comp_id

The object returned by the cmdlet here is a DataTable object, and by aliasing the variable as I did in the final select statement, the value supplied to the variable can be returned to the PowerShell script.

The second issue is actually a bug I found when testing my script. I've filed a Connect item at for the bug. It identifies two related issues.

One is that once you connect to a database with Invoke-SQLCMD the connection remains after the cmdlet is finished. That means you can't drop the database, for example, without forcing existing connections to be closed. That's just an annoyance.

The bigger problem (and one you're probably not likely to encounter) is that if you do force an existing connection to be closed and drop the database, then recreate the same database and objects so you can re-run the same query, you then receive a transport-level error from the cmdlet because it won't reestablish the connection.

For the most part you won't run into either of these issues, but it's important to know they're there.


Published Friday, August 14, 2009 1:33 PM by AllenMWhite



Chad Miller said:

One other odd thing I've noticed with Invoke-SqlCmd, when called from certain paths in the SQL Server 2008 provider you'll receive the following error:

Invoke-Sqlcmd : Ambiguous match found.

At line:1 char:14

For example change directories to

cd 'SQLSERVER:\SQLRegistration\Database Engine Server Group'

invoke-sqlcmd -Server "Z002\SQLEXPRESS" -Database master -query "select getdate()"

Yet if you're in the parent directory of SQLRegistration it works fine.

August 14, 2009 4:52 PM

Glen said:

Allen--regarding the second problem with Invoke-SqlCmd (the unclosed connection/transport-level error):

Isn't this "normal" for ADO.NET?  I have worked with queries using just .net framework calls and gotten the same behavior.  I think what is happening is that the connection used by Invoke-SqlCmd gets "closed," which means it really goes into the ADO.NET connection pool, to be reused for better performance.  However, killing a connection in the connection pool doesn't remove it from the pool; hence, the next use of that connection gets the transport-level error.  And the error removes the connection from the pool, so you're OK next time.

I played with this extensively some time ago to figure out what was going on, using a test program for the ADO.Net connection (or Invoke-SqlCmd) and Management Studio to view and KILL connections.  Naturally I have none of that code or test procedures now, but you could probably figure it out.

It would be interesting to see what Microsoft plans to do to "fix" it in the next major release...maybe handle dead connections in a connection pool automatically so we don't have to?

March 24, 2010 6:46 PM

Russ bass said:

I have also run into the issue of killing the users in one invoke-sqlcmd session and then executing another only to have it terminate session.

For example, the first does a "alter database testdb set read_only with rollback immediate" using the context of master.

The second does a "select * from tableA" with the context of testdb. This connection fails with a session was forcably terminated. If I do the second with a 3 part identifier in the context of master, it will work.

April 10, 2010 2:48 AM

jamiet said:

Hi Allen,

I'm having a few issues with Sqlcmd myself and seeing as everyone knows you're "the man" for SQL Sever/Powershell I thought I'd come and ask you here. hope you don't mind.

Basically I am calling a .sql script using "Invoke-Sqlcmd -InputFile blah blah" however I want to use the -Variable option to pass in a value to be used in my script. I can't get it working though.

I've got a repro up here: if you feel like taking this on.

Hope you don't mind the direct approach - I'm a bit pressed for time so am out looking for people that might be able to help :)


July 20, 2010 8:24 AM

AllenMWhite said:

Jamie, I figured it out, but 10 minutes after you did.  You'd marked your answer so no need for me to post there.  

July 20, 2010 9:23 AM

Aravind Kumar said:

$a = Invoke-Sqlcmd -ServerInstance "srv_name"-Database master -Query

“select name,state_desc from sys.databases where state<>0”

-ErrorAction silentlyContinue -ErrorVariable err

if($a.count -eq 0)


write-host "offline or suspect databases are ter"


else {write-host "no offline db"}

Here i want to check the count of $a.. If $a has any value (offline databases) it shud prompt "offline or suspect databases are ter" else "no offline db"

But the thing is I am always getting the else option as answer "offline db" .

How do i manipulate the count of the result of invoke sqlcmd (if 2 dbs offline ) shud print IF part..if not then ELSE part.. ???

your help in much appreciated Allen

November 11, 2012 3:32 PM

Greg Low said:

Another issue is that I can't seem to see any way to specify an Application Name for the connection string that's used by Powershell, and it doesn't even set it to "Powershell". It just leaves it as the default .NET client connection string.

Anyone have any clue if there's a way to set it? (for tracing purposes)

March 19, 2015 12:11 AM

ALZDBA said:

@Greg : how about this work around ?

import-module -name SQLPs -DisableNameChecking | out-null



$SQLInstance = 'yourServer\yousInstance'

$ApplicationName = "DBA_Test_ApplicationName";

# Ready to rumble

$SMOConnection = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance ;

#Oh please put some application info in your connection metadata !

$SMOConnection.ConnectionContext.ApplicationName = $ApplicationName ;

$SMOConnection.ConnectionContext.WorkstationId = $env:COMPUTERNAME ;

# connecting should take less than 5 seconds !

$SMOConnection.ConnectionContext.ConnectTimeout = 5

#connect before processing

$SMOConnection.ConnectionContext.Connect() ;

$QueryTemplate = @'

Select *

FROM sys.dm_exec_sessions WITH (NOLOCK)

where [program_name] = '<Application Name>'


$Query = $QueryTemplate.Replace('<Application Name>', $ApplicationName )

$dbsmo = get-sqldatabase -InputObject $SMOConnection -Name msdb ;

$ds = $dbsmo.ExecuteWithResults( $Query ) ;

$ds.tables[0].rows | out-gridview


June 3, 2015 2:20 AM

Yean said:

@ALZDBA's post:

I encountered the similar issue and Alzdba's solution works just fine.  But I have to change $Query = $QueryTemplate.Replace('<Application Name>', $ApplicationName ) to $Query = $QueryTemplate -Replace '<Application Name>', $ApplicationName

July 28, 2015 1:59 PM

muriel greenwood said:

Helpful piece ! I was fascinated by the information ! Does anyone know if my company might be able to get a template Category: Modify PDF document to type on ?

September 12, 2016 1:26 PM

Bailey said:

Hi muriel greenwood . my business partner got ahold of a template a form version at this site

September 13, 2016 9:53 AM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement