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
