none
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file RRS feed

  • Question

  • Hi Team,

    am using bellow script in ssrs reports to download to local system from report server 

     

    DECLARE @FilterReportPath AS VARCHAR(500) ='http://servername/Reportserver\' 

    DECLARE @FilterReportName AS VARCHAR(500) ='?'

    DECLARE @OutputPath AS VARCHAR(500) = 'D:\Reports\Download\' 

    DECLARE @TSQL AS NVARCHAR(MAX) 

    SET @OutputPath = REPLACE(@OutputPath,'\','/') 

    IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = '' 
    BEGIN 
      SELECT 'Invalid Output Path' 
    END 
    ELSE 
    BEGIN 

       SET @TSQL = STUFF((SELECT 
                          ';EXEC master..xp_cmdshell ''bcp " ' + 
                          ' SELECT ' + 
                          ' CONVERT(VARCHAR(MAX), ' + 
                          '       CASE ' + 
                          '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+ 
                          '         ELSE C.Content '+ 
                          '       END) ' + 
                          ' FROM ' + 
                          ' [ReportServer].[dbo].[Catalog] CL ' + 
                          ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' + 
                          ' WHERE ' + 
                          ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x''' 
                        FROM 
                          [ReportServer].[dbo].[Catalog] CL 
                        WHERE 
                          CL.[Type] = 2 --Report 
                          AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/') 
                          AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name) 
                        FOR XML PATH('')), 1,1,'') 

      EXEC SP_EXECUTESQL @TSQL 
    END

    But am getting above mentioned error message. Could you please help on same.

    Am using local authentication.


    Thanks Bala Narasimha

    Friday, October 18, 2019 11:33 AM

All replies

  • Hi Bala,

    Looks like you are trying to use TSQL script to download RDL file from Report Server Database?

    As far as I recalled this kind of ways(working directly with RS DB) were not encouraged by the official doc. Why not try more promising way that other developer has already succeed ? Please take look to the first and second answer in this thread:How do I copy SSRS reports to a new server if I am not the owner of the reports

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 21, 2019 2:50 AM