Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple Dymanic connection strings Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 8:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:58 AM
Points: 64, Visits: 268
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.


Post #1421684
Posted Friday, February 22, 2013 12:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 13, 2014 6:37 AM
Points: 112, Visits: 102
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
Post #1422894
Posted Tuesday, February 26, 2013 10:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 9:58 AM
Points: 64, Visits: 268
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.
Post #1424181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse