February 19, 2013 at 8:43 am
Hi,
I am working on creating a report which should pull data from multiple instances. The instances will be passed in as parameters. I am current doing this for one instance and creating a dynamic connection to the instance using expression ="data source=" &Parameters!servername.Value &";initial catalog=master".
Now I am facing a problem in giving multiple instances names and the report should
1) Create dynamic connection to the fist instance and get the data
2) Check if there are any other instances (in the comma delimited parameters) and if there are more instance names then create the dynamic connection to the next instance and pull the data.
Any suggestions would be appreciated.
February 22, 2013 at 12:11 am
Hi Try this approach, it might help you.
DECLARE @DB varchar(200)='DBName1,DBName2,DBName3......'
,@Server varchar(100)='ServerName'
CREATE TABLE #Test(COL1 varchar(20))
WHILE LEN(@DB)>0
BEGIN
DECLARE @hold varchar(100)
DECLARE @Sql VARCHAR(1000)
IF charindex(',',@DB)>0
BEGIN
SET @hold=SUBSTRING(@DB,1,charindex(',',@DB)-1)
END
ELSE
BEGIN
SET @hold=@DB
END
SET @Sql=''
SET @Sql='insert into #Test select top 10 COL1 from ['+@Server+'].['+@hold+'].dbo.TABLENAM1'
EXECUTE (@sql)
IF charindex(',',@DB)>0
BEGIN
set @DB= STUFF(@DB,1,charindex(',',@DB),'')
END
ELSE
BEGIN
SET @DB=''
END
END
SELECT * FROM #Test
DROP TABLE #Test
Thanks,
Neeraj
February 26, 2013 at 10:45 am
Thanks for the suggestion NeerajStar. But my question was more inclined towards the SSRS aspect. I wanted to know how can I create dynamic connections to the instances based on the parameters and display each instance data in a separate page.
I will be able to separate multiple comma delimit parameters but I am not sure how to dynamically create multiple data sources based on input parameter.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply