This is the Stored Procedure----------------
create procedure Michael @JOBID varchar (30) AS
SELECT T1.DetailData, T1.Time, T2.LogOn_Name0, T2.Name0, T1.Status,
T2.SMSID0
FROM dbo.JobDetails as T1,dbo.Identification as T2
WHERE T1.JobID = '@JOBID'
AND (T1.DetailData = (T2.Domain0 + char(124) + T2.SMSID0))
ORDER BY Status, Name0, LogOn_Name0, Time
--------------------------------------------------
This is the first thing I tried in CF---------------------
<cfoutput>
<cfstoredproc procedure="Michael" datasource="#Datasource#"
username="#def_SQLNamehistory#" password="#def_SQLpasshistory#">
<cfprocparam Type="In"
DBVARNAME="@JOBID"
VALUE="#Form.jobID#"
CFSQLTYPE="cf_sql_varchar">
<cfprocresult name="Name0">
</cfstoredproc>
</cfoutput>
----------------------------
This is what I tried next--------------------
<CFQUERY DATASOURCE="#Datasource#" NAME="SMSLOOK"
Username="#def_SQLNamehistory#"
Password="#def_SQLPasshistory#">
EXECute Michael @JOBID = 'dal13fa6'
</cfquery>
<cfoutput query="SMSLook">
#Name0#
</cfoutput>Hello
-----------------------------------
When I run just cfquery with the intial Select staements, I get the Name0,
Status, etc results and I am able to display them. When I run the
procedure, nothing. I turned on the status code option for the procedure
and it says it was successful.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Clint Tredway
Sent: Friday, March 30, 2001 7:46 PM
To: [EMAIL PROTECTED]
Subject: RE: Stored Procedure Result sets and OLEDB DSNs
can you give me an example of what you are trying to do? i may be able to
help.
---------- Original Message ----------------------------------
From: The Hepburn's <[EMAIL PROTECTED]>
Date: Fri, 30 Mar 2001 17:38:19 -0600
RE: Stored Procedure Result sets and OLEDB DSNsI have no problem sending the
variables, it is receiving them so I can display them. I am assuming I need
to do something in my Stored procedure to have it display a variable. Do
you know how I do this?
-----Original Message-----
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
-------------------------------------------------------------------------
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
--
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
