'Changing parameter prefix from : to @

I'm moving our SSRS reporting from an Oracle based database to SQL Server.

However, this is affecting the parameters within SSRS reports which use : for Oracle but @ for SQL Server.

We have almost 600 reports that are affected. Is there a quick way of making this change or is it a manual task?

I have tried search and replace on the actual RDL files but the colon is in more than just the parameters (lots of tags in the file text), so that's not an option.

sql-serveroraclereporting-servicesparameters


Solution 1:[1]

In Visual Studio, your report solution can do a find and replace in the XML code that makes up each .rdl file. Right click any report and view by code. Then you can use Crtl + F to Find the parameters in the entire solution.

Solution 2:[2]

This would require a bit of work but might be worth investigating...

First get a list of all parameters, this query also shows if the parameter is used in a query which might be helpful.

use ReportServer
SELECT 
       r.ReportName, r.ReportID,
       pVal.value('Name[1]', 'VARCHAR(256)') AS ParamName,
       pVal.value('UsedInQuery[1]', 'VARCHAR(250)') AS UsedInQuery,
       pVal.value('PromptUser[1]', 'VARCHAR(250)') AS PromptUser
FROM 
(
   SELECT 
       C.Name AS ReportName,
       c.ItemID as ReportID , 
       CONVERT(XML,C.Parameter) AS pXML
   FROM  Catalog C
   WHERE  C.Content IS NOT NULL AND  C.TYPE  = 2  
) AS  r
CROSS APPLY pXML.nodes('//Parameters/Parameter') p ( pVal )

Now you can search replace specific words e.g. :CountryID/@CountryID . You could probably write something to do this for you quite easily but you might find that there are not that many distinct parameter names in which case you could use VS Code or similar to do a search/replace across all rdl files in one go.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 aduguid
Solution 2 Alan Schofield

Related Questions