T-SQL stored procedure creates a PDF format file (report)

xiaoxiao2021-03-06  62

Repost, only reference fso, PDF format can write it yourself, just like a sign language:

Creating a PDF from a stored procedure

http://www.sqlservercentral.com/columnists/mivi/creatingapdffromastoredProcedure.asp

If you don't want to register, please post:

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 is this?) Article Rating Total Number of votes [183]

This Content Sponsored by:

IS SQL THE CENTER OF YOURSE? SQLCENTRIC IS A Comprehensive Web-Based Network Database Monitoring and Alert System. - BROUGHT To You By Pearl Knowledge Solutions, Inc.http://www.pearlknows.com.com.com.com.com

This Article Explains How To create a a stored procedure That Will In Turn Create a Simple Column Based Report In Pdf WITHOUT USING ANY EXTERNAL TOOLS or Libraries (and their associated licensing costs!).

SQL2PDF Makes a PDF Report from Text Inserted In The Table Psopdf (NVARCHAR (80)). First A Table Named Psopdf Should Be CREATED.

Create Table Psopdf (Code Nvarchar (80))

After That Create The Stored Procedure SQL2PDF.

SQL2PDF.TXTAND TABLE PSOPDF HAS TO BE FILED WITH YOUR DATA SHOWN EXAMPLES BELOW.AT THE The store name Only Only ".

EXEC SQL2PDF 'FileName'

The Result is in your c: / directory.

EXAMPLE 1:

INSERT PSOPDF (CODE) SELECT SPACE (60) 'Company Ltd.

INSERT PSOPDF (CODE) SELECT SPACE (60) 'Company Address'

INSERT PSOPDF (Code) SELECT SPACE (60) 'Street Name & NO'

INSERT PSOPDF (CODE) SELECT ''

INSERT PSOPDF (CODE) SELECT SPACE (34) 'Bill of Sale'

INSERT PSOPDF (CODE) SELECT ''

INSERT PSOPDF (CODE) SELECT 'Product' Space (10) 'Quantity' Space (10) 'Price' Space (10) 'Total'

INSERT PSOPDF (Code) SELECT Replace (Space (56), '', '_')

INSERT PSOPDF (CODE) SELECT 'Product1' Space (9) '10 .00 '

Space (10) '52.30 ' Space (10) ' 5230.0 '

INSERT PSOPDF (CODE) SELECT 'Product2' Space (9) '2.00'

Space (10) '10.00 ' space (10) ' 20.0 '

INSERT PSOPDF (Code) SELECT Replace (Space (56), '', '_')

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 PUBS

INSERT 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 result is in your c: / Directory.

SQL2PDF.txt

- DROP Procedure SQL2PDF

Create Procedure SQL2PDF

@FileName Varchar (100)

AS

Create Table #pdf (IDNumber Int Idness (1, 1)

, Code nvarchar (200))

Create Table #XREF (IDNumber Int Idnessity (1, 1)

, Code Varchar (30))

Create Table #Text (IDNumber Int Idness (1, 1)

Code varchar (200))

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 (10),

@Page Varchar (8000),

@pdf varchar (100),

@trenutnired nvarchar (200),

@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 (67) CHAR (114) Char (101) CHAR (97) CHAR (116) CHAR (111) CHAR (114)

SET @pr = char (80) char (114) char (111) char (100) char (117) char (99) char (101) char (114)

Set @ti = char (84) CHAR (105) Char (116) Char (108) CHAR (101)

Set @XSTR = '00000 n'

Set @ofset = 396

INSERT INTO #XREF (Code) VALUES ('Xref')

INSERT INTO #XREF (Code) Values ​​('0 10')

Insert Into #xref (Code) Values ​​('0000000000 65535 F')

INSERT INTO #XREF (Code) VALUES ('0000000017' @XSTR)

INSERT INTO #XREF (Code) VALUES ('0000000790' @XSTR)

INSERT INTO #XREF (Code) VALUES ('0000000869' @XSTR)

INSERT INTO #XREF (Code) VALUES ('0000000144' @XSTR)

INSERT INTO #XREF (Code) VALUES ('0000000247' @XSTR)

INSERT INTO #XREF (Code) VALUES ('0000000321' @XSTR)

INSERT INTO #XREF (Code) VALUES ('0000000396' @XSTR)

INSERT INTO #PDF (Code) VALUES ('%' Char (80) CHAR (68) CHAR (70) '-1.2') Insert Into #PDF (CODE) VALUES ('% Youyou')

INSERT INTO #PDF (Code) VALUES ('1' @BEG)

INSERT INTO #PDF (Code) VALUES (@ a1)

INSERT INTO #PDF (Code) VALUES ('/' @Cr "(Ivica Masar ' Char (80) CHAR (83) CHAR (79) CHAR (80) Char (68) Char (70 ) ')')

INSERT INTO #PDF (CODE) VALUES ('/' @pr '(Stored Procedure for MS SQL PSO@vip.hr)')

INSERT INTO #PDF (Code) VALUES ('/' @ti '(SQL2' Char (80) Char (68) CHAR (70) ')')

INSERT INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) VALUES (@end)

INSERT INTO #PDF (Code) VALUES ('4' @BEG)

INSERT INTO #PDF (Code) VALUES (@ a1)

INSERT INTO #PDF (Code) VALUES ('/ type / font')

INSERT INTO #PDF (Code) VALUES ('/ subtype / type1')

INSERT INTO #PDF (CODE) VALUES ('/ Name / F1')

INSERT INTO #PDF (Code) VALUES ('/ Encoding 5' @ad)

INSERT INTO #PDF (Code) Values ​​('/ BaseFont / Courier)

INSERT INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) VALUES (@end)

INSERT INTO #PDF (CODE) VALUES ('5' @BEG)

INSERT INTO #PDF (Code) VALUES (@ a1)

INSERT INTO #PDF (CODE) VALUES ('/ type / encoding ")

INSERT INTO #PDF (Code) Values ​​('/ BaseEncoding / Winansiencoding')

INSERT INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) VALUES (@end)

INSERT INTO #PDF (Code) VALUES ('6' @BEG)

INSERT INTO #PDF (Code) VALUES (@ a1)

INSERT INTO #PDF (CODE) VALUES ('/ font' @ A1 '/ F1 4' @ad '' @ A2 '/ procset [/' char (80) char (68) char (70) '/ text]') Insert INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) VALUES (@end)

INSERT INTO #Text (Code) (SELECT CODE "PSOPDF)

SELECT @X = count (*) from #Text

SELECT @X = (@X / 60) 1

While @nopg <@X

Begin

Declare Syskursor Insensitive Scroll Cursor

For Select Substring ((Code Space (81)), 1, 80) from #text where idnumber between (@nopg * 60) 1) and (@nopg 1) * 60)

For Read Only

Open syskursor

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 (CAST (Cast (@Object as varchar) @beg)

INSERT INTO #PDF (Code) VALUES (@ a1)

INSERT INTO #PDF (CODE) VALUES ('/ type / page')

INSERT INTO #PDF (CODE) VALUES ('/ Parent 3' @ad)

INSERT INTO #PDF (CODE) VALUES ('/ Resources 6' @ad)

SELECT @Object = @Object 1

INSERT INTO #PDF (CODE) VALUES ('/ Contents' Cast (@Object as varchar) @ad)

INSERT INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) VALUES (@end)

SELECT @ofset = @LEN 86 @ofset

INSERT INTO #XREF (CADE) (@ofset as varchar),

LEN ('0000000000' Cast (@ofset as varchar)) - 9, LEN ('0000000000' Cast (@ofset as varchar))) @XSTR)

INSERT INTO #PDF (CAST (Cast (@Object as varchar) @beg)

INSERT INTO #PDF (Code) VALUES (@ a1)

SELECT @Object = @Object 1

INSERT INTO #PDF (CODE) VALUES ('/ Length' Cast (@Object as varchar) @ad)

INSERT INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) Values ​​('Street ")

INSERT INTO #PDF (CODE) VALUES ('bt')

INSERT INTO #PDF (CODE) VALUES ('/ F1 10 TF')

INSERT INTO #PDF (Code) VALUES ('1 0 0 1 50 802 TM')

INSERT INTO #PDF (Code) VALUES ('12 TL ')

While @@ fetch_status = 0

Begin

INSERT INTO #PDF (Code) VALUES ('T * (' @trenutnired ") TJ ')

Fetch next from syskursor Into @trenutnired

End

INSERT INTO #PDF (CODE) VALUES ('ET')

INSERT INTO #PDF (Code) VALUES ('endstream')

INSERT INTO #PDF (Code) VALUES (@end)

SELECT @ROWS = (Select Count (*) from #text where IDnumber Between (@nopg * 60) 1) and (@nopg 1) * 60) * 90 45

SELECT @nopg = @nopg 1

SELECT @LEN = LEN (@Object) LEN (@Object - 1)

SELECT @ofset = @LEN 57 @ofset @ROWS

INSERT INTO #XREF (CADE) (@ofset as varchar),

Len ('0000000000' Cast (@ofset as varchar) - 9,

Len ('0000000000' cast (@ofset as varchar)) @XSTR)

INSERT INTO #PDF (CAST (Cast (@Object as varchar) @beg)

INSERT INTO #PDF (CODE) VALUES (@ROWS) INSERT INTO #PDF (CODE) VALUES (@end)

SELECT @LEN = LEN (@Object) LEN (@ROWS)

SELECT @ofset = @LEN 18 @ofset

INSERT INTO #XREF (CADE) (@ofset as varchar),

Len ('0000000000' Cast (@ofset as varchar) - 9,

Len ('0000000000' cast (@ofset as varchar)) @XSTR)

Close syskursor

Deallocate syskursor

End

INSERT INTO #PDF (Code) VALUES ('2' @BEG)

INSERT INTO #PDF (Code) VALUES (@ a1)

INSERT INTO #PDF (CODE) VALUES ('/ type / catalog')

INSERT INTO #PDF (Code) VALUES ('/ Pages 3' @ad)

INSERT INTO #PDF (CODE) VALUES ('/ PageLayout / OneColumn')

INSERT INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) VALUES (@end)

Update #xref set code = (Select code from #xref where idnumber = (select max (idnumber) from #xref) Where idnumber = 5

Delete from #xref where idnumber = (SELECT MAX (IDNUMBER) FROM #xref)

INSERT INTO #PDF (CODE) VALUES ('3' @beg)

INSERT INTO #PDF (Code) 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]')

INSERT INTO #PDF (Code) VALUES ('/ KIDS [' @Page ']')

INSERT INTO #PDF (Code) VALUES (@ a2)

INSERT INTO #PDF (Code) VALUES (@end)

SELECT @ofset = @ofset 79

Update #xref set code = (SELECT SUBSTRING (@ofset as varchar),

Len (@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 ​​(@ a1)

SELECT @Object = @Object 1

Update #xref set code = '0' cast (@Object as varchar) where idnumber = 2

INSERT INTO #XREF (CODE) VALUES ('/ size' cast (@Object as varchar))

INSERT INTO #XREF (Code) VALUES ('/ root 2' @ad)

INSERT INTO #XREF (Code) VALUES ('/ INFO 1' @ad)

INSERT INTO #XREF (Code) VALUES (@ a2)

INSERT INTO #XREF (Code) VALUES ('Startxref')

SELECT @LEN = LEN (@nopg) LEN (@PAGE)

SELECT @ofset = @LEN 86 @ofset

INSERT INTO #XREF (Code) VALUES (@ofset)

INSERT INTO #XREF (Code) VALUES ('%%' char (69) char (79) char (70))

INSERT INTO #PDF (Code) (SELECT CODE from #xref)

--Select 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 Syskursor Insensitive Scroll Cursor

For select code from #pdf Order by IDNumber

For Read Only

Open syskursor

Fetch next from syskursor Into @trenutnired

While @@ fetch_status = 0

Begin

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 = sp_oadestroy @fs

转载请注明原文地址:https://www.9cbs.com/read-119444.html

New Post(0)