0

I'm developing a ASP.NET web application that displays some SSRS reports. My application's database is located in a different server from my report's database, considering that reports consume data from both servers there's a linked server in report database server to application's server.

In one of dataset reports I have a query as bellow:

SELECT T.column1, T1.column2 FROM localTable T JOIN [linkedServer].[databasename].tablename T1 ON T.id = T1.id

There's only 3 reports, so it was ok to replicate [linkedServer].[databasename] when i changed the application to another environment.

My question is: what's the best way to parameterize [linkedServer].[databasename] in order not to need replace all occurrences every time the server and/or database names change?

Antonio Correia
  • 1,093
  • 1
  • 15
  • 22
  • 1
    One way is dynamic sql. And I can't think of any others. – Tab Alleman Feb 09 '15 at 21:51
  • If you are using the stored procedure then dynamic sql with `ssrs` parameters and if query then you can directly incorporate `ssrs` parameters in that query – Mahesh Feb 10 '15 at 06:31

1 Answers1

2

next to the query field in the dataset, there is a little fx which allows you to enter an expression for the query.

from your example the query would be

= "SELECT T.column1, T1.column2 FROM localTable T  JOIN [" + Parameters!ReportParameter2.Value + "].[databasename].tablename T1 ON T.id = T1.id"

or sth in the like.

in a off topic note i would also recommend to never use the server.database.owner.table syntax, but openquery(server, 'query'). this is for performance reasons, as the server.database.owner.table will copy the whole table over and the openquery retrieves only the result of the query.

busytools
  • 361
  • 1
  • 6