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)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.|||mark@.uunix.com (Mark Davies) wrote in message news:<f81eeddd.0311110400.57f47a47@.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

Running SQL Scripts from VB

HI All,
I think I am posting this to the right group , If not Please Ignore .
Does anyone know how to run a Sql script from a VB Program . My
requirement is something like this : From my VB Code , I need to
execute a sql script which will internally create a Temporary Stored
Procedure . This Stored Procedure has one column that gets filled up
with the further processing of the sql script . Later on I need to
select all the rows from this proc ( if any ) .
Kindly , Post reply if solution is known to anyone of you .
Thanks In advance ,
triveni
Hi
"triveni504@.gmail.com" wrote:

> HI All,
> I think I am posting this to the right group , If not Please Ignore .
> Does anyone know how to run a Sql script from a VB Program . My
> requirement is something like this : From my VB Code , I need to
> execute a sql script which will internally create a Temporary Stored
> Procedure . This Stored Procedure has one column that gets filled up
> with the further processing of the sql script . Later on I need to
> select all the rows from this proc ( if any ) .
> Kindly , Post reply if solution is known to anyone of you .
> Thanks In advance ,
> triveni
>
You can look at the ADO examples that come with SQL Server see
http://msdn2.microsoft.com/en-us/library/aa173708(SQL.80).aspx regarding
where they can be found.
Your terminology does not seem correct I assume that you want to create a
temporary table. If there is a large number of statements in your script you
may want to encapsulate this in a stored procedure and just execute that from
your code. This will reduce the amount of network traffic and will also allow
you to implement some error handling.
John

Running SQL Scripts from VB

HI All,
I think I am posting this to the right group , If not Please Ignore .
Does anyone know how to run a Sql script from a VB Program . My
requirement is something like this : From my VB Code , I need to
execute a sql script which will internally create a Temporary Stored
Procedure . This Stored Procedure has one column that gets filled up
with the further processing of the sql script . Later on I need to
select all the rows from this proc ( if any ) .
Kindly , Post reply if solution is known to anyone of you .
Thanks In advance ,
triveniHi
"triveni504@.gmail.com" wrote:
> HI All,
> I think I am posting this to the right group , If not Please Ignore .
> Does anyone know how to run a Sql script from a VB Program . My
> requirement is something like this : From my VB Code , I need to
> execute a sql script which will internally create a Temporary Stored
> Procedure . This Stored Procedure has one column that gets filled up
> with the further processing of the sql script . Later on I need to
> select all the rows from this proc ( if any ) .
> Kindly , Post reply if solution is known to anyone of you .
> Thanks In advance ,
> triveni
>
You can look at the ADO examples that come with SQL Server see
http://msdn2.microsoft.com/en-us/library/aa173708(SQL.80).aspx regarding
where they can be found.
Your terminology does not seem correct I assume that you want to create a
temporary table. If there is a large number of statements in your script you
may want to encapsulate this in a stored procedure and just execute that from
your code. This will reduce the amount of network traffic and will also allow
you to implement some error handling.
John

Running SQL Scripts from VB

HI All,
I think I am posting this to the right group , If not Please Ignore .
Does anyone know how to run a Sql script from a VB Program . My
requirement is something like this : From my VB Code , I need to
execute a sql script which will internally create a Temporary Stored
Procedure . This Stored Procedure has one column that gets filled up
with the further processing of the sql script . Later on I need to
select all the rows from this proc ( if any ) .
Kindly , Post reply if solution is known to anyone of you .
Thanks In advance ,
triveniHi
"triveni504@.gmail.com" wrote:

> HI All,
> I think I am posting this to the right group , If not Please Ignore .
> Does anyone know how to run a Sql script from a VB Program . My
> requirement is something like this : From my VB Code , I need to
> execute a sql script which will internally create a Temporary Stored
> Procedure . This Stored Procedure has one column that gets filled up
> with the further processing of the sql script . Later on I need to
> select all the rows from this proc ( if any ) .
> Kindly , Post reply if solution is known to anyone of you .
> Thanks In advance ,
> triveni
>
You can look at the ADO examples that come with SQL Server see
http://msdn2.microsoft.com/en-us/library/aa173708(SQL.80).aspx regarding
where they can be found.
Your terminology does not seem correct I assume that you want to create a
temporary table. If there is a large number of statements in your script you
may want to encapsulate this in a stored procedure and just execute that fro
m
your code. This will reduce the amount of network traffic and will also allo
w
you to implement some error handling.
John

Running SQL scripts from Command Prompt

I have a requirment, when a user logs in to the pc, I want to run some SQL scripts which is been saved as a stored procedure on another pc. Could this be possible. I know in oracle you could do it using SQLLDR. Is there any way to do it in SQL?

Please advice. Any help is greatly appreciated.

If you are using SQL 2000, check out OSQL utility. If you have plans to upgrade to 2005 you might also want to check out SQLCMD. OSQL will be removed in later versions.|||I have one more problem, I can only use the osql on the server. I want to run this on the client pc which conatins an odbc connection to the sql server|||Then you need to install SQL Server client tools from the SQL Server installation PC on the client pc. You can choose to install client tool only without installing database engine.

Running SQL Scripts against SQL 2005

Our developers generate a large number of SQL scripts that get applied with each application update. Is there an easy way to select one script after the other and run them against a particular database?

Right now it appears I have to open them one by one (since the window selector is useless for actually picking a script) by scanning through the directory for the next script, confirming the connection (thankfully I'm using Integrated Security), Changing the current database (since it always defaults to master) and finally run the script. And I'm ending up doing this for every script. Hoping there's a better way to do this.

Thanks,

Larry

Management studio accepts a subset of sqlcmd statements. The one you want to use is :r. This reads the contents of the file and puts it in line with the query you have.

i.e.

:r c:\sql\myfirstscript.sql

:r c:\sql\mysecondscript.sql

go

:r c:\sql\mythirdscript.sql

in this example the first 2 scripts are combined and run. If they contain GOs in then each batch will be executed as normal.

The point to note is that the :r does execute the file but reads the file into the query. So make sure each file ends in GO or you seperate your :r statements by go.

TO use sqlcmd mode click on the icon on the toolbar with a window AND a red exclamation mark. Or go to Query and select SQLCMD mode

Running sql scripts

How does one run an sql script against a database then using sql management express.

I have the script and i connect to the correct database using the sql management express and execute the script. It even says that it completed successfully. But when i check the database there is nothing new added.

Is it somthing to do with the community preview version of sql management express??

thanks for any help

In Management Studio, the query window (where you run your query) can connect to different SQL instance/database from the Object Explorer (connect to a SQL instance when you open Management Studio). So check the dropdown list on the left of Execute(!) button to make sure your current query is connecting to the right database then execute the script. Before you check changes in the database, right click on the database ->select Refresh, then check the changes.