tag:blogger.com,1999:blog-7079873813109086690.post7797162942067427792..comments2023-06-20T21:38:12.042-07:00Comments on ...juggling with .net & sql: SSRS – Download all RDL files from Report Server in one go.vinay pugaliahttp://www.blogger.com/profile/07027426501611091260noreply@blogger.comBlogger97125tag:blogger.com,1999:blog-7079873813109086690.post-86698472317188345522023-06-20T21:38:12.042-07:002023-06-20T21:38:12.042-07:00Hello Brad,
Good to know that you found this code...Hello Brad,<br /><br />Good to know that you found this code useful but seems like it's not working for you.<br /><br />The case which you have explained generally happens when incorrect path is set (this is the only case I have found so far). So, I would request you to please double check if you have the right path set for the variables.<br /><br />Hope, it works for you.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-52298692023324476682023-06-20T13:30:15.691-07:002023-06-20T13:30:15.691-07:00Vinay - thank you for the code - when i run this c...Vinay - thank you for the code - when i run this code i get a 0kb file that is empty and the output states: NULL / Starting copy... / NULL / 0 rows copied. / Network packet size (bytes): 4096 / Clock Time (ms.) Total :1 / NULL<br />any ideas for me? Thanks, BradAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-51765765089895780992021-12-08T20:39:54.251-08:002021-12-08T20:39:54.251-08:00Hello Farrukh, I am sorry to say but supporting vi...Hello Farrukh, I am sorry to say but supporting via screen sharing will not be possible for me. At the same time I am sure that if you can debug the script by yourself, you will certainly pick the issue and then I can help you to sort it out.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-75508492494432109612021-12-08T14:53:12.754-08:002021-12-08T14:53:12.754-08:00Hi Expert Vinay can you please connect via teams o...Hi Expert Vinay can you please connect via teams or anydesk portable to resolve a issue if you have a time my whatsapp 00923333551019Farrukhhttps://www.blogger.com/profile/14679959877595383065noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-25737071256023907462021-12-08T05:50:20.363-08:002021-12-08T05:50:20.363-08:00Oops!
If this is the case, the only option we hav...Oops!<br /><br />If this is the case, the only option we have is to debug the script in your environment and check results are you getting at each stage.<br /><br />Please do the needful & share the update.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-21526774760361040502021-12-08T05:23:50.930-08:002021-12-08T05:23:50.930-08:00HI Expert Vinay,
I changed the above as mentioned ...HI Expert Vinay,<br />I changed the above as mentioned also given the full control shared to the folder Reports but no luck.<br />DECLARE @OutputPath AS VARCHAR(500)<br />set @OutputPath = 'C:\Reports\Download\'Farrukhhttps://www.blogger.com/profile/14679959877595383065noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-57683522737716787462021-12-08T03:28:15.179-08:002021-12-08T03:28:15.179-08:00However, I noticed that you need to change the val...However, I noticed that you need to change the value being set for this variable as under -<br />--Replace this path with the Server Location where you want the<br />--reports to be downloaded..<br />DECLARE @OutputPath AS VARCHAR(500)<br />set @OutputPath = 'C:\Reports\Download\'<br /><br />NOTICE the ending "\"<br /><br />Rest all seems okay to me.<br /><br />Please try again with the suggested change & update your findings.<br /><br />Thanks!vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-7259570086672422502021-12-07T22:55:35.046-08:002021-12-07T22:55:35.046-08:00DECLARE @FilterReportPath AS VARCHAR(500) = '&...<br /><br />DECLARE @FilterReportPath AS VARCHAR(500) = ''<br />DECLARE @FilterReportName AS VARCHAR(500) =''<br />DECLARE @OutputPath AS VARCHAR(500) = 'C:\Reports\Download';<br />SELECT Path, @OutputPath + REPLACE(REPLACE(REVERSE(SUBSTRING(REVERSE(Path), CHARINDEX('/',REVERSE(Path)), LEN(Path) - CHARINDEX('/',REVERSE(Path)) + 1)), '/','\'), '\\\\','\\') FolderPath FROM [SERVER].[ReportServer].[dbo].Catalog WITH(NOLOCK)<br />DECLARE @TSQL AS NVARCHAR(MAX);<br />DECLARE @DataPath NVARCHAR(500);<br />DECLARE @DirTree TABLE<br /> (<br /> subdirectory NVARCHAR(255) ,<br /> depth INT<br /> );<br /><br />select @OutputPath = REPLACE(@OutputPath, '/', '\');<br /> <br />IF LTRIM(RTRIM(ISNULL(@OutputPath, ''))) = ''<br /> BEGIN<br /> SELECT 'Invalid Output Path';<br /> END;<br />ELSE<br /> BEGIN<br /> DECLARE @itemID UNIQUEIDENTIFIER = NULL;<br /> DECLARE @currentFolderPath NVARCHAR(2000); <br /> IF ( OBJECT_ID('tempdb..#catalog') IS NOT NULL )<br /> DROP TABLE #catalog;<br /> SELECT * ,<br /> 0 AS Processed<br /> INTO #catalog<br /> FROM [SERVER].[ReportServer].[dbo].[Catalog] CL<br /> WHERE CL.[Type] = 2 --Report-- = 5 -- DataSource<br /> AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%'+ @FilterReportPath+ '%/%','/' + CL.[Path] + '/')<br /> AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%',CL.Name);<br /> WHILE EXISTS ( SELECT 1FROM #catalogWHERE Processed = 0 )<br /> BEGIN SELECT TOP 1 @itemID = ItemID ,@currentFolderPath = @OutputPath<br /> + REPLACE(REPLACE(REVERSE(SUBSTRING(REVERSE(Path),<br /> CHARINDEX('/',<br /> REVERSE(Path)),<br /> LEN(Path)<br /> - CHARINDEX('/',<br /> REVERSE(Path))<br /> + 1)), '/', '\'),<br /> '\\\\', '\\')<br /> FROM #catalog<br /> WHERE Processed = 0;<br /><br /> SET @TSQL = STUFF(( SELECT ';EXEC master..xp_cmdshell ''bcp " '<br /> + ' SELECT '<br /> + ' CONVERT(VARCHAR(MAX), '<br /> + ' CASE '<br /> + ' WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '<br /> + ' ELSE C.Content '<br /> + ' END) ' + ' FROM '<br /> + ' [SERVER].[ReportServer].[dbo].[Catalog] CL '<br /> + ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C '<br /> + ' WHERE ' + ' CL.ItemID = '''''<br /> + CONVERT(VARCHAR(MAX), CL.ItemID)<br /> + ''''' " queryout "'<br /> + @currentFolderPath + ''<br /> + CL.Name + '.rdl" '<br /> + '-T -c -x'''<br /> FROM #catalog CL<br /> WHERE CL.ItemID = @itemID<br /> FOR<br /> XML PATH('')<br /> ), 1, 1, '');<br /> <br /><br /> DELETE FROM @DirTree;<br /> INSERT INTO @DirTree<br /> ( subdirectory ,<br /> depth<br /> )<br /> <br /> EXEC master.sys.xp_dirtree @OutputPath;<br /> <br /> SELECT @currentFolderPath;<br /> IF NOT EXISTS ( SELECT 1<br /> FROM @DirTree<br /> WHERE subdirectory = @currentFolderPath )<br /> EXEC master.dbo.xp_create_subdir @currentFolderPath;<br /> <br /> -- SELECT @TSQL;<br /> <br /> --Execute the Dynamic Query<br /> EXEC sp_executesql @TSQL;<br /><br /> UPDATE #catalog<br /> SET Processed = 1<br /> WHERE ItemID = @itemID;<br /><br /> END;<br /> END;Farrukhhttps://www.blogger.com/profile/14679959877595383065noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-7871539590634413782021-12-07T22:51:15.734-08:002021-12-07T22:51:15.734-08:00Hi Vinay,
The command successfully executed but ou...Hi Vinay,<br />The command successfully executed but output is null and in folder no .rdl foundFarrukhhttps://www.blogger.com/profile/14679959877595383065noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-28883566982124506552021-12-07T21:14:42.220-08:002021-12-07T21:14:42.220-08:00Hello Farrukh, thanks a lot for using this script ...Hello Farrukh, thanks a lot for using this script but I am sorry to hear that it did not work for you. Can you please share more details on this. Say, any error message if you are getting. I will be in a much better position to help you with more details in hand.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-68536985292201188132021-12-07T08:09:06.940-08:002021-12-07T08:09:06.940-08:00Hi Admin Vinay,
I command runs successfully even c...Hi Admin Vinay,<br />I command runs successfully even create folders subfolders but .rdl files not downloading please help me out of it<br />Farrukhhttps://www.blogger.com/profile/14679959877595383065noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-33268501790359536072021-06-17T23:28:27.457-07:002021-06-17T23:28:27.457-07:00Hello, please scroll up & have a look at the c...Hello, please scroll up & have a look at the comment dated 25th March 2015. I guess both the issues are same & check on the replies to it. I hope, it will solve your issues. If not, please do write back.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-43923854035575850772021-06-17T03:51:59.899-07:002021-06-17T03:51:59.899-07:00This script is amazing thanks! I'm having one ...This script is amazing thanks! I'm having one issue though. For me it only works when I put a specific report name via <br />DECLARE @FilterReportName AS VARCHAR(500) = 'test'. If I leave it null it just throws me various errors.<br /><br />Could you please help? Thanks.<br /><br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near 's'.<br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near '705'.<br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near '78598004'.<br />Msg 132, Level 15, State 1, Line 1<br />The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.<br />Msg 168, Level 15, State 1, Line 1<br />The floating point value '4E910162' is out of the range of computer representation (8 bytes).<br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near '4E910162'.<br />Msg 132, Level 15, State 1, Line 1<br />The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.<br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near '93'.<br />Msg 132, Level 15, State 1, Line 1<br />The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.<br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near '0'.<br />Msg 132, Level 15, State 1, Line 1<br />The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.<br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near '696'.<br />Msg 132, Level 15, State 1, Line 1<br />The label 'D' has already been declared. Label names must be unique within a query batch or stored procedure.<br />Msg 102, Level 15, State 1, Line 1<br />Incorrect syntax near 'DC1D8B00'.<br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-69397467420523578212021-05-26T02:53:15.289-07:002021-05-26T02:53:15.289-07:00Thank you very much!Thank you very much!Anonymoushttps://www.blogger.com/profile/06328510628829905746noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-37585328974302017722021-05-26T02:15:49.639-07:002021-05-26T02:15:49.639-07:00Hello Daniel,
Deploying Rdl files in bulk was nev...Hello Daniel,<br /><br />Deploying Rdl files in bulk was never a problem. You can use tools like SSDT to create a report project and then deploy them in one go. vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-23592314581718372302021-05-26T00:29:57.333-07:002021-05-26T00:29:57.333-07:00Hi Vinay,
It was a good direction and I was able ...Hi Vinay,<br /><br />It was a good direction and I was able to fix the problem.<br /><br />Now, that I have the RDL file, I will modify it and I will have to put it back in the database.<br /><br />Is there a script for something like this, or can you help me with a few words to upload the RDL file into the database?<br /> <br />Thank you a lot.<br />DanielAnonymoushttps://www.blogger.com/profile/06328510628829905746noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-63070955861388867202021-05-25T04:38:33.863-07:002021-05-25T04:38:33.863-07:00Hello Daniel,
I guess the [ReportServer].[dbo].[C...Hello Daniel,<br /><br />I guess the [ReportServer].[dbo].[Catalog] table of the SQL Server version which you are using is having a different structure than the one this script is expecting.<br /><br />The error message says that it could not find the columns - Type, Path, Name & ItemId in the table - [ReportServer].[dbo].[Catalog]<br /><br />I hope, this will give you a good direction to fix the error.<br /><br />And yes, thanks a lot for using this script.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-86725572921368580682021-05-25T04:16:10.450-07:002021-05-25T04:16:10.450-07:00Hi Vinay,
Thanks for the nice script.but unfortun...Hi Vinay,<br /><br />Thanks for the nice script.but unfortunately I am facing below errors. Could you please help me out?<br /><br />Msg 207, Level 16, State 1, Line 54<br />Invalid column name 'Type'.<br />Msg 207, Level 16, State 1, Line 55<br />Invalid column name 'Path'.<br />Msg 207, Level 16, State 1, Line 55<br />Invalid column name 'Path'.<br />Msg 207, Level 16, State 1, Line 56<br />Invalid column name 'Name'.<br />Msg 207, Level 16, State 1, Line 56<br />Invalid column name 'Name'.<br />Msg 207, Level 16, State 1, Line 50<br />Invalid column name 'ItemID'.<br />Msg 207, Level 16, State 1, Line 50<br />Invalid column name 'Name'.<br /><br /><br />Thanks<br />DanielAnonymoushttps://www.blogger.com/profile/06328510628829905746noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-19897692942790112632019-08-23T06:17:08.806-07:002019-08-23T06:17:08.806-07:00is it possible to download all the reports with fo...is it possible to download all the reports with folder structure?Simhttps://www.blogger.com/profile/02127050160982717763noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-30882016352687717902019-07-15T21:25:35.310-07:002019-07-15T21:25:35.310-07:00Justin,
Can you please share your T-SQL for the a...Justin,<br /><br />Can you please share your T-SQL for the above statement if possible? I strongly feel that it would be just an extra '/' character in the path which is creating the issue.<br /><br />Actually, the 'Path' in the Catalog table stores the path with a '/' character prefixed & no trailing '/'. If we just take care of this in the T-SQl, it will work as you will need to use the '=' operator instead of 'LIKE'.<br /><br />Hope, this helps!vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-28922885022111173552019-07-15T13:44:05.773-07:002019-07-15T13:44:05.773-07:00Hi vinay,
Thanks again for your help but I found ...Hi vinay,<br /><br />Thanks again for your help but I found a powershell tool that does the trick. <br />https://github.com/Microsoft/ReportingServicesTools<br /><br />Take care,<br />JustinAnonymoushttps://www.blogger.com/profile/06762207197264388585noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-79755931124148779862019-07-15T12:39:44.108-07:002019-07-15T12:39:44.108-07:00Hi vinay,
I appreciate the effort but this did no...Hi vinay,<br /><br />I appreciate the effort but this did not seem to work. I get Command(s) completed successfully but no results in folder.<br /><br />Thank you,<br />Justin Anonymoushttps://www.blogger.com/profile/06762207197264388585noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-9021171315524579812019-07-15T10:01:18.806-07:002019-07-15T10:01:18.806-07:00Thank you very much. I'll test ASAP and report...Thank you very much. I'll test ASAP and report back. Anonymoushttps://www.blogger.com/profile/06762207197264388585noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-81923232364988758822019-07-13T00:18:57.239-07:002019-07-13T00:18:57.239-07:00Hello Justin,
Good to know that you have found th...Hello Justin,<br /><br />Good to know that you have found this script helpful. Actually, the primary focus of this script is to download all the rdl files starting from the root folder - recursively. <br /><br />However, I do understand your concern. And to handle this, you just need to make a small change in the TSQL. Please change the following statement <br /><br />AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')<br /><br />to<br /><br />AND '/' + CL.[Path] + '/' = COALESCE('/' + @FilterReportPath + '/', '/' + CL.[Path] + '/')<br /><br />Hope, this helps!<br /><br />P.S. I have not tested this. Please feel free to let me know if this does not work for you.vinay pugaliahttps://www.blogger.com/profile/07027426501611091260noreply@blogger.comtag:blogger.com,1999:blog-7079873813109086690.post-45694001341479398662019-07-12T14:28:42.826-07:002019-07-12T14:28:42.826-07:00Hello vinay, love the script, works great. However...Hello vinay, love the script, works great. However, I have sub folders and when I use @FilterReprotPath='Reports' I get all reports even those that are in sub folders like 'Reports/More Reports' The issue is I may have a report with the same name in the sub folder. Can I get it to only look at a specific folder and not the sub folders?<br />Thank you,<br />JustinAnonymoushttps://www.blogger.com/profile/06762207197264388585noreply@blogger.com