0

Im using the following command in SQL Powershell to generate csv of data returned by SQL.

Invoke-Sqlcmd -Query "myp" -ServerInstance "." | Export-Csv -Path "d:\data\MyDatabaseSizes.csv" -NoTypeInformation

The above command works perfectly fine in SQL Power Shell. but when I tried to run in from SQL using the following code

DECLARE @cmd varchar(1000)
SET @cmd = 'Invoke-Sqlcmd -Query "myp" -ServerInstance "." | Export-Csv -Path "d:\data\MyDatabaseSizes.csv" -NoTypeInformation'
EXEC xp_cmdshell @cmd

it give error that

'Invoke-Sqlcmd' is not recognized as an internal or external command,

Anyone please help me in running the command from SQL.

Thanks

Muhammad Taqi
  • 305
  • 2
  • 6
  • 19

1 Answers1

0

xp_cmdmshell executes Windows shell commands, not PowerShell commands. You can of course call PowerShell from within the Windows shell, for example like this:

DECLARE @cmd varchar(1000)
SET @cmd = 'Invoke-Sqlcmd -Query ''myp'' -ServerInstance ''.'' ^| Export-Csv -Path ''d:\data\MyDatabaseSizes.csv'' -NoTypeInformation'
EXEC xp_cmdshell 'powershell.exe -c "' + @cmd + '"'

Note that:

  • I have replaced the double quotes in the PowerShell command with single quotes (escaped for SQL) so I can use double quotes in the Windows command. If you need double quotes in the PowerShell, you're better off having the PowerShell script in a file to avoid all this
  • You may also need more parameters (e.g. to set the execution policy). This MSDN page has more help on PowerShell command line switches
  • Pipe characters in command line arguments need to be escaped with ^
aucuparia
  • 2,021
  • 20
  • 27
  • Hello, When I execute the above Code in SQL it gives error 'Export-Csv' is not recognized as an internal or external command, – Muhammad Taqi Jun 29 '16 at 11:41
  • you need to escape pipes in the command line (if not actually being used as a shell pipe) with a caret. – aucuparia Jun 29 '16 at 16:14
  • Sorry for being such a noob. But when I execute your query I removed | character. And in the 3rd line it gave me error in concatinating. Please help can you please create a working query for me thanks – Muhammad Taqi Jun 29 '16 at 16:57