Access multiple databases with dynamic connection string

• Kawior

  Valued Member

  Points: 74

  More actions

  May 14, 2012 at 10:18 am

  #268864

  Hello;

  I have about 20 databases for which I like to run reports, same structure.

  Dynamic connection string works fine with Parameters!Databes.Value

  However when I change this Parameter to Allow Multiple Values I'm having an "error during processing of the ConnectString expression of data source".

  Is that anyway to overcome this error and be able to show records from different dbases grouped by database name?

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 14, 2012 at 11:59 am

  #1487299

  I do not know how to do this in SSRS, but I have occasionally done it in SQL Server with a set of Unioned Views.

  How many different objects (tables or views) do you reference in each database?

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• Kawior

  Valued Member

  Points: 74

  More actions

  May 14, 2012 at 1:00 pm

  #1487337

  I am using unions to get data from about 10 tables for one database

  Now to union this in each of my databases is a lot of queries but works

  Have you tried to implement a parameter in query in ssrs which refers to database.

  Query like select * from [Dbase].[dbo].[Table]

  I tried select * from [@Database].[dbo].[Table]

  but that doesn't work either.

  Any other ideas to make this thing simpler.

  Thank you for your input.