p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

asp_databases thread: exec a SQL stored proc with no paramaters


Message #1 by eck_e_thump@h... on Wed, 19 Dec 2001 11:50:24
OK, so I've been tearing my hair out all morning trying to get this to 

work. And I've failed.



Can somebody out there please give me some code for executing a stored 

procedure held on a sql database, that doesn't require any paramaters and 

returns the info into a recordset.



Microsofts support database code doesn't work, Code I have found in these 

archives doesn't work. What am I to do.



Thanks in advanced



Chris Lee
Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 19 Dec 2001 13:00:31 +0100
this should do the trick.



rs.open "procedurename", objConn, adOpenForwardOnly, adLockReadOnly,

adCmdStoredProc



-Kim





> -----Original Message-----

> From: eck_e_thump@h... [mailto:eck_e_thump@h...]

> Sent: 19. december 2001 11:50

> To: ASP Databases

> Subject: [asp_databases] exec a SQL stored proc with no paramaters

>

>

> OK, so I've been tearing my hair out all morning trying to get this to

> work. And I've failed.

>

> Can somebody out there please give me some code for executing a stored

> procedure held on a sql database, that doesn't require any paramaters and

> returns the info into a recordset.

>

> Microsofts support database code doesn't work, Code I have found in these

> archives doesn't work. What am I to do.

>

> Thanks in advanced

>

> Chris Lee




> $subst('Email.Unsub').

>



Message #3 by "Chris Lee" <eck_e_thump@h...> on Wed, 19 Dec 2001 12:21:35
Can you tell me what numbers to replace the options with. I tried



Set Conn1 = Server.CreateObject("ADODB.Connection")

Conn1.Open Application("my_conn")



rstdata.open "SP_Active_Resource_Request_Report", Conn1, 

adOpenForwardOnly, adLockReadOnly, adCmdStoredProc



as it doesn't understand adOpenForwardOnly etc



Cheers,

Chris



> this should do the trick.

> 

> rs.open "procedurename", objConn, adOpenForwardOnly, adLockReadOnly,

> adCmdStoredProc

> 

> -Kim

> 

> 

> > -----Original Message-----

> > From: eck_e_thump@h... [mailto:eck_e_thump@h...]

> > Sent: 19. december 2001 11:50

> > To: ASP Databases

> > Subject: [asp_databases] exec a SQL stored proc with no paramaters

> >

> >

> > OK, so I've been tearing my hair out all morning trying to get this to

> > work. And I've failed.

> >

> > Can somebody out there please give me some code for executing a stored

> > procedure held on a sql database, that doesn't require any paramaters 

and

> > returns the info into a recordset.

> >

> > Microsofts support database code doesn't work, Code I have found in 

these

> > archives doesn't work. What am I to do.

> >

> > Thanks in advanced

> >

> > Chris Lee




> > $subst('Email.Unsub').

> >

> 

Message #4 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 19 Dec 2001 13:22:37 +0100
you can look the constants up in adovbs.inc, or just include the entire file

:)



-Kim





> -----Original Message-----

> From: Chris Lee [mailto:eck_e_thump@h...]

> Sent: 19. december 2001 12:22

> To: ASP Databases

> Subject: [asp_databases] RE: exec a SQL stored proc with no paramaters

>

>

> Can you tell me what numbers to replace the options with. I tried

>

> Set Conn1 = Server.CreateObject("ADODB.Connection")

> Conn1.Open Application("my_conn")

>

> rstdata.open "SP_Active_Resource_Request_Report", Conn1,

> adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

>

> as it doesn't understand adOpenForwardOnly etc

>

> Cheers,

> Chris

>

> > this should do the trick.

> >

> > rs.open "procedurename", objConn, adOpenForwardOnly, adLockReadOnly,

> > adCmdStoredProc

> >

> > -Kim

> >

> >

> > > -----Original Message-----

> > > From: eck_e_thump@h... [mailto:eck_e_thump@h...]

> > > Sent: 19. december 2001 11:50

> > > To: ASP Databases

> > > Subject: [asp_databases] exec a SQL stored proc with no paramaters

> > >

> > >

> > > OK, so I've been tearing my hair out all morning trying to get this to

> > > work. And I've failed.

> > >

> > > Can somebody out there please give me some code for executing a stored

> > > procedure held on a sql database, that doesn't require any paramaters

> and

> > > returns the info into a recordset.

> > >

> > > Microsofts support database code doesn't work, Code I have found in

> these

> > > archives doesn't work. What am I to do.

> > >

> > > Thanks in advanced

> > >

> > > Chris Lee




> > > $subst('Email.Unsub').

> > >

> >

>




> $subst('Email.Unsub').

>



Message #5 by "Daniel Njuguna" <danny@n...> on Wed, 19 Dec 2001 15:48:07 +0300
this is the error it gives me now





The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another. 



---------- Original Message ----------------------------------

From: "Kim Iwan Hansen" <kimiwan@k...>

Reply-To: "ASP Databases" <asp_databases@p...>

Date:  Wed, 19 Dec 2001 13:00:31 +0100



>this should do the trick.

>

>rs.open "procedurename", objConn, adOpenForwardOnly, adLockReadOnly,

>adCmdStoredProc

>

>-Kim

>

>

>> -----Original Message-----

>> From: eck_e_thump@h... [mailto:eck_e_thump@h...]

>> Sent: 19. december 2001 11:50

>> To: ASP Databases

>> Subject: [asp_databases] exec a SQL stored proc with no paramaters

>>

>>

>> OK, so I've been tearing my hair out all morning trying to get this to

>> work. And I've failed.

>>

>> Can somebody out there please give me some code for executing a stored

>> procedure held on a sql database, that doesn't require any paramaters and

>> returns the info into a recordset.

>>

>> Microsofts support database code doesn't work, Code I have found in these

>> archives doesn't work. What am I to do.

>>

>> Thanks in advanced

>>

>> Chris Lee




>> $subst('Email.Unsub').

>>

>

>




>



--

Danny Njuguna

--

Message #6 by "Chris Lee" <eck_e_thump@h...> on Wed, 19 Dec 2001 13:01:34
How do I get that file then?



> you can look the constants up in adovbs.inc, or just include the entire 

file

> :)

> 

> -Kim

> 

> 

> > -----Original Message-----

> > From: Chris Lee [mailto:eck_e_thump@h...]

> > Sent: 19. december 2001 12:22

> > To: ASP Databases

> > Subject: [asp_databases] RE: exec a SQL stored proc with no paramaters

> >

> >

> > Can you tell me what numbers to replace the options with. I tried

> >

> > Set Conn1 = Server.CreateObject("ADODB.Connection")

> > Conn1.Open Application("my_conn")

> >

> > rstdata.open "SP_Active_Resource_Request_Report", Conn1,

> > adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

> >

> > as it doesn't understand adOpenForwardOnly etc

> >

> > Cheers,

> > Chris

> >

> > > this should do the trick.

> > >

> > > rs.open "procedurename", objConn, adOpenForwardOnly, adLockReadOnly,

> > > adCmdStoredProc

> > >

> > > -Kim

> > >

> > >

> > > > -----Original Message-----

> > > > From: eck_e_thump@h... [mailto:eck_e_thump@h...]

> > > > Sent: 19. december 2001 11:50

> > > > To: ASP Databases

> > > > Subject: [asp_databases] exec a SQL stored proc with no paramaters

> > > >

> > > >

> > > > OK, so I've been tearing my hair out all morning trying to get 

this to

> > > > work. And I've failed.

> > > >

> > > > Can somebody out there please give me some code for executing a 

stored

> > > > procedure held on a sql database, that doesn't require any 

paramaters

> > and

> > > > returns the info into a recordset.

> > > >

> > > > Microsofts support database code doesn't work, Code I have found in

> > these

> > > > archives doesn't work. What am I to do.

> > > >

> > > > Thanks in advanced

> > > >

> > > > Chris Lee




> > > > $subst('Email.Unsub').

> > > >

> > >

> >




> > $subst('Email.Unsub').

> >

> 

Message #7 by "Kim Iwan Hansen" <kimiwan@k...> on Wed, 19 Dec 2001 14:12:38 +0100
c:\Program Files\common Files\System\ado\adovbs.inc



but the better solution would be to use the following line instead, to

include the the type library for constants.  it contains the same things but

is more efficient to use.



<!--METADATA TYPE="typelib" File="c:\Program Files\Common

Files\System\ado\msado15.dll" -->



but do take a close look at adovbs.inc - here you can see which constants

you can use.



-Kim





> -----Original Message-----

> From: Chris Lee [mailto:eck_e_thump@h...]

> Sent: 19. december 2001 13:02

> To: ASP Databases

> Subject: [asp_databases] RE: exec a SQL stored proc with no paramaters

>

>

> How do I get that file then?

>

> > you can look the constants up in adovbs.inc, or just include the entire

> file

> > :)

> >

> > -Kim

> >

> >

> > > -----Original Message-----

> > > From: Chris Lee [mailto:eck_e_thump@h...]

> > > Sent: 19. december 2001 12:22

> > > To: ASP Databases

> > > Subject: [asp_databases] RE: exec a SQL stored proc with no paramaters

> > >

> > >

> > > Can you tell me what numbers to replace the options with. I tried

> > >

> > > Set Conn1 = Server.CreateObject("ADODB.Connection")

> > > Conn1.Open Application("my_conn")

> > >

> > > rstdata.open "SP_Active_Resource_Request_Report", Conn1,

> > > adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

> > >

> > > as it doesn't understand adOpenForwardOnly etc

> > >

> > > Cheers,

> > > Chris

> > >

> > > > this should do the trick.

> > > >

> > > > rs.open "procedurename", objConn, adOpenForwardOnly, adLockReadOnly,

> > > > adCmdStoredProc

> > > >

> > > > -Kim

> > > >

> > > >

> > > > > -----Original Message-----

> > > > > From: eck_e_thump@h... [mailto:eck_e_thump@h...]

> > > > > Sent: 19. december 2001 11:50

> > > > > To: ASP Databases

> > > > > Subject: [asp_databases] exec a SQL stored proc with no paramaters

> > > > >

> > > > >

> > > > > OK, so I've been tearing my hair out all morning trying to get

> this to

> > > > > work. And I've failed.

> > > > >

> > > > > Can somebody out there please give me some code for executing a

> stored

> > > > > procedure held on a sql database, that doesn't require any

> paramaters

> > > and

> > > > > returns the info into a recordset.

> > > > >

> > > > > Microsofts support database code doesn't work, Code I

> have found in

> > > these

> > > > > archives doesn't work. What am I to do.

> > > > >

> > > > > Thanks in advanced

> > > > >

> > > > > Chris Lee




> > > > > $subst('Email.Unsub').

> > > > >

> > > >

> > >




> > > $subst('Email.Unsub').

> > >

> >

>




> $subst('Email.Unsub').

>



Message #8 by "Chris Lee" <eck_e_thump@h...> on Wed, 19 Dec 2001 13:32:43
I've got the file and it is possible that the procedure is running, but 

(there's always a but) when I try to do something with the recordset it 

tells me that it is closed.



ADODB.Recordset error '800a0e78' 



Operation is not allowed when the object is closed. 



Any ideas?



Cheers

--------------------------



> c:\Program Files\common Files\System\ado\adovbs.inc

> 

> but the better solution would be to use the following line instead, to

> include the the type library for constants.  it contains the same things 

but

> is more efficient to use.

> 

> <!--METADATA TYPE="typelib" File="c:\Program Files\Common

> Files\System\ado\msado15.dll" -->

> 

> but do take a close look at adovbs.inc - here you can see which constants

> you can use.

> 

> -Kim

> 

> 

> > -----Original Message-----

> > From: Chris Lee [mailto:eck_e_thump@h...]

> > Sent: 19. december 2001 13:02

> > To: ASP Databases

> > Subject: [asp_databases] RE: exec a SQL stored proc with no paramaters

> >

> >

> > How do I get that file then?

> >

> > > you can look the constants up in adovbs.inc, or just include the 

entire

> > file

> > > :)

> > >

> > > -Kim

> > >

> > >

> > > > -----Original Message-----

> > > > From: Chris Lee [mailto:eck_e_thump@h...]

> > > > Sent: 19. december 2001 12:22

> > > > To: ASP Databases

> > > > Subject: [asp_databases] RE: exec a SQL stored proc with no 

paramaters

> > > >

> > > >

> > > > Can you tell me what numbers to replace the options with. I tried

> > > >

> > > > Set Conn1 = Server.CreateObject("ADODB.Connection")

> > > > Conn1.Open Application("my_conn")

> > > >

> > > > rstdata.open "SP_Active_Resource_Request_Report", Conn1,

> > > > adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

> > > >

> > > > as it doesn't understand adOpenForwardOnly etc

> > > >

> > > > Cheers,

> > > > Chris

> > > >

> > > > > this should do the trick.

> > > > >

> > > > > rs.open "procedurename", objConn, adOpenForwardOnly, 

adLockReadOnly,

> > > > > adCmdStoredProc

> > > > >

> > > > > -Kim

> > > > >

> > > > >

> > > > > > -----Original Message-----

> > > > > > From: eck_e_thump@h... [mailto:eck_e_thump@h...]

> > > > > > Sent: 19. december 2001 11:50

> > > > > > To: ASP Databases

> > > > > > Subject: [asp_databases] exec a SQL stored proc with no 

paramaters

> > > > > >

> > > > > >

> > > > > > OK, so I've been tearing my hair out all morning trying to get

> > this to

> > > > > > work. And I've failed.

> > > > > >

> > > > > > Can somebody out there please give me some code for executing a

> > stored

> > > > > > procedure held on a sql database, that doesn't require any

> > paramaters

> > > > and

> > > > > > returns the info into a recordset.

> > > > > >

> > > > > > Microsofts support database code doesn't work, Code I

> > have found in

> > > > these

> > > > > > archives doesn't work. What am I to do.

> > > > > >

> > > > > > Thanks in advanced

> > > > > >

> > > > > > Chris Lee




> > > > > > $subst('Email.Unsub').

> > > > > >

> > > > >

> > > >




> > > > $subst('Email.Unsub').

> > > >

> > >

> >




> > $subst('Email.Unsub').

> >

> 

Message #9 by "Dallas Martin" <dmartin@z...> on Wed, 19 Dec 2001 08:34:04 -0500
CREATE PROCEDURE sp_GetAuthors

AS

SELECT * FROM authors

RETURN



<%

set RS = Conn.Execute("EXEC sp_GetAuthors")

%>



Dallas





----- Original Message -----

From: <eck_e_thump@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, December 19, 2001 11:50 AM

Subject: [asp_databases] exec a SQL stored proc with no paramaters





> OK, so I've been tearing my hair out all morning trying to get this to

> work. And I've failed.

>

> Can somebody out there please give me some code for executing a stored

> procedure held on a sql database, that doesn't require any paramaters and

> returns the info into a recordset.

>

> Microsofts support database code doesn't work, Code I have found in these

> archives doesn't work. What am I to do.

>

> Thanks in advanced

>

> Chris Lee




$subst('Email.Unsub').

>



Message #10 by "Chris Lee" <eck_e_thump@h...> on Wed, 19 Dec 2001 13:48:59
This does the same saying the recordset is closed as soon as I try to do 

something with it. Also this is actually executing SQL from the command so 

it would still have to be parsed,(taking extra time etc) thus defeating 

one of the objects of using a stored procedure.





> CREATE PROCEDURE sp_GetAuthors

> AS

> SELECT * FROM authors

> RETURN

> 

> <%

> set RS = Conn.Execute("EXEC sp_GetAuthors")

> %>

> 

> Dallas

> 

> 

> ----- Original Message -----

> From: <eck_e_thump@h...>

> To: "ASP Databases" <asp_databases@p...>

> Sent: Wednesday, December 19, 2001 11:50 AM

> Subject: [asp_databases] exec a SQL stored proc with no paramaters

> 

> 

> > OK, so I've been tearing my hair out all morning trying to get this to

> > work. And I've failed.

> >

> > Can somebody out there please give me some code for executing a stored

> > procedure held on a sql database, that doesn't require any paramaters 

and

> > returns the info into a recordset.

> >

> > Microsofts support database code doesn't work, Code I have found in 

these

> > archives doesn't work. What am I to do.

> >

> > Thanks in advanced

> >

> > Chris Lee




> $subst('Email.Unsub').

> >

> 

Message #11 by "Chris Lee" <eck_e_thump@h...> on Wed, 19 Dec 2001 14:01:11
Got some progress.

If  I do some noddy sql as Dallas suggested, then it works. Therefore it 

must be my SQL. However, I know the SQL works fine in Query Analyser, so 

is there certain things that cannot be done through the ASP.

All the sql does is

Create a tempory table from several others

Create another tempory table from several others

then performs a select from the two temp tables

and then drops the temp tables.



What should happen is the recordset should be populated with the final 

select. Is this not so?
Message #12 by "Dallas Martin" <dmartin@z...> on Wed, 19 Dec 2001 09:01:44 -0500
Okay, by the numbers:



1) Create the below stored procedure in the SQL Database.

2) Make sure the ASP user has login rights to the DB server and

     Access rights to the database and this stored procedure

3) Put the ASP code in an ASP page with a Connection.

4) Response.write the output: au_lname,au_fname,au_author_id, etc



I know this works, because I NOW test every code example that I

post to the list.



Good Luck!!!







----- Original Message -----

From: "Chris Lee" <eck_e_thump@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, December 19, 2001 1:48 PM

Subject: [asp_databases] Re: exec a SQL stored proc with no paramaters





> This does the same saying the recordset is closed as soon as I try to do

> something with it. Also this is actually executing SQL from the command so

> it would still have to be parsed,(taking extra time etc) thus defeating

> one of the objects of using a stored procedure.

>

>

> > CREATE PROCEDURE sp_GetAuthors

> > AS

> > SELECT * FROM authors

> > RETURN

> >

> > <%

> > set RS = Conn.Execute("EXEC sp_GetAuthors")

> > %>

> >

> > Dallas

> >

> >

> > ----- Original Message -----

> > From: <eck_e_thump@h...>

> > To: "ASP Databases" <asp_databases@p...>

> > Sent: Wednesday, December 19, 2001 11:50 AM

> > Subject: [asp_databases] exec a SQL stored proc with no paramaters

> >

> >

> > > OK, so I've been tearing my hair out all morning trying to get this to

> > > work. And I've failed.

> > >

> > > Can somebody out there please give me some code for executing a stored

> > > procedure held on a sql database, that doesn't require any paramaters

> and

> > > returns the info into a recordset.

> > >

> > > Microsofts support database code doesn't work, Code I have found in

> these

> > > archives doesn't work. What am I to do.

> > >

> > > Thanks in advanced

> > >

> > > Chris Lee




> > $subst('Email.Unsub').

> > >

> >

>




$subst('Email.Unsub').

>



Message #13 by "Breidenbach, Beth" <Beth.Breidenbach@g...> on Wed, 19 Dec 2001 09:09:28 -0500
1)  Have you double-checked the permissions with which your connection is

running?  If things are different between running interactively (i.e. Query

Analyzer) and ASP, that's always the first thing I check.



2)  Run SQLProfiler and have it capture SQL sent to the db.  Then, run your

sproc once via Query Analyzer and once via the ASP page -- look at the

captured SQL and see what's different between the two runs.



Beth Breidenbach



-----Original Message-----

From: Chris Lee [mailto:eck_e_thump@h...]

Sent: Wednesday, December 19, 2001 6:01 AM

To: ASP Databases

Subject: [asp_databases] Re: exec a SQL stored proc with no paramaters





Got some progress.

If  I do some noddy sql as Dallas suggested, then it works. Therefore it 

must be my SQL. However, I know the SQL works fine in Query Analyser, so 

is there certain things that cannot be done through the ASP.

All the sql does is

Create a tempory table from several others

Create another tempory table from several others

then performs a select from the two temp tables

and then drops the temp tables.



What should happen is the recordset should be populated with the final 

select. Is this not so?



to unsubscribe  	send a blank email to

$subst('Email.Unsub').

Message #14 by "Chris Lee" <eck_e_thump@h...> on Wed, 19 Dec 2001 14:35:30
I have run through that profiler, (first time used, very nice indeed). The 

text is exactly the same, even down to the case. It does take longer 

(duration/CPU) via the asp page but I would expect that. My connetion 

string is

Application("my_conn") = "Provider=SQLOLEDB.1;Persist Security 

Info=False;User ID=**;Initial Catalog=IT_FACILITIES;Data 

Source=ntfspcteam01;Locale Identifier=2057;Connect Timeout=15;Use 

Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation 

ID=intranet;User Id=sa;



I know nothing about this and how it would effect running some sql, can 

you explaine.



Cheers,

Chris



> 1)  Have you double-checked the permissions with which your connection is

> running?  If things are different between running interactively (i.e. 

Query

> Analyzer) and ASP, that's always the first thing I check.

> 

> 2)  Run SQLProfiler and have it capture SQL sent to the db.  Then, run 

your

> sproc once via Query Analyzer and once via the ASP page -- look at the

> captured SQL and see what's different between the two runs.

> 

> Beth Breidenbach

> 

> -----Original Message-----

> From: Chris Lee [mailto:eck_e_thump@h...]

> Sent: Wednesday, December 19, 2001 6:01 AM

> To: ASP Databases

> Subject: [asp_databases] Re: exec a SQL stored proc with no paramaters

> 

> 

> Got some progress.

> If  I do some noddy sql as Dallas suggested, then it works. Therefore it 

> must be my SQL. However, I know the SQL works fine in Query Analyser, so 

> is there certain things that cannot be done through the ASP.

> All the sql does is

> Create a tempory table from several others

> Create another tempory table from several others

> then performs a select from the two temp tables

> and then drops the temp tables.

> 

> What should happen is the recordset should be populated with the final 

> select. Is this not so?



> to unsubscribe  	send a blank email to

> $subst('Email.Unsub').

Message #15 by "Chris Lee" <eck_e_thump@h...> on Wed, 19 Dec 2001 15:24:45
And the answer is......



It would appear that when you execute the stored procedure it will only 

bring back the info from the first sql statement. So if the first doesn't 

actually bring back anything (like create a temp table) it won't display 

anything and close the recordset. Even though the stored proc continues to 

run to completition. I am having to create multiple stored procs and 

execute them in turn, and then it works.



Thanks for all the help I would not have been able to get it done if it 

wasn't for you all.



I'm off for a celebratory sticky bun.



Thanks



Chris Lee
Message #16 by dmartin@z... on Wed, 19 Dec 2001 10:25:37 -0500
Make sure that your stored procedure

includes the statement, SET NOCOUNT ON,

right before the first SELECT statement.

You could be encountering problems with

SQL returning information about the number

of records insert into the temp table.



Dallas



Quoting Chris Lee <eck_e_thump@h...>:



> Got some progress.

> If  I do some noddy sql as Dallas suggested, then it 

works. Therefore it

> must be my SQL. However, I know the SQL works fine in 

Query Analyser, so

> is there certain things that cannot be done through 

the ASP.

> All the sql does is

> Create a tempory table from several others

> Create another tempory table from several others

> then performs a select from the two temp tables

> and then drops the temp tables.

>

> What should happen is the recordset should be 

populated with the final

> select. Is this not so?





> $subst('Email.Unsub').

> 


  Return to Index