hi everyone,
i have create a demo project to install database consisting of tables and stored procedures it is just database installation and does not contain any application to be installed. to create it i referred
Walkthrough: Using a Custom Action to Create a Database at Installation[
^]
the only change i made is that the installer will ask you to enter the server name and database name both.
through some forum i found out the best way to avoid error due go statement and other sql statement is to write queries in different .txt file so now the scenario is
I have all my create table query in one text file and have different text file for each stored procedure as my stored procedures have variable declared of same name which give error that's why different file for stored procedures
now the problem is that at the end of the installation it shows me error "INCORRECT SYNTAX NEAR DATABASE" and the whole installation is reverted but when i check SQL Server the database is installed with the tables and stored procedure sucessfully. The code is as follows:
Imports System.ComponentModel
Imports System.Configuration.Install
Imports System.IO
Imports System.Reflection
Imports System.Data.SqlClient
Public Class VbDeployInstaller
Dim masterConnection As New System.Data.SqlClient.SqlConnection
Public Sub New()
MyBase.New()
InitializeComponent()
End Sub
Private Function GetSql(ByVal Name As String) As String
Try
Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()
Dim strm As Stream = Asm.GetManifestResourceStream( _
Asm.GetName().Name + "." + Name)
Dim reader As StreamReader = New StreamReader(strm)
Return reader.ReadToEnd()
Catch ex As Exception
MsgBox("In GetSQL: " & ex.Message)
Throw ex
End Try
End Function
Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String, Optional ByVal ConnString As String = "Data Source=.;Initial Catalog=master;Integrated Security=True")
masterConnection = New SqlConnection(ConnString)
Dim Command As New SqlClient.SqlCommand(Sql, masterConnection)
Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)
Try
Command.ExecuteNonQuery()
Finally
Command.Connection.Close()
End Try
End Sub
Private Function BuildConnString(ByVal ServerName As String) As String
BuildConnString = Nothing
ServerName = "Data Source=" & ServerName & ";Initial Catalog=master;Integrated Security=True"
Return ServerName
End Function
Protected Sub AddDBTable(ByVal strDBName As String, Optional ByVal DBServer As String = ".")
Try
ExecuteSql("master", "CREATE DATABASE " & strDBName, BuildConnString(DBServer))
ExecuteSql(strDBName, GetSql("sql.txt"), BuildConnString(DBServer))
ExecuteSql(strDBName, GetSql("Sqlproc.txt"), BuildConnString(DBServer))
ExecuteSql(strDBName, GetSql("Sqlproc2.txt"), BuildConnString(DBServer))
Catch ex As Exception
MsgBox("In exception handler: " & ex.Message)
Throw ex
End Try
End Sub
Public Overrides Sub Install(ByVal stateSaver As _
System.Collections.IDictionary)
MyBase.Install(stateSaver)
AddDBTable(Me.Context.Parameters.Item("dbname"), Me.Context.Parameters.Item("dbserver"))
End Sub
End Class
Code for sql.txt
CREATE TABLE [dbo].[Employees] (
[Name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Rsvp] [int] NULL ,
[Requests] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Name]
) ON [PRIMARY];
Create Table [dbo].[Departments](
[Id] [int] identity(1,1) not null,
[Name] [nvarchar] (50),
[Location] [varchar] (50),
CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
m trying this for last three days.
plz. any help appreciated
Thanks In advance
Submit an article or tip