Tilfried Weissenberger

In a post-deploy script, I want to connect to another server using

:connect $(TargetServer)

which works in the sql management console SQL editor window.

But during build, I get the following error:

Error TSD164: Syntax checking failed : Incorrect syntax near :.




Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

Gert Drapers - MSFT

I think the issue is that $(TargetServer) is an MSBuild property and not a SQLCMD variable. First try using :connect with a literal like:

:connect "(local)\sql90"

In the deploy output you will see

Connecting... Server "(local)\sql90"

Then try making the connection target a SQLCMD variable, you can map the MSBuild property in to a SQLCMD variable inside the XML variables blog, please http://blogs.msdn.com/gertd/archive/2007/01/08/variables-to-the-rescue.aspx and the sample that goes with this http://www.sqldev.net/dbpro/UsingVariables.zip

-GertD





Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

Tilfried Weissenberger

Hi Gert,

Thanks. I am (havily) using variables already, and have registered TargetServer within the dbproj file as a variable. I will try the literal way and let you know if that worked.






Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

Tilfried Weissenberger

sorry, still not working!

Even a simple file with only this in it produces build-errors:

:connect "d03"
GO

Error:

Error TSD164: Syntax checking failed : Incorrect syntax near :.

need urgent work-around!!






Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

J. D. Laflen MSFT

I tried this with our current bits (and to my knowledge, this part has not changed since prior to RTM). In the Script.PostDeployment.sql I have the following code:

:setvar server "SERVER\INSTANCE"

:connect $(server)

USE [Test]

CREATE TABLE T1 (c1 int)

I build fine. When I deploy I see the following:

------ Build started: Project: Database11, Configuration: Default Any CPU ------

------ Deploy started: Project: Database11, Configuration: Default Any CPU ------

Deploying script C:\Users\xxx\Documents\Visual Studio 2005\Projects\Database11\Database11\sql\Database11.sql to server (local)\sqlexpress

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Connecting... Server SERVER\INSTANCE

Disconnection from server SERVER\INSTANCE

C:\Users\xxx\Documents\Visual Studio 2005\Projects\Database11\Database11\sql\Database11.sql --> Server:"(local)\sqlexpress", Database:"Database11"

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Does deploy work if you run it from within the IDE






Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

Tilfried Weissenberger

Correction: I was able to track the compile-problem down to the following fact:

using it DIRECTLY in the post-deploy SQL file, I don't get compile-error. But using it within an included file (:r .\remote-system.sql) causes the compile-errors!!






Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

Tilfried Weissenberger

Also: if I use a variable which is declared in the DBPROJ file, BUILD crashes:

C:\Programme\MSBuild\Microsoft\VisualStudio\v8.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets(49,5): Error MSB4018: The "SqlBuildTask" task failed unexpectedly.
ManagedBatchParser.ParserException
at ManagedBatchParser.Parser.Parse()
at Microsoft.VisualStudio.TeamSystem.Data.Common.ExecutionEngine.BuildFileBatchParser.GetStatementsFromFile(String fileContent)
at Microsoft.VisualStudio.TeamSystem.Data.Schema.SqlBuildTaskHelper.ReadScriptFile(String scriptPath, StringBuilder stringBuilder)
at Microsoft.VisualStudio.TeamSystem.Data.Schema.SqlBuildTaskHelper.GenerateProjectBuildScript()
at Microsoft.VisualStudio.TeamSystem.Data.Tasks.SqlBuildTask.Execute()
at Microsoft.Build.BuildEngine.TaskEngine.ExecuteTask(ExecutionMode howToExecuteTask, Hashtable projectItemsAvailableToTask, BuildPropertyGroup projectPropertiesAvailableToTask, Boolean& taskClassWasFound)






Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

J. D. Laflen MSFT

Okay, I was able to reproduce the less the unhandled exception, I am bugging it. Today, if you want to use a variable defined in the project file that is not declared in the post script itself you need to put [ ¡­ ] around the variable. For instance, this does not work:

:connect $(Server)

:r ModServer.sql

While this does:

:connect [$(Server)]

:r ModServer.sql

And this does:

:setvar Server "SERVER\INSTANCE"

:connect [$(Server)]

:r ModServer.sql

Yes, right now you cannot use setvar statements in files that are included using :r. I will check to see if there is already a bug on this.






Re: Visual Studio Team System - Database Professionals SQLCMD :connect not allowed?

Tilfried Weissenberger

thank you!