SSRS Stored Procedure (Timing Out?). Using too many Linked Servers?

We are using one shared data source ds1 that connects to a server main_server which holds all the stored procedures to generate our reports.

One of the stored procedures usp1 simply executes another stored procedure on another server another_server:

exec another_server.another_database.dbo.usp1

When I execute usp1 on main_server, everything works fine. It runs at almost the same speed as if I had just gone to开发者_StackOverflow another_server and executed from there.... Which is what I expected...

However, when I try to run the report from our report server, it fails. When I watch it run, it almost immediately goes into suspended mode and stays there.

We have all of our timeouts set to 40 minutes, so that shouldn't be our issue, since the report runs in 15. Any thoughts?

Thanks...

---

Maybe the account reporting services uses to run the report doesnt have the correct access or rights to run an sp on another server?

---

With linked servers your stored procedure may copy ALL the data it needs before doing any joins.

Check out: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/06/30/linked-server-performance-heterogeneous-databases.aspx