Dynamically specify server and database in Stored Procedure

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')?)

Thanks for any help

Hi,

that is not (yet) parameterizable. You would have to use dynamic sql here.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Dynamically Create Connection Managers @ Run time

Is there a way to dynamically create a connection manager @. run time? I would like to do this from a data set of connection strings so I can link them into a union all component.

No. You cannot change package structure at run-time. You can dynamically read information in at run-time, so you can change your connection string for example. The best method is the build in Configuration support. You can also drive most properties through Expressions and supply a variable to set the property. Variables can be set in several ways, including other expressions and script tasks.

To load data from multiple sources, try using the For Each Loop, and drive this off your list of "connections". This can contain a data flow task, the connection of which can be updated on each loop iteration.

Try this article to give you an idea of looping with recordsets.

Shredding a Recordset
(http://www.sqlis.com/default.aspx?59)

Dynamic Subreports

Has anyone been successful in being able to dynamically link subreports in
2005.
Background of what we are doing:
We have a main report that gets passed a set of ID's of a table, then from
there we want it to loop the subreport and based on the type of record, we
want it to bring in a specific subreport.
We have tried toggling the visibility of multiple subreports, except every
time it runs all the subreports for ever record ID even if the record ID
wouldn't return anything. With this approach we would have multiple
subreports over laid and it could be passed an infinite amount of record
ID's and with multiple users it would be an huge amount of hits to the SQL
server. For instance, 2 different users call the same report with 20 record
ID, our trace showed that it hit the SQL server 320 times even though it
only needed to be hit 40 times. We were able to dynamically set subreports
with Data Dynamics ActiveReports and we thought there should be something
comparable with SRS2005.
Has anyone either ran into this same situation or has found a workaround
(besides visibility)?Ok, I have found a way to do what i wanted with out having the all the
subreports run for each record.
First, I created a subreport and created a Parameter that only had one
available value, for instance, the parameter TypeID and the available is set
to one. Then in the main report, I set the subreport to pass the TypeID of
the recordset to the subreport. If it didn't match the TypeID in the
subreport it didn't run the report. Then I set the NoRow equal to a space
and it didn't show anything then. It works perfectly, now if the main
recordsets TypeID matches the available TypeID of the subreport it runs
against the sql server, otherwise it doesn't run the subreport. I then over
laided the 8 subreports and it is working perfectly.
Just thought I would pass this information along on how I was able to work
around the issue.
Thanks, Terry.
"Microsoft" <tnederveld@.ezlistmls.com> wrote in message
news:OmrnGPfHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Has anyone been successful in being able to dynamically link subreports in
> 2005.
> Background of what we are doing:
> We have a main report that gets passed a set of ID's of a table, then from
> there we want it to loop the subreport and based on the type of record, we
> want it to bring in a specific subreport.
> We have tried toggling the visibility of multiple subreports, except every
> time it runs all the subreports for ever record ID even if the record ID
> wouldn't return anything. With this approach we would have multiple
> subreports over laid and it could be passed an infinite amount of record
> ID's and with multiple users it would be an huge amount of hits to the SQL
> server. For instance, 2 different users call the same report with 20
> record ID, our trace showed that it hit the SQL server 320 times even
> though it only needed to be hit 40 times. We were able to dynamically set
> subreports with Data Dynamics ActiveReports and we thought there should be
> something comparable with SRS2005.
> Has anyone either ran into this same situation or has found a workaround
> (besides visibility)?
>