G
Geoffrey Barnes
I have found a number of posts related to this problem, but no real
solutions to it. After playing around for a while, I stumbled onto
something that worked for me. I thought I would post it so that it would
get archived on Google. Maybe it will help somebody else someday.
Here is the gist of the problem. I have an Access Data Project in Access
XP, and am using SQL Server 2000. I have a report that is based on a stored
procedure. The procedure originally took only a single parameter, and the
report worked just fine. I got the desired parameter value from the user
and stored it in a variable. Then I just set the report's RecordSource
property to "EXECUTE stpRptDetailed @AgencyKey = " & lngUserAgency, and
everything was good. But do take note of the fact that there are spaces on
either side of the "=" character. That fact will become important later on.
Then I had to add a second parameter to the stored procedure. No problem, I
thought. I will just set the RecordSource to "EXECUTE stpRptDetailed
@AgencyKey = " & lngUserAgency & ", @ShowReconciled = " & bitUserShow. Only
this did not work. The error I got from this was "Must pass parameter
number 2 and subsequent parameters as '@name = value'. After the form '@name
= value' has been used, all subsequent parameters must be passed in the form
'@name = value'. This made no sense to me, since I was already using the
@name format for all my parameters. Just to be sure, I dumped the reports
RecordSource value into a Debug.Print, and it came out in exactly that
format. The only thing that I could think of that might be messing it up
were the spaces on either side of the equal signs. So I edited my VB
programming to exclude the spaces. And this is where things got really
interesting.
Now, when I tried to run the report, I got a new error, "Provider command
for the child rowset does not produce a rowset". I searched Google groups,
and I saw where any number of people have posted about this error when (a)
using an Access Data Project, (b) basing a report on a stored procedure, and
(c) having that procedure accept parameters. Nobody was offering much help,
though. One guy found that his problem went away when he included SET
NOCOUNT ON at the start of his stored procedure, but that's a virtual
requirement for any stored procedure used in an ADP. My procedure already
had SET NOCOUNT ON in it, and it still wasn't working.
Just like all the posters I found on Google, my stored procedure would run
just fine in the Query Analyzer. In fact, I found that could dump the
RecordSource property from my ADP report into the immediate window, copy it,
paste it directly into the QA, and it would run just fine. So I couldn't
figure out why the ADP report kept saying that it wasn't getting a rowset,
when the QA was getting all the rows it asked for. But it was the earlier
error that gave me the clue I needed to find a solution.
It turns out that when you have a report based on a stored procedure, and
that stored procedure has more than one parameter, you can't use the '@name
= value' format any longer. It works fine for passing a single parameter.
But once I expanded out to two parameters, the report failed. I found that
everything worked when I changed my RecordSource to "EXECUTE stpRptDetailed
& " & lngUserAgency & ", " & bitUserShow. Of course, you have to be sure
to pass those parameters in the same order as the stored procedure expects
them to be, but at least the report works now.
Hope this helps someone.
solutions to it. After playing around for a while, I stumbled onto
something that worked for me. I thought I would post it so that it would
get archived on Google. Maybe it will help somebody else someday.
Here is the gist of the problem. I have an Access Data Project in Access
XP, and am using SQL Server 2000. I have a report that is based on a stored
procedure. The procedure originally took only a single parameter, and the
report worked just fine. I got the desired parameter value from the user
and stored it in a variable. Then I just set the report's RecordSource
property to "EXECUTE stpRptDetailed @AgencyKey = " & lngUserAgency, and
everything was good. But do take note of the fact that there are spaces on
either side of the "=" character. That fact will become important later on.
Then I had to add a second parameter to the stored procedure. No problem, I
thought. I will just set the RecordSource to "EXECUTE stpRptDetailed
@AgencyKey = " & lngUserAgency & ", @ShowReconciled = " & bitUserShow. Only
this did not work. The error I got from this was "Must pass parameter
number 2 and subsequent parameters as '@name = value'. After the form '@name
= value' has been used, all subsequent parameters must be passed in the form
'@name = value'. This made no sense to me, since I was already using the
@name format for all my parameters. Just to be sure, I dumped the reports
RecordSource value into a Debug.Print, and it came out in exactly that
format. The only thing that I could think of that might be messing it up
were the spaces on either side of the equal signs. So I edited my VB
programming to exclude the spaces. And this is where things got really
interesting.
Now, when I tried to run the report, I got a new error, "Provider command
for the child rowset does not produce a rowset". I searched Google groups,
and I saw where any number of people have posted about this error when (a)
using an Access Data Project, (b) basing a report on a stored procedure, and
(c) having that procedure accept parameters. Nobody was offering much help,
though. One guy found that his problem went away when he included SET
NOCOUNT ON at the start of his stored procedure, but that's a virtual
requirement for any stored procedure used in an ADP. My procedure already
had SET NOCOUNT ON in it, and it still wasn't working.
Just like all the posters I found on Google, my stored procedure would run
just fine in the Query Analyzer. In fact, I found that could dump the
RecordSource property from my ADP report into the immediate window, copy it,
paste it directly into the QA, and it would run just fine. So I couldn't
figure out why the ADP report kept saying that it wasn't getting a rowset,
when the QA was getting all the rows it asked for. But it was the earlier
error that gave me the clue I needed to find a solution.
It turns out that when you have a report based on a stored procedure, and
that stored procedure has more than one parameter, you can't use the '@name
= value' format any longer. It works fine for passing a single parameter.
But once I expanded out to two parameters, the report failed. I found that
everything worked when I changed my RecordSource to "EXECUTE stpRptDetailed
& " & lngUserAgency & ", " & bitUserShow. Of course, you have to be sure
to pass those parameters in the same order as the stored procedure expects
them to be, but at least the report works now.
Hope this helps someone.