Powershell Invoke-Expression with SqlCmd

Question

When using Invokve Expression sqlcmd -i script.sql it seems to only display the first 2 lines returned.

When I run in Query Analyser the script it returns:

(2 rows affected) Changed database context to 'ShopStore'. (5 rows affected) Changed database context to 'ShopStore'.

However from powershell I only get:

(2 rows affected) Changed database context to 'ShopStore'.

YET, the whole thing is being run, I can see this by playing with results. Why is this?

Answer 1

From the docs on sqlcmd.exe:

SQL Server Management Studio uses the Microsoft.NET FrameworkSqlClient for execution in regular and SQLCMD mode in Query Editor. When sqlcmd is run from the command line, sqlcmd uses the OLE DB provider. Because different default options may apply, you might see different behavior when you execute the same query in SQL Server Management Studio in SQLCMD Mode and in the sqlcmd utility.

Could this be why you're seeing different results??