Can I See What A Parameter is Passing to a Sproc?

Since you cannot access the database, what you can do is first you can give
litral values for the stored proc in the Data tab and see whether it works
and remove one by one and see whether it works..

I feel the problem is due to some Null values which may not have been
handled in the stored proc.

Amarnath

"Liz" wrote:

> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
>
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
>
> Any troubleshooting ideas for parameters would be welcome.
>
> Thanks,
> Liz

Everything works fine in the Data tab. However, it fails in the Preview
tab. To test the parameters, I tweaked the sproc to only return what it had
been sent in parameters. I created another report to display what it had
sent to the sproc. Everything checks out. The report is sending what it's
supposed to. So now I'm back to the original report.

How can I troubleshoot the Preview? I suspect there's something in the
stored procedure that it doesn't like but that would be hard to narrow down.

The other thing might be that I orignally created this sproc and report with
one parameter - the one that works. I then added the other three - they
don't work. Do you have add all the parameters at the outset? Can you not
add parameters later?

What kind of null values are you talking about? Null values in the data?
Or null values in the parameters? There are no nulls in the parameters. I
will now go and make sure I'm not sending any nulls back to RS.

Thanks.
"Amarnath" wrote:

> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
>
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
>
> Amarnath
>
> "Liz" wrote:
>
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz

I just went and bulletproofed every field to make sure that a null would not
be returned to RS and that, unfortunately, made no difference.

I'm desperate for any ideas you or anyone might have.

Thanks.

"Amarnath" wrote:

> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
>
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
>
> Amarnath
>
> "Liz" wrote:
>
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz

My apologies if this is a double-post but I just bulletproofed all the fields
to make sure that no nulls are returned to RS but that didn't make any
difference.

So I'm still searching for ideas on this one.

"Amarnath" wrote:

> Since you cannot access the database, what you can do is first you can give
> litral values for the stored proc in the Data tab and see whether it works
> and remove one by one and see whether it works..
>
> I feel the problem is due to some Null values which may not have been
> handled in the stored proc.
>
> Amarnath
>
> "Liz" wrote:
>
> > I've got a report with 4 parameters to a stored procedure. The parameters
> > all work in the stored procedure when I run it in Query Analyzer, but only
> > one of them works in the report.
> >
> > Is there a way to see what's being passed to the sproc? I don't have admin
> > rights to the DB so I can't use the profiler.
> >
> > Any troubleshooting ideas for parameters would be welcome.
> >
> > Thanks,
> > Liz

>>
Do you have add all the parameters at the outset? Can you not
add parameters later?
<<

Yes you can definitely add parameters later. However sometimes there is
something subtly different in the *way* you added the other three that might
be making a difference.

I would look at the code (the XML) instead of trying to handle this in the
Designer. Take a look at all of the parameter definitions and find out what
makes that one different from the others.

There also might be something different about the params in the sproc -- for
example, do they all have default values, or only one/some?

FWIW -- in your other message you say you " just bulletproofed all the
fields to make sure that no nulls are returned to RS" but I don't think
Amarnath was concerned about nulls RETURNED to RS. I think s/he was
concerned about the way the *sproc* might handle a null. I know you said
that there are no nulls in the params you are sending -- just clarifying.

Is there any possibility we have a type or casting issue here?

>L<




"Liz" wrote in message
news64A0349-6597-4A1D-9C09-51DE7B9FD0BA@microsoft.com...
> Everything works fine in the Data tab. However, it fails in the Preview
> tab. To test the parameters, I tweaked the sproc to only return what it
> had
> been sent in parameters. I created another report to display what it had
> sent to the sproc. Everything checks out. The report is sending what
> it's
> supposed to. So now I'm back to the original report.
>
> How can I troubleshoot the Preview? I suspect there's something in the
> stored procedure that it doesn't like but that would be hard to narrow
> down.
>
> The other thing might be that I orignally created this sproc and report
> with
> one parameter - the one that works. I then added the other three - they
> don't work. Do you have add all the parameters at the outset? Can you
> not
> add parameters later?
>
> What kind of null values are you talking about? Null values in the data?
> Or null values in the parameters? There are no nulls in the parameters.
> I
> will now go and make sure I'm not sending any nulls back to RS.
>
> Thanks.
> "Amarnath" wrote:
>
>> Since you cannot access the database, what you can do is first you can
>> give
>> litral values for the stored proc in the Data tab and see whether it
>> works
>> and remove one by one and see whether it works..
>>
>> I feel the problem is due to some Null values which may not have been
>> handled in the stored proc.
>>
>> Amarnath
>>
>> "Liz" wrote:
>>
>> > I've got a report with 4 parameters to a stored procedure. The
>> > parameters
>> > all work in the stored procedure when I run it in Query Analyzer, but
>> > only
>> > one of them works in the report.
>> >
>> > Is there a way to see what's being passed to the sproc? I don't have
>> > admin
>> > rights to the DB so I can't use the profiler.
>> >
>> > Any troubleshooting ideas for parameters would be welcome.
>> >
>> > Thanks,
>> > Liz

Liz wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
>
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
>
> Any troubleshooting ideas for parameters would be welcome.
>
> Thanks,
> Liz


Aside from the SQL Profiler (as you mentioned), I would suggest
creating a stored procedure that just accepts the 4 parameter values
and inserts them into a basic table with 4 varchar/nvarchar defined
columns. Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant

On Apr 24, 11:42 am, Liz wrote:
> I've got a report with 4 parameters to a stored procedure. The parameters
> all work in the stored procedure when I run it in Query Analyzer, but only
> one of them works in the report.
>
> Is there a way to see what's being passed to the sproc? I don't have admin
> rights to the DB so I can't use the profiler.
>
> Any troubleshooting ideas for parameters would be welcome.
>
> Thanks,
> Liz

Oh, I forgot, you could also create 4 textbox controls in the report
and add an expression similar to this for each one that way it will
display the values in the report itself.
=Parameters!Parameter1Name.Value
Also, if the SP is erroring out, temporarily replace it with a basic
query that definitely works. Hope this is helpful.

Regards,

Enrique Martinez
Sr. Software Consultant