James, Thanks ... yes cfdump is empty.
I did check things in query analyzer, but maybe not the right way ... I'll take some clues from what you offer here and see if I can get things figure out. H. > -----Original Message----- > From: James Ang [SMTP:[EMAIL PROTECTED]] > Sent: Friday, January 17, 2003 12:06 PM > To: CF-Talk > Subject: RE: StoredProc Error Num 3 > > When you said no rows, are you saying that: > > <cfdump var="#FULL_TEXT#"> > > Returns an empty query dump? Does the query even have any columns? > > Judging from your stored procedure definition, those OUTPUT parameters > are not needed since you never assign them any value. But if you do need > the output parameters, perhaps you meant to have: > > SELECT > @NB_ID = NB_ID, > @COPYRIGHT = COPYRIGHT, > @PUB_DATE = PUB_DATE, > @P_SECTION = P_SECTION, > @PAGE = PAGE, > @WORD_COUNT = WORD_COUNT, > @HEADLINE = HEADLINE, > @SUBHEAD = SUBHEAD, > @LEDE = LEDE, > @STORY = STORY > FROM story_archive > WHERE ARCHIVE_ID = @ARCHIVE_ID > > If you do the above, then: FULL_TEXT will definitely be empty, BUT all > your <cfprocparam> variables will be populated after the stored proc > call. > > And for future stored proc "bugs", you should test with SQL Query > Analyzer first to make sure that your stored proc is working there. If > it is, then the problem is in the CFML code. :) And nice thing about > testing in SQL Query Analyzer is that you can use PRINT statements in > your stored proc (which may or may not be deadly in a CFSTOREDPROC call) > to help you debug better. :) > > ---------------------------- > James Ang > Programmer > MedSeek, Inc. > [EMAIL PROTECTED] > > > > > > -----Original Message----- > From: Owens, Howard [mailto:[EMAIL PROTECTED]] > Sent: Friday, January 17, 2003 11:13 AM > To: CF-Talk > Subject: RE: StoredProc Error Num 3 > > > I'm still pulling my hair out over this stored proc. > > To recap, the variable archive_id does not seem to be getting passed to > the > query ... so no rows (and 1 row should be returned) are being returned. > > Here's my cf tags: > > <cfstoredproc procedure="detail_story" > datasource="#request.dsn#" > returncode="yes"> > > > > <cfprocparam type="INOUT" > cfsqltype="CF_SQL_VARCHAR" > variable="ARCHIVE_ID" > value="#URL.SID#" > null="yes"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_CHAR" > variable="NB_ID" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_VARCHAR" > variable="COPYRIGHT" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_TIMESTAMP" > variable="PUB_DATE" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_VARCHAR" > variable="P_SECTION" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_CHAR" > variable="PAGE" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_CHAR" > variable="WORD_COUNT" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_VARCHAR" > variable="HEADLINE" > null="NO"> > > > <cfprocparam type="Out" > cfsqltype="CF_SQL_VARCHAR" > variable="SUBHEAD" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_VARCHAR" > variable="LEDE" > null="NO"> > > <cfprocparam type="Out" > cfsqltype="CF_SQL_longVARCHAR" > variable="STORY" > null="no"> > > <cfprocresult name="FULL_TEXT" > resultset="1"> > > > </cfstoredproc> > > Here's my stored proc: > > > > > CREATE PROCEDURE detail_story > @ARCHIVE_ID bigint, > @NB_ID char OUTPUT, > @COPYRIGHT varchar OUTPUT, > @PUB_DATE datetime OUTPUT, > @P_SECTION varchar OUTPUT, > @PAGE char OUTPUT, > @WORD_COUNT char OUTPUT, > @HEADLINE varchar OUTPUT, > @SUBHEAD varchar OUTPUT, > @LEDE varchar OUTPUT, > @STORY NTEXT OUTPUT > AS SELECT ARCHIVE_ID, > NB_ID, > COPYRIGHT, > PUB_DATE, > P_SECTION, > PAGE, > WORD_COUNT, > HEADLINE, > SUBHEAD, > LEDE, > STORY > FROM story_archive > WHERE ARCHIVE_ID = @ARCHIVE_ID > GO > > I even tried editing the stored proc to hardcode a variable @archive_id, > and > I still got no results returned. But if I run the query in the SQL > admin, > runs fine ... returns results. > > It's probably some stupid simple little error, but I can't see it. > > H. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
