Microsoft Reporting Services - Error - Expects Parameter - Stored Procedure

Solution:
This happens to me quite a bit...its because the designer removes the
code that defines what
values are passed to your stored procedure.
You have to select the report you're working on > Right-Click 'Code' >
Locate the <Query> XML tag...and you'll have to re-define the
<QueryParameters>.
<Query>
<DataSourceName>GPS</DataSourceName>
<CommandType>StoredProcedure</CommandType>
<CommandText>spLRGetLeadRotationSummary</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.RegionID">
<Value>=Parameters!RegionID.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>You can also do this by clicking on the ..., parameters tab. Modifying the
xml is dangerous. Although if you have a lot of query parameters and RS
keeps losing it then I have sometimes saved the section off so I could copy
it back in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<petejk@.gmail.com> wrote in message
news:1135124852.986963.63390@.f14g2000cwb.googlegroups.com...
> Solution:
> This happens to me quite a bit...its because the designer removes the
> code that defines what
> values are passed to your stored procedure.
> You have to select the report you're working on > Right-Click 'Code' >
> Locate the <Query> XML tag...and you'll have to re-define the
> <QueryParameters>.
>
> <Query>
> <DataSourceName>GPS</DataSourceName>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>spLRGetLeadRotationSummary</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.StartDate">
> <Value>=Parameters!StartDate.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.EndDate">
> <Value>=Parameters!EndDate.Value</Value>
> </QueryParameter>
> <QueryParameter Name="@.RegionID">
> <Value>=Parameters!RegionID.Value</Value>
> </QueryParameter>
> </QueryParameters>
> </Query>
>

Microsoft Reporting Services

Default value or value provided for the report parameter 'ParamaterName' is
not a valid value. (rsInvalidReportParameter)
I am getting this error when I am trying to run a deployed report.
However,
this is driving me mad because in the report preview within VS.Net the
report
works fine and i can choose the same parameter settings from within the dev
environment with no problems. I am populating this parameter with a
dropdown
with valid values from a sproc (and this I've done a thousand times before)
.
I can not figure out why it is throwing this error, particularly since it
works perfectly fine in the preview pane. How could it be crappin out when
I
deploy it?!?! I am going insane!
Pl. put in forum.
regards
Arvind Sharma
--
Message posted via http://www.sqlmonster.comOn the deployed report, in report manager, properties... is the prompt string
blank? If so it will try to render the report without prompting for input
which will throw that error.
"arvind sharma via SQLMonster.com" wrote:
> Default value or value provided for the report parameter 'ParamaterName' is
> not a valid value. (rsInvalidReportParameter)
> I am getting this error when I am trying to run a deployed report.
> However,
> this is driving me mad because in the report preview within VS.Net the
> report
> works fine and i can choose the same parameter settings from within the dev
> environment with no problems. I am populating this parameter with a
> dropdown
> with valid values from a sproc (and this I've done a thousand times before)
> ..
> I can not figure out why it is throwing this error, particularly since it
> works perfectly fine in the preview pane. How could it be crappin out when
> I
> deploy it?!?! I am going insane!
> Pl. put in forum.
> regards
> Arvind Sharma
> --
> Message posted via http://www.sqlmonster.com
>sql

Metadata for SP parameter default values

I am looking for a way to list any default values for stored procedure
parameters.
For example with this stored procedure:
CREATE PROC dbo.pr_GetSomeData
(
@.SomeInt int,
@.AnotherInt int = 0,
@.SomeDate datetime = NULL
)
I'd like to be able to create a table that would look like this
ParameterName DefaultValue
@.SomeInt
@.AnotherInt 0
@.SomeDate NULL
I can get at the parameters themselves through information_schema.PARAMETERS
but I don't see a way to get at the default value.
Any suggestions would be appreciated.
Joel Reinford
Data Management Solutions LLCSQL Server does not store information about this in any sys table. One
possible solution could be parsing the sp code from syscomment or
information_schema.routines for the sp in question.
AMB
"Joel Reinford" wrote:

> I am looking for a way to list any default values for stored procedure
> parameters.
> For example with this stored procedure:
> CREATE PROC dbo.pr_GetSomeData
> (
> @.SomeInt int,
> @.AnotherInt int = 0,
> @.SomeDate datetime = NULL
> )
> I'd like to be able to create a table that would look like this
> ParameterName DefaultValue
> @.SomeInt
> @.AnotherInt 0
> @.SomeDate NULL
> I can get at the parameters themselves through information_schema.PARAMETE
RS
> but I don't see a way to get at the default value.
> Any suggestions would be appreciated.
>
> Joel Reinford
> Data Management Solutions LLC
>
>

Meta Data Help Needed[:(]

Hi Guys,

I have a DataBase in which I have several Tables.

What I want is an SP or Query which takes as its parameter the "tablename".

The Output Should be a having three fields only.

Field name, DataType Of the Field, Length of the DataType.

For Example

Suppose the StoredProcedure Name is "SP_GetTables"

if i have a table named "tbl_Users" with fields

UserName varchar(50)

UserPass varchar(20)

UserAge int

UserStatus bit

In my program side if I pass the parameter as "tbl_Users" to the StoredProcedure SP_Users,

I should get the O/P as

Field Name DataType Length

UserName varchar 50

UserPass varchar 20

UserAge int

UserStatus bit

Regards,

Naveen.

Here it is,

Createproc SP_GetTables(@.TableNameSysname)
as
Begin
Select
column_name [field name]
, data_type [datatype]
, character_maximum_length [length]
From
INFORMATION_SCHEMA.COLUMNS
Where
table_name= @.tablename
Orderby
ordinal_position
End

|||

HiManivannna.D.Sekaran,

Thanks a lot!!!

Is there a way by which I can pass the DataBase name too.

My requirement is like this.

I want an SP where i can pass DBName and Table Name as Parameters and that SP finds the DB and the table inside it.

The SP must reside in My DB too.

Regards,

Naveen

|||

Createproc SP_GetTables(@.TableNameSysname, @.databaseSysname)
as
Begin
Exec('Select
column_name [field name]
, data_type [datatype]
, character_maximum_length [length]
From
['+ @.database+'].INFORMATION_SCHEMA.COLUMNS
Where
table_name = '''+ @.tablename+'''
Order by
ordinal_position')
End