Create SP from ASP 
Author Message
 Create SP from ASP

I've got a text file that contains the source for a
Stored Procedure.  On my ASP I shell out to a batch file.
The batch file uses the ISQL command to execute the
contents of the SP source file.  This seems to work, but
I would prefer to execute the textfile using ADO.

If I use the File System Object, and set the contents of
the file to a local variable, then set the ADO
commandtext property equal to the local variable it does
not seem to work.  

Anyone know a way to do this using ADO.

Thanks. Kelsey



Fri, 01 Jul 2005 23:20:46 GMT
 Create SP from ASP

Kelsey,

Is it SQL Server? If yes, then probably you have GO statements inside your
batch and OLEDB provider does not recognize that GO, because it is not a
part of Transact-SQL syntax, but part of batch syntax and recognized only by
OSQL, ISQL and Query Analizer, not by provider. What you need to do is to
separate your SQL statements by semicolon (;) or execute each statement
separately.

--
Val Mazur
Microsoft MVP


Quote:
> I've got a text file that contains the source for a
> Stored Procedure.  On my ASP I shell out to a batch file.
> The batch file uses the ISQL command to execute the
> contents of the SP source file.  This seems to work, but
> I would prefer to execute the textfile using ADO.

> If I use the File System Object, and set the contents of
> the file to a local variable, then set the ADO
> commandtext property equal to the local variable it does
> not seem to work.

> Anyone know a way to do this using ADO.

> Thanks. Kelsey



Fri, 01 Jul 2005 23:55:57 GMT
 Create SP from ASP

One reason certain SQL statements are separated by GO in TSQL scripts (a
group of one or more batches) is that some TSQL cannot be executed in the
same batch with the other statements. The entire batch (one of several in a
script) is syntax checked before compilation and execution. If the TSQL in
the batch refers to an object that does not exist, the batch will fail to
compile. For example, "CREATE TABLE ..." needs to be in a batch by itself
and executed before the next step can be compiled and run.

No, I don't expect the insertion of ";" will help you--unless the batches
can be run together. Actually, SQL Server does not need ";" to separate TSQL
statements... any whitespace will do.

I'm mystified, however, why you would shell out to OSQL to do this--unless
it is a script. Even then, writing code to execute a script is a TSQL 101
lesson that my students did in their first lab. It's not at all hard to
implement.

hth
--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Quote:
> Kelsey,

> Is it SQL Server? If yes, then probably you have GO statements inside your
> batch and OLEDB provider does not recognize that GO, because it is not a
> part of Transact-SQL syntax, but part of batch syntax and recognized only
by
> OSQL, ISQL and Query Analizer, not by provider. What you need to do is to
> separate your SQL statements by semicolon (;) or execute each statement
> separately.

> --
> Val Mazur
> Microsoft MVP



> > I've got a text file that contains the source for a
> > Stored Procedure.  On my ASP I shell out to a batch file.
> > The batch file uses the ISQL command to execute the
> > contents of the SP source file.  This seems to work, but
> > I would prefer to execute the textfile using ADO.

> > If I use the File System Object, and set the contents of
> > the file to a local variable, then set the ADO
> > commandtext property equal to the local variable it does
> > not seem to work.

> > Anyone know a way to do this using ADO.

> > Thanks. Kelsey



Sat, 02 Jul 2005 01:50:43 GMT
 Create SP from ASP

Val,

That was it... I had GO statements inside the file.

The first batch was checking to see if the SP existed
prior to executing the CREATE PROCEDURE statement.

I'm going to pull that logic out and execute it
separately.

Thanks for the info.

Kelsey

Quote:
>-----Original Message-----
>Kelsey,

>Is it SQL Server? If yes, then probably you have GO

statements inside your
Quote:
>batch and OLEDB provider does not recognize that GO,
because it is not a
>part of Transact-SQL syntax, but part of batch syntax

and recognized only by
Quote:
>OSQL, ISQL and Query Analizer, not by provider. What you
need to do is to
>separate your SQL statements by semicolon (;) or execute
each statement
>separately.

>--
>Val Mazur
>Microsoft MVP


message

>> I've got a text file that contains the source for a
>> Stored Procedure.  On my ASP I shell out to a batch
file.
>> The batch file uses the ISQL command to execute the
>> contents of the SP source file.  This seems to work,
but
>> I would prefer to execute the textfile using ADO.

>> If I use the File System Object, and set the contents
of
>> the file to a local variable, then set the ADO
>> commandtext property equal to the local variable it
does
>> not seem to work.

>> Anyone know a way to do this using ADO.

>> Thanks. Kelsey

>.



Sat, 02 Jul 2005 01:58:42 GMT
 Create SP from ASP
Bill,
I've got an ASP script that gets the latest version of a
Stored Procedure from VSS and applies it to various
databases.  When I get the source from VSS it creates a
file on the server that I then execute against the
database.  The file had GO statements in it, which
apparently the OLEDB provider does not know what to do
with.  I was shelling out to OSQL to do this because it
does know how to handle the GO statements.

I agree, it is very easy to write code to execute a
script.  I guess the missing piece of information for me
was that OLEDB can't deal with GO.

Thanks for your additional info.

Kelsey

Quote:
>-----Original Message-----
>One reason certain SQL statements are separated by GO in
TSQL scripts (a
>group of one or more batches) is that some TSQL cannot
be executed in the
>same batch with the other statements. The entire batch

(one of several in a
Quote:
>script) is syntax checked before compilation and

execution. If the TSQL in
Quote:
>the batch refers to an object that does not exist, the
batch will fail to
>compile. For example, "CREATE TABLE ..." needs to be in
a batch by itself
>and executed before the next step can be compiled and
run.

>No, I don't expect the insertion of ";" will help you--
unless the batches
>can be run together. Actually, SQL Server does not

need ";" to separate TSQL
Quote:
>statements... any whitespace will do.

>I'm mystified, however, why you would shell out to OSQL
to do this--unless
>it is a script. Even then, writing code to execute a

script is a TSQL 101
Quote:
>lesson that my students did in their first lab. It's not
at all hard to
>implement.

>hth
>--
>____________________________________
>Bill Vaughn
>www.betav.com
>Please reply only to the newsgroup so that others can
benefit.
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>__________________________________



>> Kelsey,

>> Is it SQL Server? If yes, then probably you have GO

statements inside your
Quote:
>> batch and OLEDB provider does not recognize that GO,
because it is not a
>> part of Transact-SQL syntax, but part of batch syntax
and recognized only
>by
>> OSQL, ISQL and Query Analizer, not by provider. What

you need to do is to
Quote:
>> separate your SQL statements by semicolon (;) or

execute each statement

- Show quoted text -

Quote:
>> separately.

>> --
>> Val Mazur
>> Microsoft MVP


message

>> > I've got a text file that contains the source for a
>> > Stored Procedure.  On my ASP I shell out to a batch
file.
>> > The batch file uses the ISQL command to execute the
>> > contents of the SP source file.  This seems to work,
but
>> > I would prefer to execute the textfile using ADO.

>> > If I use the File System Object, and set the
contents of
>> > the file to a local variable, then set the ADO
>> > commandtext property equal to the local variable it
does
>> > not seem to work.

>> > Anyone know a way to do this using ADO.

>> > Thanks. Kelsey

>.



Sat, 02 Jul 2005 02:32:52 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Creating sp by *.asp

2. SP - Possible to run sp from ASP webpage?

3. create sp from sp

4. Vanishing Create Table when creating new SP

5. creating SP from create table script

6. Return value in SP ans ASP code

7. Desenvolvedores VC++ e ASP p/ Sao Paulo-SP

8. Executing a SP from a ASP page to return multiple values

9. Coverting an ASP funtion into an SQL function or SP

10. ASP Passing back a Recordset to SP

11. SP-ASP

12. SP and Vbscript-ASP


 
Powered by phpBB® Forum Software