469,090 Members | 1,112 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Running SQL scripts within a VB enviroment.

Hi all, I think I have the right group, if I don't then I do
apologize.
My problem is that I'm trying to run a SQL script within a VB
Environment. The actual program is WISE Installer, but I'm sure that
the same problem arise within VB because we have also wrote an app
that is meant to run SQL scripts (But falls over the same). Basically,
there are some SQL keywords that are Query Analyser only.. ie: GO

Does anyone know of a list of Query only statements? The code below
fails, but since it's not as verbose as Query, all I'm getting is
'INCORRECT SYNTAX NEAR THE KEYWORD "PROCEDURE". Here's what's around
it:

^^^^^^^^^^^^^^^^^^ More stuff above
SET ANSI_NULLS OFF

CREATE PROCEDURE ASP_OutstandingStaffingRequirements
@stDate as datetime, @enDate as datetime, @locId as integer,
@gradeCount as Integer
AS

Create Table #glb_outshifts
^^^^^^^^^^^^^^^^^^^ More stuff below.
Any help would be great as I'm banging my head against the wall here.

BTW.. I don't actually know what the SQL script does, I'm just trying
to package together a deployment solution.

Many Many Thanks. Regards Mark (SQL GURU.. LOL)
Jul 20 '05 #1
2 4961
Two things:
- what library are you using to run this? ADO? DMO?
- CREATE PROCEDURE must be the first statement in a batch. You should
set the ANSI_NULLS in the connection string or send it as a separate
call to the exec method of your class before executing the CREATE PROC
statement.
Does anyone know of a list of Query only statements? The code below
fails, but since it's not as verbose as Query, all I'm getting is
'INCORRECT SYNTAX NEAR THE KEYWORD "PROCEDURE". Here's what's around
it:

^^^^^^^^^^^^^^^^^^ More stuff above
SET ANSI_NULLS OFF

CREATE PROCEDURE ASP_OutstandingStaffingRequirements
@stDate as datetime, @enDate as datetime, @locId as integer,
@gradeCount as Integer
AS

Create Table #glb_outshifts
^^^^^^^^^^^^^^^^^^^ More stuff below.

Jul 20 '05 #2
ma**@uunix.com (Mark Davies) wrote in message news:<f8**************************@posting.google. com>...
Hi all, I think I have the right group, if I don't then I do
apologize.
My problem is that I'm trying to run a SQL script within a VB
Environment. The actual program is WISE Installer, but I'm sure that
the same problem arise within VB because we have also wrote an app
that is meant to run SQL scripts (But falls over the same). Basically,
there are some SQL keywords that are Query Analyser only.. ie: GO

Does anyone know of a list of Query only statements? The code below
fails, but since it's not as verbose as Query, all I'm getting is
'INCORRECT SYNTAX NEAR THE KEYWORD "PROCEDURE". Here's what's around
it:

^^^^^^^^^^^^^^^^^^ More stuff above
SET ANSI_NULLS OFF

CREATE PROCEDURE ASP_OutstandingStaffingRequirements
@stDate as datetime, @enDate as datetime, @locId as integer,
@gradeCount as Integer
AS

Create Table #glb_outshifts
^^^^^^^^^^^^^^^^^^^ More stuff below.
Any help would be great as I'm banging my head against the wall here.

BTW.. I don't actually know what the SQL script does, I'm just trying
to package together a deployment solution.

Many Many Thanks. Regards Mark (SQL GURU.. LOL)


The error is probably because CREATE PROC must be the first statement
in a batch, so you need GO after the SET statement above.

A simple approach for multi-batch scripts is to use OSQL.EXE, which
will run the complete input file, even if it includes batch
delimiters. It's also very easy to direct the output to a log file in
case there are errors, so you can produce an installation log. Whether
or not this is a good solution in your case, I don't know, as I have
no experience of the WISE tools.

Simon
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Sticks | last post: by
reply views Thread by Russell E. Owen | last post: by
2 posts views Thread by Tim Daneliuk | last post: by
1 post views Thread by sub1ime_uk | last post: by
24 posts views Thread by Mark | last post: by
2 posts views Thread by rodmc | last post: by
4 posts views Thread by Jonathan Wood | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.