Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

SSRS TABLENAME

Hi,
I have a code that is used both for Dev environment and Prod on my rdl file but have to just change te same table name at multiple places in the code. Meaning if prod uses tablename b i have to change b.columnname in the entire code and if Dev environment uses q i have to change q. Columnname in the entire code.Now what I am told to do is that instead of changing the table names in the code come up with something in the rdl file where the table name could be passed without changing the code. Not sure how to do that but if possible could you guide me step by step as its a
Completely new thing for me.
0
Josh2442
Asked:
Josh2442
  • 2
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>but have to just change te same table name at multiple places in the code.
A vastly better idea would be to have all object names the same in whatever database you're working in, and only change the source database when migrating from dev to whatever to prod.
0
 
Josh2442Author Commented:
I'm sorry Jim i have to change DB name not table name. Sorry about the confusion. Is there an option for this?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste the code you have in your SSRS report into this question, as I'd like to see if you're hard-coding database names in it.

Under normal circumstances you should just be able to double-click on the data source, click on the Edit... button, change the database, save, and deploy.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Josh2442Author Commented:
Yes that's what they don't want. They want it to change database with the help of a parameter
0
 
ValentinoVBI ConsultantCommented:
"They want it to change database with the help of a parameter"

Assuming you're referring to a report parameter, and assuming that your databases are located on the same SQL Server instance, you could create a dataset with an IF statement as follows:

if @param = 1
	select some_fields from FirstDB.YourSchema.YourTable
else
	select some_fields from SecondDB.YourSchema.YourTable

Open in new window

Your report will contain a parameter that displays a list of possible databases, with string/integer combinations for easy usage, for instance Label 'DB1' could match with Value 1 which is used in the dataset.

If your databases are on different instances then you'll have to set up linked servers to make that work.  Once the linked servers are set up, you can just use an IF as explained above, only the FROM clause of the select statement will contain an additional component:

from LinkedServer.SecondDB.YourSchema.YourTable

Open in new window

0
 
ValentinoVBI ConsultantCommented:
Why the B-rating?
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now