Lisa
I have a dozen stored procs with nothing between the selects (such as GO).
Unless there is something specific about Sybase returning multiple
resultsets to a client, you shouldn't need anything. But at least you have
isolated the problem somewhat. I'm not sure what to try next - there are a
few things about sybase drivers and such in CF help, but none seems to
pertain to this particular problem. However, there are clearly more setting
choices in setting up Sybase native drivers than with SQL Server. You might
consider trying a new DSN in CF administrator with the native Sybase driver
(as opposed to ODBC) and see if the problem goes away. Ideally, someone on
this list uses Sybase and will chime in now.
Mark
That was a good idea. It is only returning the last resultset no matter
which select statement is last. Is there something that needs to separate
each select (begin/end statements, etc.) in order for CF to see it as a
distinct resultset?
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 09, 2001 9:51 AM
To: CF-Talk
Subject: RE: Multiple Resultsets from a Stored Procedure
When you say that the third select is the one which returns the data, do
you mean successfully, while the other two fail? Or that the first two
selects do not return data because they are not supposed to? I assume it's
the former. It sounds like if you run it with only one crprocresult tag,
where resultset="3", it works fine. Which leads me to ask: Are the first
two returning recordsets or variables?
If you have always had trouble with multiple result sets, there's
definitely a pattern there. Maybe you could switch two of the select
statements in the stored proc (for testing). Call the third select when
it's in different positions (and with a different number). If your problem
has to do with only retrieving the last recordset, (i.e. the last one only
works each time, whichever one is last), then this will at least focus your
attention on that. Otherwise you can focus on the select statements.
Mark
I have all the variables in the correct order - the procedure will run fine
- the errors come into play when I add the cfprocresult tag for resultset
one and two. There are three main select statements in the procedure (the
third being the simplest and the one which returns the data). They are not
separated in the stored procedure (no begin/end or anything) - could this
possibly be the problem? I have never managed to have a stored procedure
with multiple resultsets work (with this particular sp writer) with the
exception of the last returned resultset. Any ideas are greatly
appreciated.
Lisa Haas
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 08, 2001 4:21 PM
To: [EMAIL PROTECTED]
Subject: RE: Multiple Resultsets from a Stored Procedure
Yeah, that confuses me too. That's one for the list at large - although not
necessarily relevant to the current problem. My guess is the Microsoft
makes the odbc drivers, and that they're SQL Server compatible; Sybase and
SQL Server have an intertwined history after all. Are there other pages you
have where multiple resultsets work?
Mark
Also - I'm not sure why it says [INTERSOLV][ODBC SQL Server driver] since I
am using a Sybase System 11 driver...
Lisa
-----Original Message-----
From: Haas, Lisa
Sent: Monday, January 08, 2001 3:55 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Multiple Resultsets from a Stored Procedure
Yes - see below: What I'm now finding is that the third resultset has no
problem. The first two give me the error ODBC Error Code = S1002 (Invalid
column number) Number of bound columns exceeds the number of result
columns.
<cfstoredproc procedure="mcc_renewal_epatron" datasource="mcc">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@season"
value="C2002">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@start_series"
value="CJO">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@end_series"
value="CJO">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@start_package"
value="CJ2">
<cfprocparam type="in" cfsqltype="CF_SQL_CHAR" dbvarname="@end_package"
value="CJ2">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@group"
null="Yes">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@code" null
="Yes">
<cfprocparam type="In" cfsqltype="CF_SQL_CHAR" dbvarname="@value"
null="Yes">
<cfprocresult name="sub_info" resultset="1">
<cfprocresult name="patron_info" resultset="2">
<cfprocresult name="seat_info" resultset="3">
</cfstoredproc>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
