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 https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=482833 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.
Allen