asked
Create a file in SQL Server 2008 R2 and convert it to image datatype without writing it to disk?
I am working in SQL Server 2008 R2. I am currently generating PDF reports by building the pdf as plaintext (based on this:
http://www.sqlservercentral.com/articles/Miscellaneous/creatingapdffromastoredprocedure/1104/ ) It builds the body in plaintext then saves it to a folder using sp_OACreate with the .pdf extension. It is actually faster than SSRS in this case; we are generating 5k-100k pdfs (historical transaction data for our customers' customers) I then have to load these files into an image column so the clients can access their report through the software. The end user can then click in the app and it will spawn a PDF viewer to view the file. Is there a way to generate the file and convert it to an image without writing it to the disk and then reloading it? - I cannot change the software, the file must be presented to the client when they click the button in the interface. - I cannot change the database. I have these files saved as image datatypes. I cannot change this to any other data type. The files are all less than 20k in size. - I cannot implement filestream due to PCI Compliance security reasons. - I have no experience with CLR functions
Two things that come to mind. Even though you said you can't change the datatype you desperately need too. The image datatype has been deprecated in favor of varbinary(max) since sql 2005. Client code that retrieves this data as a byte array will still function with no changes. The second thing that comes to mind is you should probably do this in CLR. sp_OACreate methods are not very efficient and can cause memory leaks when not handled correctly.
PDF is a text-based format, so you should be able to accomplish this by making the following changes to that stored procedure: 1. Change the `@filename` input parameter to instead be: `@PdfContents NVARCHAR(MAX) OUTPUT` 2. Remove the bottom portion of that Stored Procedure, starting with the following line: SELECT @trenutniRed = 'del '+ @pdf 3. Add the following code to the end of that Stored Procedure: SET @PdfContents = N''; SELECT @PdfContents += pdf.code + NCHAR(0x0A) FROM #pdf pdf ORDER BY pdf.idnumber ASC; Then use the Stored Procedure as follows: DECLARE @MyPDF NVARCHAR(MAX); EXEC sql2pdf @PdfContents = @MyPDF OUTPUT; INSERT INTO dbo.MyTable (column_list) VALUES (..., CONVERT(VARBINARY(MAX), @PdfContents), ...);
19 People are following this question.
