T-SQL stored procedures create PDF Format files (reports)

Create | Stored procedures
Paste, only reference the Fso,pdf format can write their own, just like the logo language:
Creating a PDF from a Stored Procedure
Http://www.sqlservercentral.com/columnists/mivica/creatingapdffromastoredprocedure.asp



Do not want to register on the posted out:
Creating a PDF from a Stored Procedure
Regular columnist:m Ivica
posted:08/26/2003
More articles from this columnist
14089 Reads

Add Article to Your Virtual Briefcase (What are this?)

Article Rating Total number of votes [183]

This Content sponsored By:is SQL the Center of your universe?
Sqlcentric is a comprehensive web-based network database monitoring and alert system. -Brought to and by Pearl Knowledge Solutions, Inc.
Http://www.pearlknows.com

This is article explains how to create a a stored procedure that would in turn create a simple column based the in PDF with Out using any external tools or libraries (and their associated licensing costs!).

Sql2pdf makes a PDF in the "inserted in the" Table psopdf (nvarchar (80)). A table named Psopdf should be created.


CREATE TABLE psopdf (Code NVARCHAR (80))

After this create the stored procedure sql2pdf.

Sql2pdf. Txt

and table psopdf has to is filled with your data as shown in examples below.
At the end of the stored procedure is called using the ' file name only ' (not extension).


EXEC sql2pdf ' FileName '

The result is in your C:\ directory.

EXAMPLE 1:
Insert Psopdf (code) Select Space (m) + ' company LTD ' Insert psopdf (code) Select Spaces + ' Company address ' insert Psopdf ( Code) + ' street NAME & No ' inserts psopdf (code) SELECT ' Insert psopdf (code) Select Space + ' BILL of SALE ' Insert psopdf (code) SELECT ' Insert psopdf (code) SELECT ' Product ' + space (Ten) + ' Quantity ' + space (Ten) + ' price ' + Space (a) + ' total ' Insert psopdf (code) Select REPLACE (Spaces (), ', ' _ ') Insert Psopdf (code) SELECT ' Product1 ' + space (9 + ' 10.00 ' + space (Ten) + ' 52.30 ' + space (Ten) + ' 5230.0 ' INSERT psopdf (code) SELECT ' Product2 ' + space (9) + ' 2.00 ' + spaces ( + ' 10.00 ' + space (Ten) + ' 20.0 ' Insert psopdf (code) Select REPLACE (Space (in), ', ' _ ') Insert Psopdf (code) Select Space (50) + ' 5250.0 '

After INSERT call the stored procedure with file name Demo2.

EXEC sql2pdf ' Demo2 '

The result is in your C:\ directory.



EXAMPLE 2:
Second example uses a database pubs.

Use Pubsinsert psopdf (code) SELECT T1.au_lname + ' + t1.au_fname + ' + t1.phone + ' + t1.address + ' + t1.city + ' ' + t1.state + ' + t1.zip from authors T1, authors T2

After INSERT call the stored procedure with file name Demo1.

EXEC sql2pdf ' Demo1 '

>the is in your C:\ directory.





Sql2pdf.txt

--DROP PROCEDURE sql2pdfcreate PROCEDURE sql2pdf @filename VARCHAR (m) as CREATE TABLE #pdf (idnumber INT IDENTITY (1,1) , Code NVARCHAR CREATE TABLE #xref (idnumber int IDENTITY (1,1), code VARCHAR) CREATE TABLE #text (idnumber int ID ENTITY (1,1), code VARCHAR (m) DECLARE @end VARCHAR (7), @beg VARCHAR (7), @a1 VARCHAR (3), @a2 VARCHAR (3), @ad VARCHAR (5), @cr VARCHAR (8), @pr VARCHAR (9), @ti VARCHAR (6), @xstr VARCHAR (a), @page VARCHAR (8000), @pdf VARCHAR (MB), @trenutniRed NVA Rchar, @rows int, @ofset int, @len int, @nopg int, @fs int, @ole int,@x int, @file int, @object int SELECT @pdf = ' C:\ ' + @filename + '. pdf ' Set @page = ' Set @nopg = 0 Set @object = 6 Set @end = ' Endobj ' Set @beg = ' 0 obj ' Set @a1 = ' < < ' Set @a2 = ' >> ' Set @ad = ' 0 R ' Set @cr = char (+) + char (114) + char () + char () + char (116) + char (111 + char (114) Set @pr = char (+) + char (114) + char (+) + char (m) + char (117) + char (+) + char (+) + char (114) Set @ti = CHAR (+) + CHAR (105) + char (116) + char (108) + char () set @xstr = ' 00000 n ' Set @ofset = 396 INSERT into #xref (code) VALUES (' XRef ') in SERT into #xref (code) VALUES (' 0 ") insert into #xref (code) VALUES (' 0000000000 65535 f ') inserts into #xref (code) values (' 0000000017 ' + @xstr) INSERT into #xref (code) VALUES (' 0000000790 ' + @xstr) inserts into #xref (code) VALUES (' 0000000869 ' + @xstr) inserts into #x Ref (code) VALUES (' 0000000144 ' + @xstr) insert into #xref (code) VALUES (' 0000000247 ' + @xstr) inserts into #xref (code) Valu ES (' 0000000321 ' + @xstr) insert into #xref (code) VALUES (' 0000000396 ' + @xstr) inserts into #pdf (code) VALUES ('% ' + CHAR (m) + CHAR (+ char) + ' -1.2 ') insert into #pdf (code) VALUES (' Bless you ') insert into #pdf (code) VALUES (' 1 ' + @beg) I Nsert into #pdf (code) VALUES (@a1) inserts into #pdf (code) VALUES ('/' + @cr + ' (Ivica Masar ' + char () + char () + C HAR ((+) + char () + char () + char () + ') ') INSERT into #pdf (code) VALUES ('/' + @pr + ' (stored procedure for MS SQL pso@vip.hr) insert into #pdf (code) VALUES ('/' + @ti + ' (SQL2 ' + char () + char () + char () + ') ') inserts into #pdf (code) VALUES (@a2) insert into #pdf (code) VALUES (@end) inserts into #pdf (code) VALUES (' 4 ' + @beg) inserts into #p DF (Code) VALUES (@a1) insert into #pdf (code) VALUES ('/type/font ') inserts into #pdf (code) VALUES ('/subtype/type1 ') I Nsert into #pdf (code) VALUES ('/NAME/F1 ') inserts into #pdf (code) VALUES ('/encoding 5 ' + @ad) inserts into #pdf (code) V Alues ('/basefont/courier ') insert into #pdf (code) VALUES (@a2) inserts into #pdf (code) VALUES (@end) inserts into #pdf ( Code) VALUES (' 5 ' + @beg) insert into #pdf (code) VALUES (@a1) inserts into #pdf (code) VALUES ('/type/encoding ') Insert I NTO #pdf (Code) VALUES ('/baseencoding/winansiencoding ') insert into #pdf (code) VALUES (@a2) inserts into #pdf (code) VAL UES (@end) insert into #pdf (code) VALUES (' 6 ' + @beg) inserts into #pdf (code) VALUES (@a1) inserts into #pdf (code) values ('/font ' + @a1 + '/F1 4 ' + @ad + ' + @a2 + '/procset [/' + char (= + char (+) + char () + '/text] ') INSERT into #pdf (code) VALUES (@ A2 INSERT into #pdf (code) VALUES (@end) insert into #text (code) (select code from psopdf) Select @x = COUNT (*) from #tex T SELECT @x = (@x/60) + 1 while @nopg < @x BEGIN DECLARE syskursor insensitive SCROLL CURSOR for select SUBSTRING (CO De + space (Bayi)), 1 from #text WHERE idnumber BETWEEN ((@nopg *) + 1) and ((@nopg + 1) * for READ only OPEN Sy Skursor FETCH NEXT from syskursor into @trenutniRed select @object = @object + 1 Select @page = @page + ' + CAST (@object As VARCHAR + @ad SELECT @len = Len (@object) + len (@object + 1) INSERT into #pdf (code) VALUES (CAST (@object as VARCHAR) + @beg) insert into #pdf (code) VALUES (@a1) inserts into #pdf (code) VALUES ('/type/page ') inserts into #pdf (code) values ('/parent 3 ' + @ad) INSERT into #pdf (code) VALUES ('/resources 6 ' + @ad) SELECT @object = @object + 1 INSERT into #pdf (code) VALUES ('/conte Nts ' + CAST (@object as VARCHAR) + @ad) insert into #pdf (code) VALUES (@a2) insert into #pdf (code) VALUES (@end) SELECT @of Set = @len + + @ofset INSERT into #xref (code) (SELECT SUBSTRING (' 0000000000 ' + CAST (@ofset as VARCHAR), Len (' 0000000000 ' + cast (@ofset as VARCHAR)-9, LEN (' 0000000000 ' + CAST (@ofset as VARCHAR)) + @xstr) INSERT into #pdf (code) VALUES (CA ST (@object as VARCHAR) + @beg) insert into #pdf (code) VALUES (@a1) SELECT @object = @object + 1 INSERT into #pdf (code) V Alues ('/length ' + CAST (@object as VARCHAR) + @ad) insert into #pdf (code) VALUES (@a2) insert into #pdf (code) VALUES (' Stream ') insert into #pdf (code) VALUES (' BT ') inserts into #pdf (code) VALUES ('/f1 Tf ') inserts into #pdf (code) values (' 1 0 0 1 Tm ') INSERT into #pdf (code) VALUES ("TL") while @ @Fetch_Status = 0 BEGIN inserts into #pdf (code) VALUES (' t* (' + @trenutni Red + ') Tj ' FETCH NEXT from syskursor to @trenutniRed end insert to #pdf (code) VALUES (' ET ') insert into #pdf (code ) VALUES (' Endstream ') INSERT into #pdf (code) VALUES (@end) Select @rows = (select COUNT (*) from #text WHERE idnumber BETW Een (@nopg *) + 1) and ((@nopg + 1))) * + Select @nopg = @nopg + 1 Select @len = Len (@object) + len (@object -1 Select @ofset = @len + + + @ofset + @rows INSERT into #xref (code) (Select SUBSTRING (' 0000000000 ' + CAST (@ofset as V Archar), Len (' 0000000000 ' + cast (@ofset as VARCHAR))-9, Len (' 0000000000 ' + CAST (@ofset as VARCHAR)) + @xstr) INSERT INT O #pdf (code) VALUES (CAST (@object as VARCHAR) + @beg) insert into #pdf (code) VALUES (@rows) insert into #pdf (code) Valu ES (@end) Select @len = Len (@object) + len (@rows) Select @ofset = @len + + @ofset INSERT into #xref (code) (select SUBST Ring (' 0000000000 ' + cast (@ofset as VARCHAR), Len (' 0000000000 ' + cast (@ofset as VARCHAR))-9, Len (' 0000000000 ' + cast (@ofs ET as VARCHAR)) + @xstr) Close syskursor deallocate syskursor end insert to #pdf (code) VALUES (' 2 ' + @beg) insert INTO #pdf (Code) VALUES (@a1INSERT into #pdf (code) VALUES ('/type/catalog ') inserts into #pdf (code) VALUES ('/pages 3 ' + @ad) inserts into #pdf (CO DE) VALUES ('/pagelayout/onecolumn ') insert into #pdf (code) VALUES (@a2) inserts into #pdf (code) VALUES (@end) UPDATE #x Ref SET Code = (select code from #xref WHERE idnumber = (select MAX (idnumber) to #xref)) WHERE IDNumber = 5 DELETE from #xref WHERE idnumber = (SELECT MAX (idnumber) from #xref) inserts into #pdf (code) VALUES (' 3 ' + @beg) inserts into #pdf (COD E values (@a1) insert into #pdf (code) VALUES ('/type/pages ') insert into #pdf (code) VALUES ('/count ' + CAST (@nopg as VARCHAR) insert into #pdf (code) VALUES ('/mediabox [0 0 595 842] ') inserts into #pdf (code) VALUES ('/kids [' + @page + '] INSERT into #pdf (code) VALUES (@a2) inserts into #pdf (code) VALUES (@end) SELECT @ofset = @ofset + UPDATE #xref SET code = (SELECT SUBSTRING (' 0000000000 ' + cast (@ofset as VARCHAR), Len (' 0000000000 ' + cast (@ofset as VARCHAR))-9, Len ( ' 0000000000 ' + CAST (@ofsET as VARCHAR) () + @xstr) WHERE IDNumber = 6 INSERT INTO #xref (code) VALUES (' trailer ') insert into #xref (code) VALUES (@a 1 SELECT @object = @object + 1 UPDATE #xref SET code = ' 0 ' + CAST (@object as VARCHAR) WHERE IDNumber = 2 INSERT into #xr EF (code) VALUES ('/size ' + CAST (@object as VARCHAR)) insert into #xref (code) VALUES ('/root 2 ' + @ad) inserts into #xref (c Ode VALUES ('/info 1 ' + @ad) insert into #xref (code) VALUES (@a2) inserts into #xref (code) VALUES (' Startxref ') SELECT @le n = Len (@nopg) + len (@page) SELECT @ofset = @len + @ofset INSERT into #xref (code) VALUES (@ofset) insert into #xref (c ODE) VALUES (' percent ' + char () + char (+) + char) INSERT into #pdf (code) (SELECT code from #xref)--select the code from # PDF SELECT @trenutniRed = ' del ' + @pdf EXECUTE @ole = sp_OACreate ' Scripting.FileSystemObject ', @fs out EXEC master. xp_cmdshell @trenutniRed, no_output EXECUTE @ole = sp_OAMethod @fs, ' OpenTextFile ', @file out, @pdf, 8, 1 DECLARE syskurso R insensitive SCROLL CURSOR FOR SELECT code from #pdf the order by IDNumber to READ only OPEN syskursor FETCH the NEXT from the syskursor into @trenutniRed while @ @Fetch_Status = 0BEGIN EXECUTE @ole = sp_OAMethod @file, ' WriteLine ', Null, @trenutniRed Fetch NEXT from syskursor into @ Trenutnired End Close Syskursor deallocate syskursor DELETE from psopdf execute @ole = sp_OADestroy @file Execute @ole = s P_oadestroy @fs