Storing Crystal Reports Files in Database?

Question

I've got a UI front end which talks to and manipulates a SQL Server database, and one of the things it can do is run reports on the data in the database.

This UI can be installed on multiple computers, and so far I've just been keeping the reports in a folder with the install, but this means that any time a new report is added is has to manually be copied to every single UI install out there.

I was thinking of storing the .rpt files in the database itself (As Blobs) and having some mechanism for the UI to fetch them when needed as a way to centralize the reports and eliminate this issue.

Has anybody tried this, and did it work well? Or if you haven't, can you think of anything I should take into account before moving forward with this? Are the any tips, tricks, or caveats you can think of that might be helpful to me?

Answer 1

Here's great podcast with Paul Randal (he wrote parts of DBCC!) where they talk about the new filestream feature in sql server 2008 for handling blobs, but they also go into the sizes of files that do and don't work well as blobs as part of the talk. I think it would help you. http://www.runasradio.com/default.aspx?showNum=74

I just found out that the 25-page FILESTREAM whitepaper Paul wrote has has been published on MSDN. http://msdn.microsoft.com/en-us/library/cc949109.aspx.

Based on the research cited later in this white paper, BLOBs smaller than 256 kilobytes (KB) (such as widget icons) are better stored inside a database, and BLOBs larger than 1 megabyte (MB) are best stored outside the database. For those sized between 256 KB and 1 MB, the more efficient storage solution depends on the read vs. write ratio of the data, and the rate of “overwrite”. Storing BLOB data solely within the database (e.g., using the varbinary(max) data type) is limited to 2 gigabytes (GB) per BLOB.

Answer 2

Great Question! It's kind of coincidental as we've actually just implemented this within the last six months.

As you've suggested, we store the rpt file within the database, but do this in Server 2005 as a Image type. It works just fine and as far as the database goes, there really is no caveats that come to mind.

Obviously, the way you access this information changes with the API. If you're using C#, this translates to using a BinaryReader to load in the rpt file, grabbing a byte array. This byte array can then be passed to the database, through a stored procedure, etc.

I realize that you're asking specifically about blobs and Server 2008, but this works in both Server 2005 and Server 2008. Hope this sheds a bit of light.

If you need more specific details I'd be glad to share!

Answer 3

Okay so we all now that we can easily store a BLOB in a SQL SERVER, ORACLE, SQLITE, MYSQL Server and any other Database thats worth anything. What I am wondering is after you got the byte array out of the DB how did you create the report?

I am wanting to do the same thing but the only thing that I can think of is pulling the file from the DB, creating a physical file in a Temp folder and then using the physical address of the new file to create the crystal Report. Is there any way to create a Crystal Report from a Memory Stream or byte Array?

Answer 4

http://natrajt.blogspot.com/2009/08/storing-and-retrieving-crystal-reports.html