Click here to Skip to main content
15,667,847 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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()

        'This call is required by the Component Designer.

        'Add initialization code after the call to InitializeComponent

    End Sub

    Private Function GetSql(ByVal Name As String) As String

            ' Gets the current assembly.
            Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()

            ' Resources are named using a fully qualified name.
            Dim strm As Stream = Asm.GetManifestResourceStream( _
              Asm.GetName().Name + "." + Name)

            ' Reads the contents of the embedded file.
            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")
        'Dim Command As New SqlClient.SqlCommand(Sql, masterConnection)
        masterConnection = New SqlConnection(ConnString)
        Dim Command As New SqlClient.SqlCommand(Sql, masterConnection)

        ' Initialize the connection, open it, and set it to the "master" database
        'masterConnection.ConnectionString = My.Settings.MasterConnectionString
            ' Closing the connection should be done in a Finally block
        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 = ".")

            ' Creates the database.
            ExecuteSql("master", "CREATE DATABASE " & strDBName, BuildConnString(DBServer))

            ' Creates the tables.
            ExecuteSql(strDBName, GetSql("sql.txt"), BuildConnString(DBServer))

            'Creates the Stored procedure.
            ExecuteSql(strDBName, GetSql("Sqlproc.txt"), BuildConnString(DBServer))

            'Creates the Stored procedure.
            ExecuteSql(strDBName, GetSql("Sqlproc2.txt"), BuildConnString(DBServer))

        Catch ex As Exception
            ' Reports any errors and abort.
            MsgBox("In exception handler: " & ex.Message)
            Throw ex
        End Try
    End Sub

    Public Overrides Sub Install(ByVal stateSaver As  _

        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 


Create Table [dbo].[Departments](
[Id] [int] identity(1,1) not null,
[Name] [nvarchar] (50),
[Location] [varchar] (50),
    [Id] ASC

m trying this for last three days.

plz. any help appreciated

Thanks In advance
Updated 22-Aug-13 2:11am
Trak4Net 22-Aug-13 17:55pm    
Maybe you should do a check to see if strDBName is empty. It might be possible that the Install method is getting called twice and the Context does not contain the parameters you are looking for.
PRATIK1988 23-Aug-13 2:43am    
@Trak4Net Hi, by reading your comment i changed my code and made the strDBName optional and gave it a default value

Protected Sub AddDBTable(Optional ByVal strDBName = "Test" As String, Optional ByVal DBServer As String = ".")

now even if install method is called twice it should take "Test" as strDBName also i gave default value in the text box of installation but still facing the same problem.
Trak4Net 23-Aug-13 3:50am    
Is the term DATABASE anywhere in your SQL scripts that aren't shown? Either a use statement or anything?
PRATIK1988 23-Aug-13 4:28am    
No just in the program where it is shown
Trak4Net 23-Aug-13 14:13pm    
Do you have any stack trace information? Have you tried executing that same code outside of installer (use a different method than "overrides sub Install") just as a test to see if you get different results?

1 solution

hi guys, i got the solution it was a silly mistake as i was not knowing that custom action data property of custom action can take two parameters values at a time so i had created two custom actions in Install folder, in the first one i was passing database name and in the second one i was passing server name. This is the reason why the install method was been called twice

following are custom actions that i crated in Install folder:

Primary output from DBCustomAction (Active) : Custom Action data= /dbname=[CUSTOMTEXTA1] = TEST

Primary output from DBCustomAction (Active) : Custom Action data= /dbserver=[CUSTOMTEXTA2] = SYS11

the default connection string was = "Data Source=.;Initial Catalog=master;Integrated Security=True"

so while installation when the system encountered first custom action consisting of database name = "TEST", it created the database as the server name was default (Data Source=.)

hence it Created all the tables and stored procedures.

and again when system encountered second custom action consisting of servername = "SYS11" it connected the server but when executing the create database query it did not find the database name hence threw the error incorrect syntax near database.

To avoid this i just deleted one Primary output from DBCustomAction (Active) from install folder and kept another over there and passed both parameters to the same custom action

Primary output from DBCustomAction (Active) : Custom Action data= /dbname=[CUSTOMTEXTA1][single space]/dbserver=[CUSTOMTEXTA2] where CUSTOMTEXTA1 is database name and CUSTOMTEXTA2 is Server name and it worked.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900