SQL Schema

[Merovingian]

Been trying to run a schema script from within vb.net. No matter how I format it I can't I just get all kinds of syntax errors:

Here is my VB script:

Dim sqlservername As String
        sqlservername = Tservername.Text
        Dim SQLStr As String
        Dim ConnString As String
        ConnString = "Data Source=" + System.Environment.MachineName + "\" + sqlservername + ";Initial Catalog=RMain;User ID=sa;Password=min"

        SQLStr = "USE RMain;" & Environment.NewLine

        'Pulls text out of dbsetup.sql 
        Dim origsql_NAME As String = System.Environment.CurrentDirectory + "\setupscripts\schema.sql"
        Try
            Dim objReader As New System.IO.StreamReader(origsql_NAME)
            SQLStr = My.Computer.FileSystem.ReadAllText(origsql_NAME)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "File Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        Dim SQLConn As New SqlConnection() 'The SQL Connection
        Dim SQLCmd As New SqlCommand() 'The SQL Command

        SQLConn.ConnectionString = ConnString 'Set the Connection String
        SQLConn.Open() 'Open the connection

        SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
        SQLCmd.CommandText = SQLStr 'Sets the SQL String
        Try
            SQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        SQLConn.Close() 'Close the connection

    End Sub

Here is a sample of my schema, it's pretty big so just a sample:

BEGIN
CREATE TABLE [dbo].[Patient Reminder](
	[Patient Number] [int] NOT NULL,
	[Reminder Code] [int] NOT NULL,
	[Reminder Next] [datetime] NOT NULL,
	[Visit Number] [int] NULL,
	[Reminder Item] [int] NULL,
	[Reminder Sent] [tinyint] NOT NULL,
	[Created User] [nvarchar](3) NOT NULL,
	[Created Date] [datetime] NOT NULL,
	[Modified User] [nvarchar](3) NOT NULL,
	[Modified Date] [datetime] NOT NULL,
	[Reminder Notes] [nvarchar](max) NULL,
	[Line Code] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Patient Reminder] PRIMARY KEY CLUSTERED 
(
	[Patient Number] ASC,
	[Reminder Code] ASC,
	[Reminder Next] 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].[Message Template]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Message Template](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Template] [nvarchar](max) NULL,
 CONSTRAINT [PrimaryKey_MessageTemplate] PRIMARY KEY CLUSTERED 
(
	[ID] 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].[List - Minimum]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[List - Minimum](
	[Minimum Name] [nvarchar](30) NOT NULL,
	[Minimum Amount] [money] NULL,
 CONSTRAINT [PK_List - Minimum] PRIMARY KEY CLUSTERED 
(
	[Minimum Name] 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

I've tried running it using a .bat and a shell call but that doesnt work either. I'm using SQL2008 and Visual Studio 2008.
Thanks...

[Merovingian]

Fixed this myself. Heres how.

First you'll need to create a schema.bat file with the following:

sqlcmd.exec -S \SKULLCAPS -U SA -P "1234" -d DBNAME -i schema.sq

remember to change 'SKULLCAPS' to the name of your server, sa to the login name your using, 1234 to the logins password, DBNAME to the database name and schema.sql to the name of the schema file.

Secondly you'll need to create two sub's.

Public Function ExecuteDOSCmd2(ByVal exe As String, _
Optional ByVal args As String = "") As String

' The string to return to the caller returned from the executed program
Dim retStr As String = ""
' The process that will that will start when the start command is executed
Dim p As New Process()
' The program to be started
p.StartInfo.FileName = exe
' Any arguments that the program needs
If args <> "" Then p.StartInfo.Arguments = args
' Do not use the system shell to start the program this is so we can
' hide the command dos window
p.StartInfo.UseShellExecute = False
' So we can get the data output from the program we are calling
p.StartInfo.RedirectStandardOutput = True
' Show no dos window
p.StartInfo.CreateNoWindow = True
' Start the program
p.Start()
' Read the output from the program we started
retStr = p.StandardOutput.ReadToEnd()
' Wait until the program to exit.
p.WaitForExit()
Return retStr

End Function

this allows you to pass the name of the file to execute and parameters.

Public Sub schema()

Dim sqlexe As String = System.Environment.CurrentDirectory + "\setupscripts\schema.bat"
Dim installresults As String
Try
installresults = ExecuteDOSCmd2(sqlexe, )
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

End Sub

this tells ExecuteDOSCmd2 to execute the .bat file.

Now simply call schema() to execute.

REALLY IMPORTANT the schema file must be in the same directory as your main program.

If this is not clear in anyway send me a message and I'll do my best to help you out.