From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Brian Bessemer
Sent: Friday, March 30, 2001 4:47 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Stored Procedure Result sets and OLEDB DSNs
<cfstoredproc> tag
<cfprocparam>
tag
<cfprocresult> tag
</cfstoredproc>
inside the storedproc tag you will name your stored procedure,
datasource, etc.
cfprocparam is used to send or retrieve
variables
cfprocresult is used to name the results of a
stored procedure
you then refer to the the stored proc the same way you refer to a cfquery ( <cfoutput query="name you put in cfprocresult tag">)
-----Original Message-----
From: The
Hepburn's [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 30, 2001 4:16 PM
To: [EMAIL PROTECTED]
Subject: RE: Stored
Procedure Result sets and OLEDB DSNs
So how do you get a stored procedure to display the fields you
requested in
the Stored procedure in CF
-----Original Message-----
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of David L. Penton
Sent: Friday,
March 30, 2001 4:25 PM
To: [EMAIL PROTECTED]
Subject: RE: Stored Procedure Result sets and OLEDB DSNs
When you run a Stored Procedure, you can do a number of things
inside of
them. A couple of things:
[1] SELECTs
[2] UPDATEs
[3] DELETEs
[4] INSERTs
[5] CREATEs (like create table, or SELECT INTO which can create a
temp
table)
[6] Administrative
issues
and many others, of which all return some sort of success/fail
(success
would be the absence of a high severity
error) and with that can return a
RecordsAffected
value (in the case of INSERT, UPDATE, DELETE, and SELECT)
SQL Server will execute each command in succession and OLE-DB will
receive
the confirmation that each command is
successful. ColdFusion query
execution tags are
set to return the FIRST successful query. By query, the
simple definition that a command executed that returned a
RecordsAffected
status. If you run the Stored
Procedure in Query Analyzer you will see this
behavior. ColdFusion (OLE-DB) will pick up on the first thing in
the
Results window. SET NOCOUNT ON is a directive
that tells SQL Server to not
report RecordsAffected for
operations which return recordsets which are
closed
(i.e. BOF and EOF are both TRUE) This shields those operations from
CF (OLE-DB) and allows you to get the correct recordset after
completion.
hth...
David L. Penton, MCP
Consultant
"Mathematics is music for the mind, and Music is Mathematics
for the
Soul. - J.S. Bach"
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
-----Original Message-----
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Clint Tredway
Thanks, that worked. May I trouble you to ask why I needed to do that?
---------- Original Message
----------------------------------
From: "David L.
Penton" <[EMAIL PROTECTED]>
Reply-To:
[EMAIL PROTECTED]
In the case of SQL Server, you may need to add SET NOCOUNT ON in
the stored
procedure or in your CFQUERY before the SP
call:
<CFQUERY>
SET NOCOUNT
ON;
sp_Whatever
</CFQUERY>
Any INSERTs, UPDATEs, or DELETEs generate RecordsAffected, which
OLE-DB
interprets as a Recordset with no records.
hth...
David L. Penton, MCP
-----Original Message-----
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Clint Tredway
Is there anything special I have to do to use OLEDB DSNs and
stored
procedures?
In any of my cfqueries that return a result set from a stored
procedure, CF
is not getting the result set.
any help is appreciated.
--
Clint Tredway
www.factorxsoftware.com
--
-------------------------------------------------------------------------
This email server is running an evaluation copy of the
MailShield anti-
spam software. Please contact your
email administrator if you have any
questions about this
message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To
subscribe / unsubscribe: http://www.dfwcfug.org
-------------------------------------------------------------------------
This email server is running an evaluation copy of the
MailShield anti-
spam software. Please contact your
email administrator if you have any
questions about this
message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To
subscribe / unsubscribe: http://www.dfwcfug.org
