|
 |
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').
>
|
|
|
View Cart
