Run batch SQL Scripts in one VB Command

Question

I am trying to make a program so I can run batch SQL scripts without installing SSMS.

I have made a basic program which works for single queries.

 If MsgBox("Are you sure you want to execute this code?", MsgBoxStyle.YesNoCancel, "Are you sure?") = MsgBoxResult.Yes Then


            Dim cmd As New SqlClient.SqlCommand()
            Dim connetionString As String
            Dim conn As SqlConnection
            Dim sql As String


            connetionString = "Data Source=Localhost\database;Initial Catalog=master;Integrated Security=True"
            conn = New SqlConnection(connetionString)
            sql = RTB_Sql.Text


            Try
                conn.Open()
                cmd.CommandText = sql
                cmd.Connection = conn
                cmd.ExecuteNonQuery()  'execute query
                'AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)


            Catch sqlEx As SqlException
                MsgBox(sqlEx.Message)
                conn.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                conn.Close()
            End Try
        Else
        End If

rtb_sql.text is a textbox with the users SQL

I would like this program to run batch statements using Variables, Joins, Updates and If statements however it doesn't cope well with it, and eventually errors.

The codes are sound in SSMS, but not in this program. Is it possible to declare the sql as something else maybe?

Thanks

Can anyone suggest a solution?

Thanks!

@variablename already exists despite a 'begin' and 'end' present in the code. — SamCramphorn
– SamCramphorn, Commented Feb 4, 2016 at 9:12

Tab Alleman · Accepted Answer · 2016-02-03 15:32:52Z

1

The biggest difference between SSMS and your application is that SSMS can parse SQL statements in a batch, and your application will send all statements as a single command.

So when entering multiple statements in your application, it is absolutely necessary that each statement in a batch be terminated with a semi-colon.

For instance, this input will work in SSMS but probably cause an error in your application:

DECLARE @id int
INSERT INTO MyTable (MyColumn) VALUES ('x')
SET @id = @@IDENTITY
SELECT @id

But this code will work in both:

DECLARE @id int;
INSERT INTO MyTable (MyColumn) VALUES ('x');
SET @id = @@IDENTITY;
SELECT @id;

There is no easy way to code your way around this. This will be a matter of user-training.

answered Feb 3, 2016 at 15:32

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rumbleweed Over a year ago

What about embedded GOs? Not sure that they can be handled without special treatment.

Tab Alleman Over a year ago

Off the top of my head I can't remember. Try them with and without semi-colons and see what happens.

SamCramphorn Over a year ago

@TabAlleman - both of these methods worked, however I will use semi-colons going forward to see if this clears the issue up.

Collectives™ on Stack Overflow

Run batch SQL Scripts in one VB Command

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related