Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ZMike
Posting Yak Master
110 Posts |
Posted - 2013-01-29 : 22:25:40
|
I'm setting up some queries that will be running against a couple SSRS Servers. We have some that are 2005 and 2008.Here is what I haveSET @SSRSReportDesigner = '''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner''' IF (@RealServerName = 'x') SET @SSRSDefinition = '''http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition''' ELSE SET @SSRSDefinition = '''http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition''' SET @SSRSPlatform = ';WITH XMLNAMESPACES ( DEFAULT ' + @SSRSDefinition + ',' + @SSRSReportDesigner + ' AS rd )'After that then I have this : It prints exactly like the hard coded statement but if I switch the ;With XMLNAMESPACES with my solution it fails. Is there a good way to make it see that this ? ;WITH XMLNAMESPACES DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )SELECT ReportID = ItemID , ReportName = Name , ReportPath = Path , DataSourceName = x.value('(@Name)[1]', 'VARCHAR(250)') , DataProvider = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)') , ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)') --INTO tReportDataSources FROM ( SELECT C.Name , C.ItemID , C.Path , CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML FROM ReportServer.dbo.Catalog C WHERE C.Content IS NOT NULL AND C.Type = 2 ) a CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x )ORDER BY Name ; |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-30 : 07:25:16
|
Are you constructing the entire query as dynamic SQL and then executing it? Assuming you are, it should work - I just tried it on a random report on my report server. If you are not doing that, how are you injecting the variable into the query string?In any case, if it will help, this is the code I tested - it is copied from what you posted, except I removed the conditionals in the @SSRSDefinition:DECLARE @SSRSDefinition NVARCHAR(256);SET @SSRSDefinition = '''http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'''DECLARE @sql NVARCHAR(MAX);SET @sql = ';WITH XMLNAMESPACES (DEFAULT ' + @SSRSDefinition +')SELECT ReportID = ItemID , ReportName = Name, ReportPath = Path , DataSourceName = x.value(''(@Name)[1]'', ''VARCHAR(250)'') , DataProvider = x.value(''(ConnectionProperties/DataProvider)[1]'',''VARCHAR(250)''), ConnectionString = x.value(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'')--INTO tReportDataSourcesFROM (SELECT C.Name, C.ItemID , C.Path , CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXMLFROM ReportServer.dbo.Catalog CWHERE C.Content IS NOT NULLAND C.Type = 2) aCROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r ( x )ORDER BY Name ;';exec sp_executesql @sql; |
|
|
ZMike
Posting Yak Master
110 Posts |
Posted - 2013-01-30 : 10:15:05
|
James K,That works great. I modified it slightly to fit my parameters. Thank you very much ! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-30 : 10:21:05
|
You are very welcome - glad to be of help.As always, be careful about using dynamic SQL, mainly because of SQL injection risk. A malicious user can send something unexpected in the @SSRSDefinition parameter and cause destructive effects on your database. |
|
|
|
|
|
|
|