Unable to extract CSV from SSMS using SQLPS or PowerShell in T-SQL script with xp_cmdshell

Tom Kelley 6 Reputation points

I had originally created a set of SQL scripts to extract CSV data w/ headers via a system of dynamic queries using SQLCMD. However, due to large text fields in the datasets the formatting codes made the CSV files unwieldly. I then switched to using SQLPS or PowerShell, which did a great job of formatting the output files. Unfortunately, I am unable to get my extract CSV files to save when running the T-SQL file manually from SSMS. Once this is working, I will also need to create a job for running this on an automated basis for some of our implementations, but at least need to be able to run manually. I can paste the same output code from the SSMS 'Messages' tab into the SQLPS command window, and it works fine using PowerShell.exe or SQLPS.exe creating the CSV's. However, the following code will not create the CSV files when run directly from within SSMS:

SET @Cmd = 'sqlps 
     $FromDate = "' + @FromDate + '";
     $ToDate = "' + @ToDate + '";
     $IncidentTypeName = "' + @IncidentTypeName + '";
     $DateField = "' + @DateField + '";
     $QueryType = "' + @QueryType + '";
     $PersonTypeID = "' + @PersonTypeID + '";
     $var = "FromDate=$FromDate", "ToDate=$ToDate", "IncidentTypeName=$IncidentTypeName", "DateField=$DateField", "QueryType=$QueryType", "PersonTypeID=$PersonTypeID";

invoke-sqlcmd -ServerInstance ' +@Server+ ' -Database ' +@Database+ ' -Username '+@Login+' -Password '+@Password + ' -QueryTimeout 0 ' +
    ' -InputFile "'+@SqlScriptFolder+@InputFileType+'" -Variable $var | export-csv -Delimiter "," -NoTypeInformation -Path "'+@ExtractFolder+@OutputFile+'.csv"'

print @Cmd
EXEC master..xp_cmdshell @Cmd

I checked file permissions on the extract folder, tried different options for SQLPS and PowerShell, and reviewed the execution in Process Monitor, but I cannot figure out why this will not create the CSV from within SSMS. There are no error messages. As much as I would like to switch completely to PowerShell, I am trying to leverage the large amount of T-SQL code I put together earlier in the project when when exporting to CSV using SQLCMD.

Any ideas much appreciated,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,583 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,456 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Kelley 6 Reputation points

    Well, after much research into my crazy process, which I should definitely rewrite to be a PowerShell script (and not use xp_cmdshell), I found the solution, which is to

    1) escape the " (double-quotes) with a \ (backslash)
    2) replace the carriage returns and newlines using SET @Cmd = REPLACE(REPLACE(@Cmd, NCHAR(13), N''), NCHAR(10), N' ');

    from invoke-sqlcmd-doesnt-work-when-used-with-xp-cmdshell

    1 person found this answer helpful.
    0 comments No comments

  2. Naomi 7,361 Reputation points

    Did you try capturing the output of the command into a text file and verified the result of the execution? Also, are you sure that running xp_cmdshell is even allowed?


  3. Erland Sommarskog 92,961 Reputation points

    Why involve xp_cmdshell at all? Why not run the powershell script directly from a command-line window? And once you get it going through a Powershell job step?