Wednesday, October 21, 2015

SQL Server - Generating PDF in TSQL

--CREATE TABLE TO HOLD PDF Details
CREATE  TABLE [tbl_SaveDoc]
(
         [DocID] [int] IDENTITY(1,1) NOT NULL,
         [DocName] nvarchar(100) NOT NULL,
         [Type] nvarchar(100) NOT NULL,
         [DocData] VARBINARY(MAX) NULL
)

-----------------------------------------------------------------------

--STORE PDF DATA AS VARBINARY(MAX) IN THE DATABASE
INSERT INTO tbl_SaveDoc(DocName,Type)
VALUES('1.pdf','pdf')

UPDATE tbl_SaveDoc
SET DocData = (
SELECT *
FROM OPENROWSET(BULK 'C:\1.pdf', SINGLE_BLOB) AS DocData)

-----------------------------------------------------------------------
--GENERATE PDF FILE FROM THE DATABASE
DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT DocData FROM tbl_SaveDoc WHERE DocID = 3);

DECLARE @Path nvarchar(1024);
SELECT @Path = 'C:\PDF';

DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT DocName FROM tbl_SaveDoc WHERE DocID = 3);

DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @Filename;

DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';

EXEC sp_OADestroy @ObjectToken;

No comments: