Script from SQL server database project

The script only creates a database and one table. If you want more tables you got to add the code to create them. Also in order to run in MS MS you will need to remove irrelevant code. What is that you are trying to accomplish?

Have  you  seen  my attached  script file  which  is generated  by  Visual Studio SQL server database  project. In  that  script  you  can see  Creation  of  Table1  which is  only  thing  I  have  created  and  I  want  to be  in  database  created  by  script. But  that  script  does  not  run  in  management  studio.

try this (I just removed some VS added code):

/*
Deployment script for Datadeploy

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/


USE [master];


GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET HONOR_BROKER_PRIORITY OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
USE [$(DatabaseName)];


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO
PRINT N'Creating [dbo].[Table1]...';


GO
CREATE TABLE [dbo].[Table1] (
    [Id]        INT        NOT NULL,
    [namefirst] NCHAR (10) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
    AND (((@@microsoftversion / power(2, 24) = 9)
          AND (@@microsoftversion & 0xffff >= 3024))
         OR ((@@microsoftversion / power(2, 24) = 10)
             AND (@@microsoftversion & 0xffff >= 1600))))
    SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
    BEGIN
        EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
    END


GO
PRINT N'Update complete.'
GO

Select allOpen in new window

The deployment scripts use a lot of variables, do you have SQLCMD mode set to true?
If so, why is it not running, and what's the message?

Where  I  have to set  SQLCMD  mode,in SQL  server management  studio  or  Visual Studios SQL server database  project?

For  YZlat

At  present  I  dont  want  just  script  of  one  table.For  bigger  project  which I  am  likely  to start
I  want  to  learn  how  to use  SQL server database  project?  and  if the  scripts generated  by  this  type  of  project  are usefull.

Ok I  will  try the same

Yah  It worked  perfectly,but will  you please  able to tell,why  it is  so.We  run  script  from C# . In that  case  what  we  have  to  do?
If  database  is  non exist  we  run  script  to  create database,if it is  exist  we upgrade  it  upto current   version.
So main  thing is  that  now  I  want to  use  sql server database  project  to create  script.What is  this SQLCMD mode  and  what  I  have to  do  when I  want  to  create  database  through  code.

Unfortunately, you can't enable sqlcmd in .net, it's a management tool.  You'd have to save the query somewhere and run it using sqlcmd.exe or something along those lines, but all of that is probably not necessary.

The main point of sqlcmd in the deployment scripts is for the database name.  When you first run the publish, and it asks for the database name, that gets set in a variable, which is used during all of the creation.

But with a bit of manual modification, you can easily remove most of the variables and run it normally.
If you're generating an alteration script, the only instances are generally "use" statements, which you can just replace with the actual database name you're targeting.

If it's a creation script, several variables are being used, but if you look at the header code, almost all of it is "typically" not necessary.  As long as you have proper permissions to create the db.  All of the exists checks, and permission checks, file paths, options etc... if you REMOVE, and simply call "create database xxx", it will use default file paths and options according to the server.

I  did  not understood  really  what  you  said.
I  dont  have  much experience  of  deploying  SQL  database  much  I  have  done  it  only  two  projects.
What  we  do  is  create four  scripts  ,One  for  creation of  database.Second  is  creation  of  tables.
Third is  for inserting  in  tables.Fourth  is  upgrade  script  which we  provide  with  every  version.
These  scripts  create  database  if  not  exist  and  update  database.We  are  only  three  people  team.
Anyone  does  database  change  extends upgrade  script  and commits using  svn. And  this  script  we  keep  along exe  while  creating  setup.
Now  I  came  across  SQL  server  Database  project.How  it  can  fit  in  our  scene ? If  we  have  to  include  this  type  of  project  in  our  solution? Will  this  type  of  project  will  reduce  our  work  and  efforts.We  are  in  product  base  company,where  biomedical  instruments  operate  through software  we  write.

Okay, well what's the scenario?  Is this an application that's distributed to many different clients, each with their own version of the database?
The great thing about the SQL projects, is if YOU have control over everything, the deployment handles scripting whatever is necessary to make changes between target and source.
So keeping track of upgrade scripts is completely unnecessary, as the DB deployment does a live comparison of source and target, and generates the scripts it has to by itself to update.

But that's based on being ABLE to do the live comparison, against a current database.
If this is intended to be a distributed app, then you need to know which version of which database any client has at any given time, which is a different story.

In short, despite how much I love the SQL projects, if this is intended for a distributed app, then it's most likely not what you need.

Yes  it  is  distributed application with each client having different database.Basically I  am  in  company which manufacture bipomedical  instruments,and I am programmer who develope  application software for the instrument