Hard-coded SSRS datasource not working in SSMS
Hi... Using Report Designer to design a report inside SSMS..
Server A contains a database of database growth statistics for all servers...
I have a SSRS report to present this data, using a datasource of "Server A."
In preview mode, report works against all servers and database combinations.
But when I use the report in SSMS (I right-click database and select Custom Report).,
1. Running the report against a database on Server A works fine.
2. Any database on Servers B and C report they cannot find the stats database.
My report's local datasource specifically references Server A at design-time, but tracing reveals at run-time it looks to itself for the stats database.
How can I prevent a datasource from deviating from where I specified?
November 2nd, 2010 3:28pm
So here's a scaled down version of the report, where I just pull @@ServerName from ServerA:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<rd:DataSourceID>7d164478-7ca7-4c1c-955c-52c3a3211497</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=ServerA;Initial Catalog=master</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<InteractiveHeight>11in</InteractiveHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<RightMargin>1in</RightMargin>
<LeftMargin>1in</LeftMargin>
<BottomMargin>1in</BottomMargin>
<rd:ReportID>1da7dd92-2bcb-4c1e-9d5f-9653448a69dc</rd:ReportID>
<DataSets>
<DataSet Name="ServerName_Dataset">
<Fields>
<Field Name="ServerName">
<DataField>ServerName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>SELECT @@ServerName AS ServerName</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<Body>
<ReportItems>
<Textbox Name="ServerName">
<rd:DefaultName>ServerName</rd:DefaultName>
<Top>0.375in</Top>
<Width>2.625in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<CanGrow>true</CanGrow>
<Left>0.25in</Left>
<Height>0.25in</Height>
<Value>=First(Fields!ServerName.Value)</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
When I use SSMS to run the report on any database on ServerB, I get a report with ServerB's name.
I was hoping to retrieve ServerA's name, regardless which server/database I run the report against.
Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2010 8:02am
So here's a scaled down version of the report, where I just pull @@ServerName from ServerA:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<rd:DataSourceID>7d164478-7ca7-4c1c-955c-52c3a3211497</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>Data Source=ServerA;Initial Catalog=master</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<InteractiveHeight>11in</InteractiveHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<RightMargin>1in</RightMargin>
<LeftMargin>1in</LeftMargin>
<BottomMargin>1in</BottomMargin>
<rd:ReportID>1da7dd92-2bcb-4c1e-9d5f-9653448a69dc</rd:ReportID>
<DataSets>
<DataSet Name="ServerName_Dataset">
<Fields>
<Field Name="ServerName">
<DataField>ServerName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>SELECT @@ServerName AS ServerName</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<Body>
<ReportItems>
<Textbox Name="ServerName">
<rd:DefaultName>ServerName</rd:DefaultName>
<Top>0.375in</Top>
<Width>2.625in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<CanGrow>true</CanGrow>
<Left>0.25in</Left>
<Height>0.25in</Height>
<Value>=First(Fields!ServerName.Value)</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
When I use SSMS to run the report on any database on ServerB, I get a report with ServerB's name.
I was hoping to retrieve ServerA's name, regardless which server/database I run the report against.
It's as if the connection string is replaced with "localhost" at run-time.
November 3rd, 2010 8:09am
When you use custom reports in SSMS they use the server you are running them against as the data source.
For example - if I cut and paste your code and save it on my server - then run it I will get the name of my server back - I do not have any ServerA or B on my network.
You will get the same results if you empty the <ConnectString>Data Source=ServerA;Initial Catalog=master</ConnectString>
value to <ConnectString></ConnectString>
Seth
http://lqqsql.wordpress.com
Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2010 11:50am
That's too bad....was waiting for second opinion. Thanks for validating.
November 4th, 2010 10:49am
This topic is archived. No further replies will be accepted.
Other recent topics
