WSE 3 Deployment: MSI and ClickOnce

USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{2}')
	DROP DATABASE [{2}]
GO
CREATE DATABASE [{2}]

GO
EXEC dbo.sp_dbcmptlevel @dbname=N'{2}', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [{2}].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [{2}] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [{2}] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [{2}] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [{2}] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [{2}] SET ARITHABORT OFF 
GO
ALTER DATABASE [{2}] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [{2}] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [{2}] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [{2}] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [{2}] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [{2}] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [{2}] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [{2}] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [{2}] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [{2}] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [{2}] SET  ENABLE_BROKER 
GO
ALTER DATABASE [{2}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [{2}] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [{2}] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [{2}] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [{2}] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [{2}] SET  READ_WRITE 
GO
ALTER DATABASE [{2}] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [{2}] SET  MULTI_USER 
GO
ALTER DATABASE [{2}] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [{2}] SET DB_CHAINING OFF 
----------------------------
/* CREATE DBUser		  */
----------------------------
USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'{0}')
	DROP LOGIN [{0}]
	
CREATE LOGIN [{0}] WITH PASSWORD=N'{1}', DEFAULT_DATABASE=[{2}], 
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [{2}]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'{0}')
	CREATE USER [{0}] FOR LOGIN [{0}] WITH DEFAULT_SCHEMA=[dbo]
	
EXEC sp_addrolemember 'db_owner', '{0}'	
GO
/* -----END---- */


USE [{2}]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fancy]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Fancy](
	[FancyId] [int] IDENTITY(1,1) NOT NULL,
	[FancyValue] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Fancy] PRIMARY KEY CLUSTERED 
(
	[FancyId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Plain]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Plain](
	[PlainId] [char](4) NOT NULL,
	[PlainValue] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Plain] PRIMARY KEY CLUSTERED 
(
	[PlainId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FancyGetGreeting]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_FancyGetGreeting]
AS

SELECT TOP 1 FancyValue FROM Fancy
ORDER BY FancyId ASC ' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_PlainGetGreeting]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_PlainGetGreeting]
AS

SELECT PlainValue FROM Plain
WHERE PlainId = ''GRE1''' 
END
----------------------------
/* Insert initial data    */
----------------------------
USE [{2}]
INSERT INTO Plain (PlainId, PlainValue)
(
	SELECT 'GRE1', 'Hello World' UNION
	SELECT 'GRE2', 'Hi World' UNION
	SELECT 'GRE3', 'Hey World'
)

INSERT INTO Fancy (FancyValue)
(
	SELECT 'Greetings to you, World'
)

GO