Bind a Table to a StoredProcedure

Hi All
I am an RS Newbie - Please be gentle :-))
I have a table on an RS report
When the table is bound to a simple Select statement EG select * from
Customers
I can then further bind each control on the report to a field in the
recordset.
EG I select the relevant text box - then from the property sheet I select
from the list of available fields
in the 'Value' combo box on the property sheet
EG txtCustomer value in the property sheet = Fields!CustomerName.Value
Cool
But when I change the recordset for the table to a Stored Proc
I see no available fields when I select a Text box and try to bind it to a
value in the propertyy sheet
Infact rather than a list of available fields, all I can see is <Expression>
Don't know where to go from here - any suggestions appreciated
Many thanks
DenzilSometimes when you switch to a stored procedure it does not detect the field
list. Try the following. Hopefully one of these two will do the trick.
1. in the dataset view click on the refresh fields button (it is to the
right of the ... and looks like the refresh button from IE
2. Make sure the command type is stored procedure and just put in the name
of the stored procedure like this: MyStoredProcName
Not like this: Exec MyStoredProcName
After doing this then try #1 again.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c3160a2829f0@.uwe...
> Hi All
> I am an RS Newbie - Please be gentle :-))
> I have a table on an RS report
> When the table is bound to a simple Select statement EG select * from
> Customers
> I can then further bind each control on the report to a field in the
> recordset.
> EG I select the relevant text box - then from the property sheet I select
> from the list of available fields
> in the 'Value' combo box on the property sheet
> EG txtCustomer value in the property sheet = Fields!CustomerName.Value
> Cool
> But when I change the recordset for the table to a Stored Proc
> I see no available fields when I select a Text box and try to bind it to a
> value in the propertyy sheet
> Infact rather than a list of available fields, all I can see is
> <Expression>
> Don't know where to go from here - any suggestions appreciated
> Many thanks
> Denzil|||Hi Bruce
Many thanks for the swift reply
When I read the bit about changing the command type I thought that would be
it
But...sorry none of this worked <sigh>
When I change the Command Type to StoredProc and remove the 'exec' at the
front of the
query string then try and run it - it errors out and does not give me the
expected popup box where I would manually enter any parameters
The error is
"An error occurred whilst trying to retrieve the parameters in the query
rsGetAccountList @.ActiveStatus does not exist"
before, I used to have "exec rsGetAccountList @.ActiveStatus" in the dataset
view and had the command type as Text. With that setup I could run the query
in DataSet view and get my parameter popup box
Any more suggestions? :-))
Many thanks
Denzil
Bruce L-C [MVP] wrote:
>Sometimes when you switch to a stored procedure it does not detect the field
>list. Try the following. Hopefully one of these two will do the trick.
>1. in the dataset view click on the refresh fields button (it is to the
>right of the ... and looks like the refresh button from IE
>2. Make sure the command type is stored procedure and just put in the name
>of the stored procedure like this: MyStoredProcName
>Not like this: Exec MyStoredProcName
>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>[quoted text clipped - 23 lines]
>> Denzil|||Did you put this? rsGetAccountList @.ActiveStatus
If so, remove th @.ActiveStatus. Just put the name of the stored procedure.
RS automatically retrieves the parameter list from the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil" <u18940@.uwe> wrote in message news:5c319c74a8f20@.uwe...
> Hi Bruce
> Many thanks for the swift reply
> When I read the bit about changing the command type I thought that would
> be
> it
> But...sorry none of this worked <sigh>
> When I change the Command Type to StoredProc and remove the 'exec' at the
> front of the
> query string then try and run it - it errors out and does not give me the
> expected popup box where I would manually enter any parameters
> The error is
> "An error occurred whilst trying to retrieve the parameters in the query
> rsGetAccountList @.ActiveStatus does not exist"
> before, I used to have "exec rsGetAccountList @.ActiveStatus" in the
> dataset
> view and had the command type as Text. With that setup I could run the
> query
> in DataSet view and get my parameter popup box
> Any more suggestions? :-))
> Many thanks
> Denzil
>
> Bruce L-C [MVP] wrote:
>>Sometimes when you switch to a stored procedure it does not detect the
>>field
>>list. Try the following. Hopefully one of these two will do the trick.
>>1. in the dataset view click on the refresh fields button (it is to the
>>right of the ... and looks like the refresh button from IE
>>2. Make sure the command type is stored procedure and just put in the name
>>of the stored procedure like this: MyStoredProcName
>>Not like this: Exec MyStoredProcName
>>After doing this then try #1 again.
>> Hi All
>> I am an RS Newbie - Please be gentle :-))
>>[quoted text clipped - 23 lines]
>> Denzil|||Thanks Bruce
yes that was the answer
Many many thanks for your assistance
I really appreciate it
have agreat day
Darren
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1|||No problem. Support is strong for stored procedures but some of what you
need to do is not intuitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Denzil via SQLMonster.com" <u18940@.uwe> wrote in message
news:5c4128a5414d2@.uwe...
> Thanks Bruce
> yes that was the answer
> Many many thanks for your assistance
> I really appreciate it
> have agreat day
> Darren
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1sql