Problem with rollback statement

Hi,

I have written a store procedure which inserts data into two tables. What I want do is to rollback transaction if the second insert fails. Below is a code.

Does anyone see my error?

Thanks,

poc1010

Create proc AddProducts

@.dcint=null,
@.pcint=null,
@.imagepathvarchar(50)=null,
@.typevarchar(2)=null,
@.descriptionvarchar(1000)=null,
@.gendervarchar(8)=null,
@.productidint=null,
@.pccodevarchar(2)=null,
@.weightvarchar(80)=null,
@.pricemoney=null,
@.activevarchar(1)=null

as

declare @.errorsave int
set @.errorsave=0
declare @.dg int

Begin transaction

insert productdescription(
designercategory,
productcategory,
imagepath,
type,
[description],
gender)
values(@.dc,
@.pc,
@.imagepath,
@.type,
@.description,
@.gender)

if @.@.error <> 0
set @.errorsave=@.@.error

set @.dg = @.@.identity

begin
insert Products(
productid,
designergroup,
designercategory,
productcategory,
pccode,
weight,
price,
active)
values(@.productid,
@.dg,
@.dc,
@.pc,
@.pccode,
@.weight,
@.price,
@.active)

if @.@.error <> 0
set @.errorsave=@.@.error
end

if @.errorsave <> 0
begin
print 'Insert into Products tables failed'
rollback transaction
return -5--Insert into Products tables failed
end

commit transaction
print 'Success'
return 0 --SuccessYou have begins and ends in useless spots. Whats the acual error message?|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||My version of your stored proc (minor changes)

create proc AddProducts
 @.dc int = null,
 @.pc int = null,
 @.imagepath varchar(50) = null,
 @.type varchar(2) = null,
 @.description varchar(1000) = null,
 @.gender varchar(8) = null,
 @.productid int = null,
 @.pccode varchar(2) = null,
 @.weight varchar(80) = null,
 @.price money = null,
 @.active varchar(1) = null
as
begin
 declare @.dg int
 begin transaction
 insert into productdescription
 (designercategory,
 productcategory,
 imagepath,
 type,
 [description],
 gender)
 values(@.dc,
 @.pc,
 @.imagepath,
 @.type,
 @.description,
 @.gender)
 if @.@.error <> 0 or @.@.rowcount <> 1
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 set @.dg = @.@.identity
 insert into Products
 (productid,
 designergroup,
 designercategory,
 productcategory,
 pccode,
 weight,
 price,
 active)
 values(@.productid,
 @.dg,
 @.dc,
 @.pc,
 @.pccode,
 @.weight,
 @.price,
 @.active)
 if @.@.error <> 0
 begin
 print 'Insert into Products tables failed'
 rollback transaction
 return -5 --Insert into Products tables failed
 end
 commit transaction
 print 'Success'
 return 0 --Success
end

|||None of you guys used ELSE. Your BEGIN/END's are a little whacked out. Honestly, I'm against returning in mid procedure if it's not necessary. You can easily follow through the entire procedure using an ELSE, then returning a specified value.|||Pierre,

Thanks for your example. I saw what I was doing wrong. Works great.

Thank you for your help.

poc1010|||That's just personal taste Lee. No real argument either way. Not in this case.|||You're right. That's why I said that I prefer the other way. Didn't say you were wrong, because it works fine.

Problem With RecordSet

Hello SQL ASP guys,
I have written this small code:
<% @. Language=VBScript %>
<html>
<head>
<title>SQL Test</title>
</head>
<%
Dim objConn
Dim objRS
Dim strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog =
BOTBS; User Id = ; Password="
%>
Connection Open
<%
strSQL = "SELECT barname from bars"
objRS.Open strSQL, objConn
objRS("barname")
response.write objRS("barname")
%>
This works upto objRS("barname")
Does anyone have any suggestions?J Bowman [293288] wrote:
> Hello SQL ASP guys,
> I have written this small code:
> <% @. Language=VBScript %>
> <html>
> <head>
> <title>SQL Test</title>
> </head>
> <%
> Dim objConn
> Dim objRS
> Dim strSQL
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog =
> BOTBS; User Id = ; Password="
> %>
> Connection Open
> <%
> strSQL = "SELECT barname from bars"
> objRS.Open strSQL, objConn
> objRS("barname")
> response.write objRS("barname")
> %>
> --
> This works upto objRS("barname")
>
> Does anyone have any suggestions?
somevariable = objRS("barname")
also, you should have a userid and password in the connection string
unless you took that out when posting to this newsgroup?|||Got it... Thanks for your help Ken
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1155595738.014662.259630@.m79g2000cwm.googlegroups.com...
> J Bowman [293288] wrote:
> somevariable = objRS("barname")
> also, you should have a userid and password in the connection string
> unless you took that out when posting to this newsgroup?
>

Problem With RecordSet

Hello SQL ASP guys,
I have written this small code:
<% @. Language=VBScript %>
<html>
<head>
<title>SQL Test</title>
</head>
<%
Dim objConn
Dim objRS
Dim strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = BOTBS; User Id = ; Password="
%>
Connection Open
<%
strSQL = "SELECT barname from bars"
objRS.Open strSQL, objConn
objRS("barname")
response.write objRS("barname")
%>
--
This works upto objRS("barname")
Does anyone have any suggestions?J Bowman [293288] wrote:
> Hello SQL ASP guys,
> I have written this small code:
> <% @. Language=VBScript %>
> <html>
> <head>
> <title>SQL Test</title>
> </head>
> <%
> Dim objConn
> Dim objRS
> Dim strSQL
> Set objConn = Server.CreateObject("ADODB.Connection")
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog => BOTBS; User Id = ; Password="
> %>
> Connection Open
> <%
> strSQL = "SELECT barname from bars"
> objRS.Open strSQL, objConn
> objRS("barname")
> response.write objRS("barname")
> %>
> --
> This works upto objRS("barname")
>
> Does anyone have any suggestions?
somevariable = objRS("barname")
also, you should have a userid and password in the connection string
unless you took that out when posting to this newsgroup?|||Got it... Thanks for your help Ken
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1155595738.014662.259630@.m79g2000cwm.googlegroups.com...
> J Bowman [293288] wrote:
>> Hello SQL ASP guys,
>> I have written this small code:
>> <% @. Language=VBScript %>
>> <html>
>> <head>
>> <title>SQL Test</title>
>> </head>
>> <%
>> Dim objConn
>> Dim objRS
>> Dim strSQL
>> Set objConn = Server.CreateObject("ADODB.Connection")
>> Set objRS = Server.CreateObject("ADODB.Recordset")
>> objConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog =>> BOTBS; User Id = ; Password="
>> %>
>> Connection Open
>> <%
>> strSQL = "SELECT barname from bars"
>> objRS.Open strSQL, objConn
>> objRS("barname")
>> response.write objRS("barname")
>> %>
>> --
>> This works upto objRS("barname")
>>
>> Does anyone have any suggestions?
> somevariable = objRS("barname")
> also, you should have a userid and password in the connection string
> unless you took that out when posting to this newsgroup?
>

Problem with parameter default when redeploying report

I've written a rss script file to automate publishing of reports to
our server. I've encountered a problem when republishing reports that
have a default value set for a parameter.
If I change the default value of the parameter in the report rdl and
then try to republish, that parameter's default value is not getting
changed on the server. If I add or remove parameters then the server
gets updated correctly. It's only if I change the default value that
the update is not happening.
I've tried with both the CreateReport and SetReportDefinition
functions. The only way I've gotten this to work is to delete the
report and then republish but this is not an acceptable solution
because it also deletes report history and subscriptions.
I'm using RS2005.
Any help is appreciated.Default values for parameters are a little like datasources, in that you
have to explicitly indicate that you want to override a previous definition
of a datasource when you re-publish a report to a server. Basically the idea
is that your testbed, from which you publish, may not be the same as the
server environment, and you want to keep those things separate, and I'm
saying that parameters' defaults are treated like datasources in this
respect.
OK so far?
If you were handling this interactively using the Report Manager interface,
and assuming you have appropriate rights, you know that you can see the Data
Sources and configure them from the Properties tab of a report. Again, the
assumption is not made that the data source information for this report is
re-deployable and automatically written from your test bed.
Similarly, if a report has parameters, when you have selected the Properties
tab, you should see a Parameters item in the left-hand menu along with Data
Sources. Here you can set the default values differently from how they are
currently set -- I don't really understand whether "Override default" works
all the time or not, you will see it in the dialog, though. Never mind.
Here is where you can fix whatever you don't like about how the report got
re-published.
However, you say "I've tried with both the CreateReport and
SetReportDefinition functions", indicating that you are using web services
rather than interactively publishing. I understand this -- just consider
the above explanation a way to conceptualize *why* the parameters work the
way they do and require a separate step, rather than the way you expected.
I think that you may need to use the .SetReportParameters method here,
explicitly providing the new information to indicate that, yes, you want to
change the default values. If not, it may be the .SetProperties method.
I hope this works for you -- if not, you may be able to use the explanation
above to figure out the correct web service approach <s>.
>L<
<bruce42@.gmail.com> wrote in message
news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
> I've written a rss script file to automate publishing of reports to
> our server. I've encountered a problem when republishing reports that
> have a default value set for a parameter.
> If I change the default value of the parameter in the report rdl and
> then try to republish, that parameter's default value is not getting
> changed on the server. If I add or remove parameters then the server
> gets updated correctly. It's only if I change the default value that
> the update is not happening.
> I've tried with both the CreateReport and SetReportDefinition
> functions. The only way I've gotten this to work is to delete the
> report and then republish but this is not an acceptable solution
> because it also deletes report history and subscriptions.
> I'm using RS2005.
> Any help is appreciated.
>|||So, from what you're saying, it's intentional that the parameter
defaults are not being overriden. You mention an "override defaults",
can you be more specific? I can see an "OverwriteDataSources" if I
publish straight from VS, but I've found nothing regarding overwriting
parameter defaults.
I'm familiar with the SetReportParameters method, what I'm trying to
do is avoid writing specific scripts for each report that I need to
publish. The rss script I use to publish now is a generic script that
I can use against any of my reports. Setting the default values
manually via the report manager interface is not really an option for
me. For one reason, it introduces the possibility of me setting
values differently than what was actually used in our test
environment, and two, we are using query based defaults and the report
manager interface doesn't give you enough detail to even be able to
make these changes (i.e. it doesn't show dataset or value field).
Currently the best idea I have for how to resolve this issue is to
publish my report to a temporary location (where it does not already
exist), loop through all of the parameters and capture the defaults, I
can then publish the report to it's normal location and do a
SetReportParameters using the default values I collected. I'm not
especially happy with this approach, it feels a little kludgy to me,
but at least it keeps me from having to write report specific rss
scripts.
Any other ideas?
thanks for your response...
-bruce
On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
> Defaultvalues for parameters are a little like datasources, in that you
> have to explicitly indicate that you want to override a previous definition
> of a datasource when you re-publish a report to a server. Basically the idea
> is that your testbed, from which you publish, may not be the same as the
> server environment, and you want to keep those things separate, and I'm
> saying that parameters' defaults are treated like datasources in this
> respect.
> OK so far?
> If you were handling this interactively using the Report Manager interface,
> and assuming you have appropriate rights, you know that you can see the Data
> Sources and configure them from the Properties tab of a report. Again, the
> assumption is not made that the data source information for this report is
> re-deployable and automatically written from your test bed.
> Similarly, if a report has parameters, when you have selected the Properties
> tab, you should see a Parameters item in the left-hand menu along with Data
> Sources. Here you can set thedefaultvalues differently from how they are
> currently set -- I don't really understand whether "Overridedefault" works
> all the time or not, you will see it in the dialog, though. Never mind.
> Here is where you can fix whatever you don't like about how the report got
> re-published.
> However, you say "I've tried with both the CreateReport and
> SetReportDefinition functions", indicating that you are using web services
> rather than interactively publishing. I understand this -- just consider
> the above explanation a way to conceptualize *why* the parameters work the
> way they do and require a separate step, rather than the way you expected.
> I think that you may need to use the .SetReportParameters method here,
> explicitly providing the new information to indicate that, yes, you want to
> change thedefaultvalues. If not, it may be the .SetProperties method.
> I hope this works for you -- if not, you may be able to use the explanation
> above to figure out the correct web service approach <s>.
> >L<
> <bruc...@.gmail.com> wrote in message
> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>
> > I've written a rss script file to automate publishing of reports to
> > our server. I've encountered a problem when republishing reports that
> > have adefaultvalue set for aparameter.
> > If I change thedefaultvalue of theparameterin the report rdl and
> > then try to republish, thatparameter'sdefaultvalue is not getting
> > changed on the server. If I add or remove parameters then the server
> > gets updated correctly. It's only if I change thedefaultvalue that
> > the update is not happening.
> > I've tried with both the CreateReport and SetReportDefinition
> > functions. The only way I've gotten this to work is to delete the
> > report and then republish but this is not an acceptable solution
> > because it also deletes report history and subscriptions.
> > I'm using RS2005.
> > Any help is appreciated.- Hide quoted text -
> - Show quoted text -|||Hi Bruce,
>> Any other ideas?
Forgive my lateness of reply (I will CC your e-mail to make sure you see
this) -- I don't get to the forum all that often.
AFAIK you are correct about not having the Overwrite parameter option to
match the Overwrite datasources when you publish straight from VS. When I
mentioned the "override defaults" I was talking only about the interactive
manager interface.
I do agree with you not only that it is confusing but also that, in most
cases, it is not advisable to set values differently in the test environment
than you would set in production. However -- bear with me, I am trying to
envision what was supposed to be the purpose of this "feature" -- we can
imagine that the designers of this system thought it *was* a good idea to
have an "override defaults" so that you could manage the report differently
on different servers, whether for test versus production or deployment of a
generic report to different customers.
For example there might be a sample size used for the test box that would be
different from production, or some sort of customer-specific value that you
wanted to use to brand a generic report.
Now to address your question...
For your generic script purposes, you might need to do something similar to
reflection to "publish" each report.
So far, I'm just restating something that you may have already tried with
your "publish to a temporary location". You could obviously pull the
parameters out of the appropriate Catalog field on the server, or use
GetReportParameters, if you've done that.
However, you don't really need to do that. Remember that the parameters
exist in the RDL, without publication, as a set of XML nodes. So, without
temporarily publishing anywhere, you should be able to read them out of the
RDL and issue the appropriate calls to set them properly on the target.
I hope this makes sense. If you like, you can e-mail me to discuss further
if it doesn't <g>. Again, I don't get here all that much...
>L<
<bruce42@.gmail.com> wrote in message
news:1176117653.014697.204110@.w1g2000hsg.googlegroups.com...
> So, from what you're saying, it's intentional that the parameter
> defaults are not being overriden. You mention an "override defaults",
> can you be more specific? I can see an "OverwriteDataSources" if I
> publish straight from VS, but I've found nothing regarding overwriting
> parameter defaults.
> I'm familiar with the SetReportParameters method, what I'm trying to
> do is avoid writing specific scripts for each report that I need to
> publish. The rss script I use to publish now is a generic script that
> I can use against any of my reports. Setting the default values
> manually via the report manager interface is not really an option for
> me. For one reason, it introduces the possibility of me setting
> values differently than what was actually used in our test
> environment, and two, we are using query based defaults and the report
> manager interface doesn't give you enough detail to even be able to
> make these changes (i.e. it doesn't show dataset or value field).
> Currently the best idea I have for how to resolve this issue is to
> publish my report to a temporary location (where it does not already
> exist), loop through all of the parameters and capture the defaults, I
> can then publish the report to it's normal location and do a
> SetReportParameters using the default values I collected. I'm not
> especially happy with this approach, it feels a little kludgy to me,
> but at least it keeps me from having to write report specific rss
> scripts.
> Any other ideas?
> thanks for your response...
> -bruce
> On Apr 6, 12:43 pm, "Lisa Slater Nicholls" <l...@.spacefold.com> wrote:
>> Defaultvalues for parameters are a little like datasources, in that you
>> have to explicitly indicate that you want to override a previous
>> definition
>> of a datasource when you re-publish a report to a server. Basically the
>> idea
>> is that your testbed, from which you publish, may not be the same as the
>> server environment, and you want to keep those things separate, and I'm
>> saying that parameters' defaults are treated like datasources in this
>> respect.
>> OK so far?
>> If you were handling this interactively using the Report Manager
>> interface,
>> and assuming you have appropriate rights, you know that you can see the
>> Data
>> Sources and configure them from the Properties tab of a report. Again,
>> the
>> assumption is not made that the data source information for this report
>> is
>> re-deployable and automatically written from your test bed.
>> Similarly, if a report has parameters, when you have selected the
>> Properties
>> tab, you should see a Parameters item in the left-hand menu along with
>> Data
>> Sources. Here you can set thedefaultvalues differently from how they are
>> currently set -- I don't really understand whether "Overridedefault"
>> works
>> all the time or not, you will see it in the dialog, though. Never mind.
>> Here is where you can fix whatever you don't like about how the report
>> got
>> re-published.
>> However, you say "I've tried with both the CreateReport and
>> SetReportDefinition functions", indicating that you are using web
>> services
>> rather than interactively publishing. I understand this -- just consider
>> the above explanation a way to conceptualize *why* the parameters work
>> the
>> way they do and require a separate step, rather than the way you
>> expected.
>> I think that you may need to use the .SetReportParameters method here,
>> explicitly providing the new information to indicate that, yes, you want
>> to
>> change thedefaultvalues. If not, it may be the .SetProperties method.
>> I hope this works for you -- if not, you may be able to use the
>> explanation
>> above to figure out the correct web service approach <s>.
>> >L<
>> <bruc...@.gmail.com> wrote in message
>> news:1175872439.784150.127590@.e65g2000hsc.googlegroups.com...
>>
>> > I've written a rss script file to automate publishing of reports to
>> > our server. I've encountered a problem when republishing reports that
>> > have adefaultvalue set for aparameter.
>> > If I change thedefaultvalue of theparameterin the report rdl and
>> > then try to republish, thatparameter'sdefaultvalue is not getting
>> > changed on the server. If I add or remove parameters then the server
>> > gets updated correctly. It's only if I change thedefaultvalue that
>> > the update is not happening.
>> > I've tried with both the CreateReport and SetReportDefinition
>> > functions. The only way I've gotten this to work is to delete the
>> > report and then republish but this is not an acceptable solution
>> > because it also deletes report history and subscriptions.
>> > I'm using RS2005.
>> > Any help is appreciated.- Hide quoted text -
>> - Show quoted text -
>

problem with OUTPUT + returned recordset

I have written a stored procedure which contains a Select that returns a
recordset,
and returns a pair of OUTPUT values. The Recordset returned is correct, but
I cannot access the OUTPUT args until I close the recordset. I've seen
reference to this in SQL 7, but not for SQL 2000. I need the open recordset
and the return values at the same time. I've tried changing the Recordset
properties from adUseServer to adUseClient etc with no success.
Thanks for any help,
Jack
The following is both sp & vb code to execute.
CREATE PROCEDURE Select_LatestTimeSlice
(@.dataID [int],
@.TS_ID [int] OUTPUT,
@.RowCnt [int] OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.TSID int
DECLARE @.numcnt int
-- get TSID(s) for this dataID value; and recordcount
Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
WHERE DATAID= @.dataID GROUP BY TS_ID
-- get recordset containing all rows where DATAID = this dataID
SELECT * FROM TimeSlices WHERE DATEID= @.dataID
SET @.RowCnt = @.numcnt
SET @.TS_ID = @.TSID
END
GO
=========================
Dim rsdata As ADODB.Recordset
Set rsdata = New ADODB.Recordset
rsdata.CursorLocation = ad_UseServer 'ad_UseClient
rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
rsdata.LockType = adLockReadOnly 'adLockOptimistic
'
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "Select_LatestTimeSlice"
.CommandType = adCmdStoredProc
'
.Parameters("@.dataID") = varDataID
'
' Pull the Trigger......
Set rsdata = .Execute() ' , , adExecuteNoRecords
'----
' when enabled these lines return NULL and I cannot get values
later
'Debug.Print Format(.Parameters("@.TS_ID"))
'Debug.Print Format(.Parameters("@.RowCnt"))
End With
'
With rsdata
' recordset data is correct
Debug.Print Format(.Fields("DATAID")) & " " &
Format(.Fields("TS_ID"))
rsdata.Close
Debug.Print Format(cmd.Parameters("@.TS_ID"))
Debug.Print Format(cmd.Parameters("@.RowCnt"))
End WithThat is the way sql server works. it sends output parameters and return valu
e
in the last packet it returns to the client. See "Parameters Markers" in BOL
.
You have to process or cancel all result sets returned by the stored
procedure before you have access to the return code and output parameter
values.
Instead using the execute method of the command, use the command as the
source of the recordset open method.
Example:
use northwind
go
create procedure dbo.usp_p1
@.sd datetime,
@.ed datetime,
@.rowcnt int output
as
set nocount on
declare @.error int
select
orderid,
customerid,
orderdate
from
dbo.orders
where
orderdate >= convert(varchar(8), coalesce(@.sd, getdate()), 112)
and orderdate < dateadd(day, 1, convert(varchar(8), coalesce(@.ed,
getdate()), 112))
select @.error = @.@.error, @.rowcnt = @.@.rowcount
return @.error
go
-- vb6
Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRs As ADODB.Recordset
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command
Set objRs = New ADODB.Recordset
With objConn
.ConnectionString =
"provider=sqloledb;server=weg-256;database=northwind;integrated security=SSP
I"
.Errors.Clear
.Open
End With
With objCmd
.CommandText = "dbo.usp_p1"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.return_value", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@.sd", adVarChar, adParamInput,
8, "19970701")
.Parameters.Append .CreateParameter("@.ed", adVarChar, adParamInput,
8, "19970731")
.Parameters.Append .CreateParameter("@.rowcnt", adInteger,
adParamOutput)
.ActiveConnection = objConn
End With
With objRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With
objRs.Open objCmd
MsgBox objRs.Fields(0) & " - " & objRs.Fields(1) & " - " & objRs.Fields(2)
MsgBox objCmd.Parameters("@.rowcnt").Value
objRs.Close
objConn.Close
Set objConn = Nothing
Set objCmd = Nothing
Set objRs = Nothing
End Sub
AMB
"hushtech" wrote:

> I have written a stored procedure which contains a Select that returns a
> recordset,
> and returns a pair of OUTPUT values. The Recordset returned is correct, b
ut
> I cannot access the OUTPUT args until I close the recordset. I've seen
> reference to this in SQL 7, but not for SQL 2000. I need the open records
et
> and the return values at the same time. I've tried changing the Recordset
> properties from adUseServer to adUseClient etc with no success.
> Thanks for any help,
> Jack
> The following is both sp & vb code to execute.
> CREATE PROCEDURE Select_LatestTimeSlice
> (@.dataID [int],
> @.TS_ID [int] OUTPUT,
> @.RowCnt [int] OUTPUT)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.TSID int
> DECLARE @.numcnt int
> -- get TSID(s) for this dataID value; and recordcount
> Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
> WHERE DATAID= @.dataID GROUP BY TS_ID
> -- get recordset containing all rows where DATAID = this dataID
> SELECT * FROM TimeSlices WHERE DATEID= @.dataID
> SET @.RowCnt = @.numcnt
> SET @.TS_ID = @.TSID
> END
> GO
> =========================
> Dim rsdata As ADODB.Recordset
> Set rsdata = New ADODB.Recordset
> rsdata.CursorLocation = ad_UseServer 'ad_UseClient
> rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
> rsdata.LockType = adLockReadOnly 'adLockOptimistic
> '
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = cn
> .CommandText = "Select_LatestTimeSlice"
> .CommandType = adCmdStoredProc
> '
> .Parameters("@.dataID") = varDataID
> '
> ' Pull the Trigger......
> Set rsdata = .Execute() ' , , adExecuteNoRecords
> '----
> ' when enabled these lines return NULL and I cannot get values
> later
> 'Debug.Print Format(.Parameters("@.TS_ID"))
> 'Debug.Print Format(.Parameters("@.RowCnt"))
> End With
> '
> With rsdata
> ' recordset data is correct
> Debug.Print Format(.Fields("DATAID")) & " " &
> Format(.Fields("TS_ID"))
> rsdata.Close
> Debug.Print Format(cmd.Parameters("@.TS_ID"))
> Debug.Print Format(cmd.Parameters("@.RowCnt"))
> End With
>|||You could "forget" about using Output parameters and return the values as a
Recordset.
Select TS_ID, COUNT(TS_ID) as rowCnt FROM TimeSlices
WHERE DATAID= @.dataID GROUP BY TS_ID
...and then use the nextRecordset method in your page.
Or keep the method you are using but use getRows to "transfer" your
recordset into an array.
Then close the recordset and access the Output parameters.
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:377AB4EE-033A-4A6A-A87C-5DD8AD355556@.microsoft.com...
>I have written a stored procedure which contains a Select that returns a
> recordset,
> and returns a pair of OUTPUT values. The Recordset returned is correct,
> but
> I cannot access the OUTPUT args until I close the recordset. I've seen
> reference to this in SQL 7, but not for SQL 2000. I need the open
> recordset
> and the return values at the same time. I've tried changing the Recordset
> properties from adUseServer to adUseClient etc with no success.
> Thanks for any help,
> Jack
> The following is both sp & vb code to execute.
> CREATE PROCEDURE Select_LatestTimeSlice
> (@.dataID [int],
> @.TS_ID [int] OUTPUT,
> @.RowCnt [int] OUTPUT)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.TSID int
> DECLARE @.numcnt int
> -- get TSID(s) for this dataID value; and recordcount
> Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
> WHERE DATAID= @.dataID GROUP BY TS_ID
> -- get recordset containing all rows where DATAID = this dataID
> SELECT * FROM TimeSlices WHERE DATEID= @.dataID
> SET @.RowCnt = @.numcnt
> SET @.TS_ID = @.TSID
> END
> GO
> =========================
> Dim rsdata As ADODB.Recordset
> Set rsdata = New ADODB.Recordset
> rsdata.CursorLocation = ad_UseServer 'ad_UseClient
> rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
> rsdata.LockType = adLockReadOnly 'adLockOptimistic
> '
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = cn
> .CommandText = "Select_LatestTimeSlice"
> .CommandType = adCmdStoredProc
> '
> .Parameters("@.dataID") = varDataID
> '
> ' Pull the Trigger......
> Set rsdata = .Execute() ' , , adExecuteNoRecords
> '----
> ' when enabled these lines return NULL and I cannot get values
> later
> 'Debug.Print Format(.Parameters("@.TS_ID"))
> 'Debug.Print Format(.Parameters("@.RowCnt"))
> End With
> '
> With rsdata
> ' recordset data is correct
> Debug.Print Format(.Fields("DATAID")) & " " &
> Format(.Fields("TS_ID"))
> rsdata.Close
> Debug.Print Format(cmd.Parameters("@.TS_ID"))
> Debug.Print Format(cmd.Parameters("@.RowCnt"))
> End With
>|||Alejandro,
Thanks for the solution to my problem. I've implemented it successfully.
You referred me to "Parameters Markers" in BOL. I'm not familiar with what
BOL is and how to find it. Please give me a pointer if you can.
Thanks again for the help. I'm really happy with how quickly responses are
given on this forum - and how accurate and helpful they are.
-- jack
"Alejandro Mesa" wrote:
> That is the way sql server works. it sends output parameters and return va
lue
> in the last packet it returns to the client. See "Parameters Markers" in B
OL.
> You have to process or cancel all result sets returned by the stored
> procedure before you have access to the return code and output parameter
> values.
> Instead using the execute method of the command, use the command as the
> source of the recordset open method.
> Example:
> use northwind
> go
> create procedure dbo.usp_p1
> @.sd datetime,
> @.ed datetime,
> @.rowcnt int output
> as
> set nocount on
> declare @.error int
> select
> orderid,
> customerid,
> orderdate
> from
> dbo.orders
> where
> orderdate >= convert(varchar(8), coalesce(@.sd, getdate()), 112)
> and orderdate < dateadd(day, 1, convert(varchar(8), coalesce(@.ed,
> getdate()), 112))
> select @.error = @.@.error, @.rowcnt = @.@.rowcount
> return @.error
> go
> -- vb6
> Private Sub Command1_Click()
> Dim objConn As ADODB.Connection
> Dim objCmd As ADODB.Command
> Dim objRs As ADODB.Recordset
> Set objConn = New ADODB.Connection
> Set objCmd = New ADODB.Command
> Set objRs = New ADODB.Recordset
> With objConn
> .ConnectionString =
> "provider=sqloledb;server=weg-256;database=northwind;integrated security=S
SPI"
> .Errors.Clear
> .Open
> End With
> With objCmd
> .CommandText = "dbo.usp_p1"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.return_value", adInteger,
> adParamReturnValue)
> .Parameters.Append .CreateParameter("@.sd", adVarChar, adParamInput
,
> 8, "19970701")
> .Parameters.Append .CreateParameter("@.ed", adVarChar, adParamInput
,
> 8, "19970731")
> .Parameters.Append .CreateParameter("@.rowcnt", adInteger,
> adParamOutput)
> .ActiveConnection = objConn
> End With
> With objRs
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
> End With
> objRs.Open objCmd
> MsgBox objRs.Fields(0) & " - " & objRs.Fields(1) & " - " & objRs.Field
s(2)
> MsgBox objCmd.Parameters("@.rowcnt").Value
> objRs.Close
> objConn.Close
> Set objConn = Nothing
> Set objCmd = Nothing
> Set objRs = Nothing
> End Sub
>
> AMB
> "hushtech" wrote:
>