Running SQL scripts within a VB enviroment.

[Mark Davies]

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)

[Rick Razzano]

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.

[Simon Hayes]

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