dynamic SQL stored procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored Procedure. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied." Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text" instead of "Stored Procedure" I got the correct SP answer, but because all the reports get their parameters from the application (user) I can not leave the report like that.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comYou should be able to do this:
exec x @.y and then map the query parameter to the report parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Alot of times this will happen when you do not have the parameter defined in
the dataset. RS takes your parameters but ends up not passing them to the
stored proc because you haven't explicitly told it to in the properites of
the dataset. Check it by going to the appropriate dataset. hit the elipsis
(...)-> parameters tab. You need to list the parameters that the proc takes
in the correct order that it takes them. The name value list would look as
such
NAME VALUE
@.x =Parameters!X.value
@.y = Parameters!Y.Value
See if that helps!
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Dynamic SQL Stored Procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored
Procedure. First, I had to handle the problem that in the Layout Tab
there were no fields by adding them manually. However, when I try to
show the report in the Preview tab I got the error: "Procedure x
Expects a parameter @.y that was not Supplied" Despite the fact that I
define the parameter in report parameter and in the Data Tab I got the
correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text"
instead of "Stored Procedure" I got the correct SP answer, but
because all the reports get their parameters from the application
(user) I can not leave the report like that.
Any Ideas?
Thank you.
AviI've had this problem a few times, and ended up solving it by simply
shutting down and restarting Visual Studio (after saving the report, of
course). Not sure if it will work for your problem, but its worth a
try if you haven't done that already.
AB wrote:
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab
> there were no fields by adding them manually. However, when I try to
> show the report in the Preview tab I got the error: "Procedure x
> Expects a parameter @.y that was not Supplied" Despite the fact that I
> define the parameter in report parameter and in the Data Tab I got the
> correct SP answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but
> because all the reports get their parameters from the application
> (user) I can not leave the report like that.
> Any Ideas?
> Thank you.
> Avi

Dynamic SQL Stored Precedure problem

Hi,

I have a project that all its reports are based on Dynamic SQL Stored Procedures. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied."Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.

When I wrote in the data setquery"execx @.y=1"and chose "Text" instead of"Stored Procedure"I got thecorrect SP answer, but because all the reports gettheir parameters from the application (user)I can not leave the report like that.

Try this: As a data set write static query that returns all the fields of your dynamic query and uses all the parameters. Then refresh report so it will update parameter list and field names. After that you can change static query to dynamic.

I did it like this:

declare @.sql varchar(max); set @.sql = '';

set @.sql = '.....'

exec (@.sql)

Maciej

Dynamic SQL queries

Hi,
I am trying to use dynamic queries in RDL from the data tab in the report
designer. The columns to be selected and the name of the table from which to
select are supplied as report parameters. Can someone give me an example of
how to do this without using stored procedures or UDFs? Is it possible at
all? I am using the reporting services from MS SQL 2000.
Thanks in advance,
PavanI've done this a few times, but then I've used stored procedures or UDF. But
you should be able to do something similar without stored procedures. I've
done something like this with MDX, should work out with SQL queries as well.
I'd try something like this:
4 Parameters - param1, param2, param3 and param4, paramTable
Query:
="Select " & Parameters!param1.Value & " as P1, " & Parameters!param2.Value
& " as P2, " & Parameters!param3.Value & " as P3, " &
Parameters!param4.Value & " as P4 from " & Parameters!paramTable.Value& "
where 1=1"
(Everything needs to be on the "same" line, no line breaks. Test in NotePad
without wordwrap to see that everything is on the same line, even if the
lines break in the editor.)
This should give you a dataset with fields P1, P2, P3 and P4, which you now
can use in your report.
You will need to be sure that all the options are available in all tables.
Or you could do cascading parameters where you choose the table first, and
then the drop down list of all parameters gets filled with valid choices.
Kaisa M. Lindahl Lervik
"Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am trying to use dynamic queries in RDL from the data tab in the report
> designer. The columns to be selected and the name of the table from which
> to select are supplied as report parameters. Can someone give me an
> example of how to do this without using stored procedures or UDFs? Is it
> possible at all? I am using the reporting services from MS SQL 2000.
> Thanks in advance,
> Pavan
>|||One additional point. First have regular SQL so you get your field list.
Then change it as below. Once it is an expression the field list will not
update.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> I've done this a few times, but then I've used stored procedures or UDF.
> But you should be able to do something similar without stored procedures.
> I've done something like this with MDX, should work out with SQL queries
> as well.
> I'd try something like this:
> 4 Parameters - param1, param2, param3 and param4, paramTable
> Query:
> ="Select " & Parameters!param1.Value & " as P1, " &
> Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> " & Parameters!param4.Value & " as P4 from " &
> Parameters!paramTable.Value& " where 1=1"
> (Everything needs to be on the "same" line, no line breaks. Test in
> NotePad without wordwrap to see that everything is on the same line, even
> if the lines break in the editor.)
> This should give you a dataset with fields P1, P2, P3 and P4, which you
> now can use in your report.
> You will need to be sure that all the options are available in all tables.
> Or you could do cascading parameters where you choose the table first, and
> then the drop down list of all parameters gets filled with valid choices.
> Kaisa M. Lindahl Lervik
> "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am trying to use dynamic queries in RDL from the data tab in the report
>> designer. The columns to be selected and the name of the table from which
>> to select are supplied as report parameters. Can someone give me an
>> example of how to do this without using stored procedures or UDFs? Is it
>> possible at all? I am using the reporting services from MS SQL 2000.
>> Thanks in advance,
>> Pavan
>|||Bruce,
I am trying to follow the BOL tutorial on dynamic queries. The expression
that they have will not fit as a one-liner in the generic query designer? Do
you have a work-around so that i can preview the result? Expression below:
="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER JOIN
Person.Contact C " &"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
Parameters!Department.Value) & "ORDER BY C.LastName"
Thanks!
"Bruce L-C [MVP]" wrote:
> One additional point. First have regular SQL so you get your field list.
> Then change it as below. Once it is an expression the field list will not
> update.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> > I've done this a few times, but then I've used stored procedures or UDF.
> > But you should be able to do something similar without stored procedures.
> > I've done something like this with MDX, should work out with SQL queries
> > as well.
> >
> > I'd try something like this:
> >
> > 4 Parameters - param1, param2, param3 and param4, paramTable
> >
> > Query:
> > ="Select " & Parameters!param1.Value & " as P1, " &
> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> > " & Parameters!param4.Value & " as P4 from " &
> > Parameters!paramTable.Value& " where 1=1"
> >
> > (Everything needs to be on the "same" line, no line breaks. Test in
> > NotePad without wordwrap to see that everything is on the same line, even
> > if the lines break in the editor.)
> > This should give you a dataset with fields P1, P2, P3 and P4, which you
> > now can use in your report.
> > You will need to be sure that all the options are available in all tables.
> > Or you could do cascading parameters where you choose the table first, and
> > then the drop down list of all parameters gets filled with valid choices.
> >
> > Kaisa M. Lindahl Lervik
> >
> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> >> Hi,
> >>
> >> I am trying to use dynamic queries in RDL from the data tab in the report
> >> designer. The columns to be selected and the name of the table from which
> >> to select are supplied as report parameters. Can someone give me an
> >> example of how to do this without using stored procedures or UDFs? Is it
> >> possible at all? I am using the reporting services from MS SQL 2000.
> >>
> >> Thanks in advance,
> >> Pavan
> >>
> >
> >
>
>|||Have a report with nothing but your parameters and a textbox. Set your
textbox to the expression so you can see the results.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:42F27824-479E-4B10-A232-083F860313EA@.microsoft.com...
> Bruce,
> I am trying to follow the BOL tutorial on dynamic queries. The expression
> that they have will not fit as a one-liner in the generic query designer?
> Do
> you have a work-around so that i can preview the result? Expression below:
> ="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
> HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
> HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER
> JOIN
> Person.Contact C " &"ON E.ContactID = C.ContactID " &
> Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
> Parameters!Department.Value) & "ORDER BY C.LastName"
> Thanks!
>
> "Bruce L-C [MVP]" wrote:
>> One additional point. First have regular SQL so you get your field list.
>> Then change it as below. Once it is an expression the field list will not
>> update.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
>> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
>> > I've done this a few times, but then I've used stored procedures or
>> > UDF.
>> > But you should be able to do something similar without stored
>> > procedures.
>> > I've done something like this with MDX, should work out with SQL
>> > queries
>> > as well.
>> >
>> > I'd try something like this:
>> >
>> > 4 Parameters - param1, param2, param3 and param4, paramTable
>> >
>> > Query:
>> > ="Select " & Parameters!param1.Value & " as P1, " &
>> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as
>> > P3,
>> > " & Parameters!param4.Value & " as P4 from " &
>> > Parameters!paramTable.Value& " where 1=1"
>> >
>> > (Everything needs to be on the "same" line, no line breaks. Test in
>> > NotePad without wordwrap to see that everything is on the same line,
>> > even
>> > if the lines break in the editor.)
>> > This should give you a dataset with fields P1, P2, P3 and P4, which you
>> > now can use in your report.
>> > You will need to be sure that all the options are available in all
>> > tables.
>> > Or you could do cascading parameters where you choose the table first,
>> > and
>> > then the drop down list of all parameters gets filled with valid
>> > choices.
>> >
>> > Kaisa M. Lindahl Lervik
>> >
>> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
>> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> Hi,
>> >>
>> >> I am trying to use dynamic queries in RDL from the data tab in the
>> >> report
>> >> designer. The columns to be selected and the name of the table from
>> >> which
>> >> to select are supplied as report parameters. Can someone give me an
>> >> example of how to do this without using stored procedures or UDFs? Is
>> >> it
>> >> possible at all? I am using the reporting services from MS SQL 2000.
>> >>
>> >> Thanks in advance,
>> >> Pavan
>> >>
>> >
>> >
>>