SQL Server SSDT publish fails thinking a PowerShell command is using a SQLCMD variable

1
votes

My SSDT database publish is failing because it is erroneously thinking an embedded string is referring to a SQLCMD variable:

EXEC xp_cmdshell 'powershell.exe $(Get-Date).IsDaylightSavingTime()'

I can manually run this statement (in SSMS) and get the desired result (which is regrettably "true") but the publish chokes trying to parse the statement:

SQL Execution error: A fatal error occurred. The variable Get-Date could not be found.

SQL Execution error: A fatal error occurred. Incorrect syntax was encountered while EXEC xp_cmdshell 'powershell.exe $(Get-Date).IsDaylightSavingTime()' was being parsed.

How do I get this statement to not fail the publish without obfuscating it or adding it to the SQLCMD variable list in the database project?

(Or perhaps there is an easier way to check if the server's time zone observes godforsaken DST? The time zone identifiers in the USA say "Standard" regardless if actually prevailing on our developer machines. And changing all references from GETDATE() to GETUTCDATE() is not feasible.)

1
sqlpowershellsql-server-data-toolssqlcmd

1 Answers

1
votes

Haven't seen this before but then again I've never tried to call powershell from t-sql :).

The simplest would be to split the string passed to xp_cmdshell using something like this:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a07d229-7972-41d1-bd90-ffa979bc7069/how-to-escape-string-that-sqlcmd-thinks-is-a-variable-but-isnt?forum=transactsql

' + CHAR(36) + '

Re a simpler solution without powershell, Not sure if I could envisage a more painful thing to have to do on t-sql, sorry :)