When using VS.Net database projects, the tool will gen your scripts needed for deployment.  The tool uses SQL Command scripts.  By default SSMS doesn’t turn this option on.

To successfully run the generated scripts, you must:

SSMS 2008 –> Tools –> Options -> Check “Open in CMD mode”

While adding support for SQLCMD syntax to Query Editor, the development team envisioned two primary scenarios. One scenario is to make Query Editor more powerful by supporting new commands in addition to GO. The other one is to enable writing and debugging SQLCMD scripts in Query Editor so that they can be executed later from batch files with the SQLCMD.EXE command line tool.

Now you can try using this mode to author and execute in Query Editor a simple query that selects all columns from a table whose name is specified by a SQLCMD variable.

Create a new query window and connect to AdventureWorks. Turn on the SQLCMD mode. After that, type the following in the text editor area of the query window:

:setvar tablename HumanResources.Department
go
select * from $(tablename)
go

Please note the same rules apply as if we were working in console mode, that is, the command should be the first statement on the line.

Notice that the line containing the :setvar SQLCMD command is highlighted. All recognized SQLCMD commands are highlighted this way so that it is clear that the command is not part of T-SQL syntax.

Now you can use the power of the commands created from the VS tool.  Try the tool on a few databases – it provides some great examples for using the power of SQL.

TY Sudheer for getting this documented.