April 22, 2011 at 9:46 am
I need to export my RDL files from my report server to a file(s).
Here is the syntax to give me what I need - I just can't make it work..
DECLARE @dir nvarchar(425)
DECLARE @name nvarchar(425)
DECLARE @mdCommand varchar(100)
DECLARE @bcpCommand varchar(1000)
DECLARE catalog_cursor CURSOR FOR
SELECT substring(path, 2, CHARINDEX('/', path, CHARINDEX('/', path)+1 ) - 2) as dir, name as name
FROM Catalog
where content is not null and type = 2 and CHARINDEX('/', path, CHARINDEX('/', path)+1 ) - 2 > 0
OPEN catalog_cursor
FETCH NEXT FROM catalog_cursor INTO @dir, @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mdCommand = 'md "C:/temp/' + @dir + '"'
SET @bcpCommand = 'bcp "SELECT convert(varchar(max), convert(varbinary(max), content)) from [reportserver].[dbo].[catalog]'
SET @bcpCommand = @bcpCommand + ' where type = 2 and name = ''' + @name + ''' " queryout'
SET @bcpCommand = @bcpCommand + ' "C:/temp/' + @dir + '/' + @name + '.rdl" ' + '-T -c -t,'
EXEC master..xp_cmdshell @mdCommand
EXEC master..xp_cmdshell @bcpCommand
FETCH NEXT FROM catalog_cursor INTO @dir, @name
END
CLOSE catalog_cursor
DEALLOCATE catalog_cursor
The error I get is:
Invalid object name 'report.server.dbo.catalog'
I have tried using just 'catalog' tried using full qulaified name etc..but can't make it work. Im running from the Report server DB..pretty sure it's something due to bcp.
September 22, 2011 at 9:25 am
Edit: Nevermind didn't read the Script correctly. I modified this to do something similar, so thanks for posting it =).
September 22, 2011 at 9:31 am
krypto69 (4/22/2011)
I need to export my RDL files from my report server to a file(s).Here is the syntax to give me what I need - I just can't make it work..
DECLARE @dir nvarchar(425)DECLARE @name nvarchar(425)
DECLARE @mdCommand varchar(100)
DECLARE @bcpCommand varchar(1000)
DECLARE catalog_cursor CURSOR FOR
SELECT substring(path, 2, CHARINDEX('/', path, CHARINDEX('/', path)+1 ) - 2) as dir, name as name
FROM Catalog
where content is not null and type = 2 and CHARINDEX('/', path, CHARINDEX('/', path)+1 ) - 2 > 0
OPEN catalog_cursor
FETCH NEXT FROM catalog_cursor INTO @dir, @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @mdCommand = 'md "C:/temp/' + @dir + '"'
SET @bcpCommand = 'bcp "SELECT convert(varchar(max), convert(varbinary(max), content)) from [reportserver].[dbo].[catalog]'
SET @bcpCommand = @bcpCommand + ' where type = 2 and name = ''' + @name + ''' " queryout'
SET @bcpCommand = @bcpCommand + ' "C:/temp/' + @dir + '/' + @name + '.rdl" ' + '-T -c -t,'
EXEC master..xp_cmdshell @mdCommand
EXEC master..xp_cmdshell @bcpCommand
FETCH NEXT FROM catalog_cursor INTO @dir, @name
END
CLOSE catalog_cursor
DEALLOCATE catalog_cursor
The error I get is:
Invalid object name 'report.server.dbo.catalog'
I have tried using just 'catalog' tried using full qulaified name etc..but can't make it work. Im running from the Report server DB..pretty sure it's something due to bcp.
That looks very interesting. Can you post the final working solution?
September 22, 2011 at 10:53 am
krypto69's script works perfectly as is.
All I did was make minor tweaks to suit my needs, i.e. added variables to specify the database, output directory, a target directory to use as the 'root' path to extract from in SSRS, and I removed that substring business on the path so the directory structure is preserved.
Very happy he posted this, as it saved me some time writing an extract myself =).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply