CODEWITHSUNDEEP
sqlsql-servervisual-studiot-sql
stevebot
stevebot

Reputation: 24005

SQL Server Database Project: Why is command execute not supported

I am trying to copy a file in my Pre Deployment script for a Visual Studio SQL Database project. 

:!!copy $(DataFileDirectory)\data.csv $(DataDestinationDirectory)

I found that this generated the error:

SQL72007: The syntax check failed 'Incorrect syntax near .' in the batch near: ':!!copy

So I tried to simplify my testing and tried:

:!! dir

This also failed:

SQL72007: The syntax check failed 'Incorrect syntax near .' in the batch near: ':!! dir

It fails when I try:

!! dir

I am able to execute commands like:

:r .\myfile.sql

I noticed the following error occurs whenever I do :!!,

72006: Fatal scripting error: Command Execute is not supported.

Why is command execute (:!!) not supported?!

Upvotes: 1

Views: 2604

Answers (1)

Christian.K
Christian.K

Reputation: 49280

This can, of course, only truly be answered by the designers of SSDT, but you can make an educated guess: it would compromise system security too much. It would allow you to run arbitrary commands on the database server's OS, under the account of the user that runs SQL Server itself.

The sqlcmd.exe command allows one to disable the :!! and :ed commands, using the -X command line option, for the same reason.

If you really need to run OS commands in your script, you could still fallback to xp_cmdshell, which of course also has issues, but at least the server's administrator can decide if it should be allowed.

There are a lot more SQLCMD syntax options, that SSDT pre- or post deployment scripts do not support (for example :connect or :on error, it seems). So, another explanation would be, that for the purpose of deployment scripts the :r and :setvar commands were considered sufficient.

Upvotes: 6

Related Questions