How to use Invoke-Sqlcmd from inside SSMS?

Question

I run the query below (from inside powershell), from different servers and all is fine:

Invoke-Sqlcmd -Query "
SELECT [ServerName]=@@servername,m.Match_id, m.HostfamilyId, hf.NIEFlag, ra.DS2019Sent 
FROM APIA_Repl_pub.dbo.repl_HostFamily hf 
INNER JOIN APIA_Repl_pub.dbo.repl_Match m ON m.HostfamilyId =  hf.HostFamilyID
INNER JOIN APIA_Repl_Pub.dbo.repl_Aupair ra on ra.AuPairID = m.AupairId
WHERE ra.JunoCore_applicationID = 459630
" -ServerInstance "CTSTGDB"

I even sometimes run the same query into several servers to compare them, see the result below as an example:

I just want to run the same query from inside SSMS, but see what I do and what I get:

(I have even simplified the query but still I get the error below)

 GO
 declare @sql varchar(8000)
  SET @SQL=N'powershell.exe -command Invoke-Sqlcmd -Query "SELECT [ServerName]=@@servername" -ServerInstance "CTSTGDB"'

IF OBJECT_ID('tempdb..#Radhe','U') IS NOT NULL
   DROP TABLE #RADHE

CREATE TABLE #RADHE(I INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, OUTPUT NVARCHAR(4000))

INSERT INTO #RADHE
 EXEC xp_cmdshell @sql

 SELECT * FROM #RADHE

 GO

OUTPUT
Invoke-Sqlcmd : A positional parameter cannot be found that accepts argument 
'[ServerName]=@@servername'.
At line:1 char:1
+ Invoke-Sqlcmd -Query SELECT [ServerName]=@@servername -ServerInstance ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Invoke-Sqlcmd], ParameterB 
   indingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.SqlServer. 
   Management.PowerShell.GetScriptCommand

 
NULL

I found out an interesting article:

6 methods to write PowerShell output to a SQL Server table

It is related or could be an alternative to the way I am working.

what exactly is the problem of Invoke-Sqlcmd from inside SSMS?

`Invoke-Sqlcmd` is a Powershell command, you don't use it in SSMS; that's an IDE for SQL Server. You would use an IDE for powershell, or just the CLI. — Thom A, Oct 06 '20 at 20:06
SSMS is where you execute SQL queries. Why are you trying to execute powershell scripts inside of SSMS? That makes zero sense. Just use the query itself. You're already in a SQL query execution environment. Delete the whole 'powershell.exe -command Invoke-Sqlcmd -Query "' prefix, and that closing quote, and drop the EXEC xp_cmdshell \@sql. Don't assign it to \@sql either. Just execute the query. Directly. That's what SSMS is for. — pmbAustin, Oct 06 '20 at 21:37
Maybe a more direct question: why are you trying to invoke SQLCMD from within SSMS, when SQLCMD is just the command line version of the SSMS GUI? It's beyond redundant. — pmbAustin, Oct 06 '20 at 21:46

score 0 · Accepted Answer · answered Oct 07 '20 at 06:40

As I understand, you struggle with executing a query against a different server than the one the query is connected to. Well, I can think of at least 2 ways of doing this in SSMS:

If your current server has a linked server for the one you are after, you can use the AT clause of the EXEC statement, like this:

declare @Sql nvarchar(max) = N'SELECT [ServerName]=@@servername,m.Match_id,
  m.HostfamilyId, hf.NIEFlag, ra.DS2019Sent 
FROM APIA_Repl_pub.dbo.repl_HostFamily hf 
  INNER JOIN APIA_Repl_pub.dbo.repl_Match m ON m.HostfamilyId =  hf.HostFamilyID
  INNER JOIN APIA_Repl_Pub.dbo.repl_Aupair ra on ra.AuPairID = m.AupairId
WHERE ra.JunoCore_applicationID = 459630';

-- This example assumes that the linked server name is the same as the remote server itself
exec (@Sql) at [CTSTGDB];

If linked server is not available, you may utilise the SQLCMD mode for the query:

:connect CTSTGDB
go

SELECT [ServerName]=@@servername,m.Match_id, m.HostfamilyId, hf.NIEFlag, ra.DS2019Sent 
FROM APIA_Repl_pub.dbo.repl_HostFamily hf 
INNER JOIN APIA_Repl_pub.dbo.repl_Match m ON m.HostfamilyId =  hf.HostFamilyID
INNER JOIN APIA_Repl_Pub.dbo.repl_Aupair ra on ra.AuPairID = m.AupairId
WHERE ra.JunoCore_applicationID = 459630;
go

:exit
go

This mode can be toggled by the Query -> SQLCMD Mode menu option.

How to use Invoke-Sqlcmd from inside SSMS?

1 Answers1