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"'
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,