Multiple Dymanic connection strings

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services 2008 Development » Multiple Dymanic connection strings

Multiple Dymanic connection strings

Rate Topic

Display Mode

Topic Options

Author

Message

Tempdb12

Posted Tuesday, February 19, 2013 8:43 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:43 AM
Points: 52, Visits: 236

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

neerajstar

Posted Friday, February 22, 2013 12:11 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:13 AM
Points: 80, Visits: 83

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

Tempdb12

Posted Tuesday, February 26, 2013 10:45 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 11:43 AM
Points: 52, Visits: 236

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 »

Permissions