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

Bi-Directional replication without changing scema on subscriber

Hello - I am a newbie to Replication.
Can I setup Bi-Directional replication without introducing the guid column
on the subscriber? All the tables have Primary Keys.
Thanks.
-A
Adam - yes - columns are only added for updatable subsriptions and merge.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Best Way of data transfer

Hello All,
I am a newbie dba and need some expert advice on one of my development
scenario I am currently struck with. I am in process of designing a
reporting solution for my company. It is going to be a web based
intranet thin client multitiered application using sql server 2000 and
..net platform.
This application basically shows the real time KPI's or statistics in
different forms of reports on an hourly basis to the senior managers to
right on their desktop. Eventually these reports will help them in
decision making for the better performance of the business...
My question here is what is the best way of transferring large chunk of
data (not entire table(s)) from production server (SQL 2000) to a
reporting server or staging database with an hourly refresh without
stressing the production environment?
Is it a) Replication preferably snapshot? Or
b) BCP? or
c) DTS?
Or do you suggest any better way of achieving this task?
Any suggestion or tips would be greatly appreciated.
Looking forward for your responses.
Many Thanks,
AK
I'd recommend using transactional replication - after the snapshot, it'll
just take the changes to the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks very much for your reply. Paul,
Is it viable to do hourly refresh through out the day using your
advised approach? Will there be any performance issues?
Thanks very much for your reply.
Ak
Paul Ibison wrote:
> I'd recommend using transactional replication - after the snapshot, it'll
> just take the changes to the data.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log-reader will ov course affect the production system, and if you have
a publisher/distributor, there will be disk access required to write and
read from the distribution database. Exactly what this all amounts to is
difficult to say and is more empirically determined, although the section
entitled "Cost of Transactional Replication at the Publisher" in this
article will give you some idea:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
The other thing to take into account is the effect on reporting queries to
have the distribution agent aplying transactions, and the consequential
potential blocking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I would use transactional replication as it offers the lowest latency.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AK" <arshad.khan@.policyadmin.co.uk> wrote in message
news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
> Hello All,
> I am a newbie dba and need some expert advice on one of my development
> scenario I am currently struck with. I am in process of designing a
> reporting solution for my company. It is going to be a web based
> intranet thin client multitiered application using sql server 2000 and
> .net platform.
> This application basically shows the real time KPI's or statistics in
> different forms of reports on an hourly basis to the senior managers to
> right on their desktop. Eventually these reports will help them in
> decision making for the better performance of the business...
>
> My question here is what is the best way of transferring large chunk of
> data (not entire table(s)) from production server (SQL 2000) to a
> reporting server or staging database with an hourly refresh without
> stressing the production environment?
> Is it a) Replication preferably snapshot? Or
> b) BCP? or
> c) DTS?
> Or do you suggest any better way of achieving this task?
> Any suggestion or tips would be greatly appreciated.
> Looking forward for your responses.
>
> Many Thanks,
> AK
>
|||Thanks very much for all your replies guys.
I will give at a go this way then. Will post more queries on this
thread if i get stuck any where.
AK
Hilary Cotter wrote:[vbcol=seagreen]
> I would use transactional replication as it offers the lowest latency.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "AK" <arshad.khan@.policyadmin.co.uk> wrote in message
> news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...