SQLCMD command not supported

Question

Hi,

in our Data Tier Application we use several scripts in post deployment.
Depending on some values in a table we import scripts:

IF (@x = 1)
BEGIN
 :r \myscript.sql
END

To separate the log from the main deployment script we instruct SQLCMD toredirect the output from that script.

:out myscript.log
:error myscript.log

UPDATE mytable...

GO
out: stdout
:error stdout

This works if we deploy from Visual Studio to a deployment file (not to a database) and then deploy this file with vsdbcmd to a database.
But when we try to deploy directly from Visual Studio to a database it fails with the message

SQL Execution error: Scripting warning. Command Out is not supported.

Curiously other SQLCMD statements like :r are supported. Can anybody help?

---

Regards, Matze

Answer 1

Hi Matze, how do you deploy data-tier application to a deployment file in VS? I can only deploy it to a database.

Answer 2

Hi Tester,

in the project properties under "deploy" chose the configuration and leave the "Target connection" blank.
That creates a full deployment file. With vsdbcmd you can then deploy this file against a database.

HTH.

---

Regards, Matze

Answer 3

Hi Matze,

I have the same question as Tester321123's.On the Deploy page of DAC application's Properties page, I can't find the "Target Connection" field, instead I find Destination connection string. See:

However, I can't deploy it because the connection string can't be empty.

So I would like to know do you add the script in the DAC project or in the database project?

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi,

you seem to be right. Obviously I do not have a Data-Tier Application. Sadly you cannot see the project type in project properties like in a C# Class Library.
Maybe it's a SQL Server Database Project. Here's a screenshot of project properties deploy tab.

---

Regards, Matze

Answer 5

Hi Matze,

Based on the screenshot you offered above, I think you should work with the database project. And I did a test on my project, I can have the database project deployed successfully if I put the :r, :out and :error statements in the pre/post deploy scripts.

I noticed that the above script you offered above is
:out myscript.log
:error myscript.log
UPDATE mytable...
GO
out:stdout -- my comment: you put the colon after out.
:error stdout

So I think you need to modify your script to be:
:out myscript.log
:error myscript.log
UPDATE mytable...
GO
:out stdout -- you need to put the colon before out.
:error stdout

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Hi Vicky,

thank you for the answer. The colon was mistyped in my post. In the script it is preceeding the statement. I think otherwise we were not able to deploy the generated script via vsdbcmd.
I wonder why you can deploy this while we can't. Just to be sure - in Visual Studio Solution Explorer you right click the project and hit "deploy". Then the project is deployed directly to a database?
I will set up a little test project and test this to avoid side effects.

---

Regards, Matze

Answer 7

Hi,

ok, as pronounced in my previous post, I created the simplest possible database project ;).
It contains one stored procedure and a post deployment script.
The post deployment script is imported in the Script.PostDeployment.sql file via

:r .\script1.sql

It contains the following statements

use [sdw_nrw]
GO

:out myfile.log
:error myfile.log

PRINT 'HELLO'

:out stdout
:error stdout

I configure the project to "Create a deployment script (.sql) and deploy to database".
The database exists and contains a large schema. I choose to not remove artifacts that exist in db and not in project.
Whe I deploy I still get the error

Command Out is not supported.

Script generation succeeds - depployment fails.

---

Regards, Matze

Answer 8

Hi Matze,

Thanks for your response. I am sorry that I have to say "YES", I can reproduce your issue on my machine. And firstly, for one thing you need to be aware is that it is only support SqlCmd syntax in the root Pre/Post deployment scripts. Put the SqlCmd syntax in any files that are included by the root Pre/Post deployment scripts is not supported. See: http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/d87ec7f6-76d5-402d-a130-5ff7cc254bc8/

However, even through I put the :out and :error commands directly in the Pre/Post deployment scripts, I still can reproduce it. I am sorry that I can't figure out the root cause of it, and I am trying to involve someone familiar with this topic to further look at this issue.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 9

Hi Matze,

We can reproduce the behavior and warning where you can’t use :out and :error from a dac project’s post-deployement script.
Currently we don’t have any documentation on why it’s not supported.  We have alerted the product team to investigate this further. You might want to consider submitting an issue at our http://connect.microsoft.com site.

If you want to get full sqlcmd.exe syntax including :error or :out, you can use Visual Studio’s Post-Build event.  In that case, the post-build event can launch sqlcmd.exe directly to execute the input file which can have :error and :out defined.

Thanks,

Cathy Miller