MSSQL网页编程之T-SQL 存储历程创立 PDF 格局文件(报表...

MySQL的海豚标志的名字叫“sakila”，它是由MySQLAB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者AmbroseTwebaze提供。创立|存储历程
转贴,仅援用了FSO,PDF格局能够本人写,就像标记言语:
CreatingaPDFfromaStoredProcedure
http://www.sqlservercentral.com/columnists/mivica/creatingapdffromastoredprocedure.asp



不肯注册就贴出来:
CreatingaPDFfromaStoredProcedure
RegularColumnist:MIvica
Posted:08/26/2003
MoreArticlesFromThisColumnist
14089Reads

AddArticletoYourVirtualBriefcase(Whatisthis?)

ArticleRatingTotalnumberofvotes[183]

ThisContentSponsoredby:IsSQLtheCenterofyourUniverse?
SQLCentricisacomprehensiveweb-basednetworkdatabasemonitoringandalertsystem.-broughttoyoubyPearlKnowledgeSolutions,Inc.
http://www.pearlknows.com

ThisarticleexplainshowtocreateaastoredprocedurethatwillinturncreateasimplecolumnbasedreportinPDFwithoutusinganyexternaltoolsorlibraries(andtheirassociatedlicensingcosts!).

SQL2PDFmakesaPDFreportfromtextinsertedinthetablepsopdf(nvarchar(80)).Firstatablenamedpsopdfshouldbecreated.


CREATETABLEpsopdf(codeNVARCHAR(80))

AfterthatcreatethestoredprocedureSQL2PDF.

SQL2PDF.TXT

Andtablepsopdfhastobefilledwithyourdataasshowninexamplesbelow.
Attheendthestoredprocedureiscalledusingthefilenameonly(notextension).


EXECsql2pdffileName

TheresultisinyourC:directory.

EXAMPLE1:
INSERTpsopdf(code)SELECTSPACE(60)+COMPANYLTDINSERTpsopdf(code)SELECTSPACE(60)+COMPANYADDRESSINSERTpsopdf(code)SELECTSPACE(60)+STREETNAME&NoINSERTpsopdf(code)SELECTINSERTpsopdf(code)SELECTSPACE(34)+BILLOFSALEINSERTpsopdf(code)SELECTINSERTpsopdf(code)SELECTProduct+SPACE(10)+Quantity+SPACE(10)+Price+SPACE(10)+TotalINSERTpsopdf(code)SELECTREPLACE(SPACE(56),,_)INSERTpsopdf(code)SELECTProduct1+SPACE(9)+10.00+SPACE(10)+52.30+SPACE(10)+5230.0INSERTpsopdf(code)SELECTProduct2+SPACE(9)+2.00+SPACE(10)+10.00+SPACE(10)+20.0INSERTpsopdf(code)SELECTREPLACE(SPACE(56),,_)INSERTpsopdf(code)SELECTSPACE(50)+5250.0

AfterINSERTcallthestoredprocedurewithfilenamedemo2.

EXECsql2pdfdemo2

TheresultisinyourC:directory.



EXAMPLE2:
Secondexampleusesadatabasepubs.

USEpubsINSERTpsopdf(code)SELECTt1.au_lname++t1.au_fname++t1.phone++t1.address++t1.city++t1.state++t1.zipFROMauthorst1,authorst2

AfterINSERTcallthestoredprocedurewithfilenamedemo1.

EXECsql2pdfdemo1

>TheresultisinyourC:directory.





sql2pdf.txt

--DROPPROCEDUREsql2pdfCREATEPROCEDUREsql2pdf@filenameVARCHAR(100)ASCREATETABLE#pdf(idnumberINTIDENTITY(1,1),codeNVARCHAR(200))CREATETABLE#xref(idnumberINTIDENTITY(1,1),codeVARCHAR(30))CREATETABLE#text(idnumberINTIDENTITY(1,1),codeVARCHAR(200))DECLARE@endVARCHAR(7),@begVARCHAR(7),@a1VARCHAR(3),@a2VARCHAR(3),@adVARCHAR(5),@crVARCHAR(8),@prVARCHAR(9),@tiVARCHAR(6),@xstrVARCHAR(10),@pageVARCHAR(8000),@pdfVARCHAR(100),@trenutniRedNVARCHAR(200),@rowsINT,@ofsetINT,@lenINT,@nopgINT,@fsINT,@oleINT,@xINT,@fileINT,@objectINTSELECT@pdf=C:+@filename+.pdfSET@page=SET@nopg=0SET@object=6SET@end=endobjSET@beg=0objSET@a1=<<SET@a2=>>SET@ad=0RSET@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=00000nSET@ofset=396INSERTINTO#xref(code)VALUES(xref)INSERTINTO#xref(code)VALUES(010)INSERTINTO#xref(code)VALUES(000000000065535f)INSERTINTO#xref(code)VALUES(0000000017+@xstr)INSERTINTO#xref(code)VALUES(0000000790+@xstr)INSERTINTO#xref(code)VALUES(0000000869+@xstr)INSERTINTO#xref(code)VALUES(0000000144+@xstr)INSERTINTO#xref(code)VALUES(0000000247+@xstr)INSERTINTO#xref(code)VALUES(0000000321+@xstr)INSERTINTO#xref(code)VALUES(0000000396+@xstr)INSERTINTO#pdf(code)VALUES(%+CHAR(80)+CHAR(68)+CHAR(70)+-1.2)INSERTINTO#pdf(code)VALUES(%佑佑)INSERTINTO#pdf(code)VALUES(1+@beg)INSERTINTO#pdf(code)VALUES(@a1)INSERTINTO#pdf(code)VALUES(/+@cr+(IvicaMasar+CHAR(80)+CHAR(83)+CHAR(79)+CHAR(80)+CHAR(68)+CHAR(70)+))INSERTINTO#pdf(code)VALUES(/+@pr+(storedprocedureformssqlpso@vip.hr))INSERTINTO#pdf(code)VALUES(/+@ti+(SQL2+CHAR(80)+CHAR(68)+CHAR(70)+))INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(@end)INSERTINTO#pdf(code)VALUES(4+@beg)INSERTINTO#pdf(code)VALUES(@a1)INSERTINTO#pdf(code)VALUES(/Type/Font)INSERTINTO#pdf(code)VALUES(/Subtype/Type1)INSERTINTO#pdf(code)VALUES(/Name/F1)INSERTINTO#pdf(code)VALUES(/Encoding5+@ad)INSERTINTO#pdf(code)VALUES(/BaseFont/Courier)INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(@end)INSERTINTO#pdf(code)VALUES(5+@beg)INSERTINTO#pdf(code)VALUES(@a1)INSERTINTO#pdf(code)VALUES(/Type/Encoding)INSERTINTO#pdf(code)VALUES(/BaseEncoding/WinAnsiEncoding)INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(@end)INSERTINTO#pdf(code)VALUES(6+@beg)INSERTINTO#pdf(code)VALUES(@a1)INSERTINTO#pdf(code)VALUES(/Font+@a1+/F14+@ad++@a2+/ProcSet[/+CHAR(80)+CHAR(68)+CHAR(70)+/Text])INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(@end)INSERTINTO#text(code)(SELECTcodeFROMpsopdf)SELECT@x=COUNT(*)FROM#textSELECT@x=(@x/60)+1WHILE@nopg<@xBEGINDECLARESysKursorINSENSITIVESCROLLCURSORFORSELECTSUBSTRING((code+SPACE(81)),1,80)FROM#textWHEREidnumberBETWEEN((@nopg*60)+1)AND((@nopg+1)*60)FORREADONLYOPENSysKursorFETCHNEXTFROMSysKursorINTO@trenutniRedSELECT@object=@object+1SELECT@page=@page++CAST(@objectASVARCHAR)+@adSELECT@len=LEN(@object)+LEN(@object+1)INSERTINTO#pdf(code)VALUES(CAST(@objectASVARCHAR)+@beg)INSERTINTO#pdf(code)VALUES(@a1)INSERTINTO#pdf(code)VALUES(/Type/Page)INSERTINTO#pdf(code)VALUES(/Parent3+@ad)INSERTINTO#pdf(code)VALUES(/Resources6+@ad)SELECT@object=@object+1INSERTINTO#pdf(code)VALUES(/Contents+CAST(@objectASVARCHAR)+@ad)INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(@end)SELECT@ofset=@len+86+@ofsetINSERTINTO#xref(code)(SELECTSUBSTRING(0000000000+CAST(@ofsetASVARCHAR),LEN(0000000000+CAST(@ofsetASVARCHAR))-9,LEN(0000000000+CAST(@ofsetASVARCHAR)))+@xstr)INSERTINTO#pdf(code)VALUES(CAST(@objectASVARCHAR)+@beg)INSERTINTO#pdf(code)VALUES(@a1)SELECT@object=@object+1INSERTINTO#pdf(code)VALUES(/Length+CAST(@objectASVARCHAR)+@ad)INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(stream)INSERTINTO#pdf(code)VALUES(BT)INSERTINTO#pdf(code)VALUES(/F110Tf)INSERTINTO#pdf(code)VALUES(100150802Tm)INSERTINTO#pdf(code)VALUES(12TL)WHILE@@Fetch_Status=0BEGININSERTINTO#pdf(code)VALUES(T*(+@trenutniRed+)Tj)FETCHNEXTFROMSysKursorINTO@trenutniRedENDINSERTINTO#pdf(code)VALUES(ET)INSERTINTO#pdf(code)VALUES(endstream)INSERTINTO#pdf(code)VALUES(@end)SELECT@rows=(SELECTCOUNT(*)FROM#textWHEREidnumberBETWEEN((@nopg*60)+1)AND((@nopg+1)*60))*90+45SELECT@nopg=@nopg+1SELECT@len=LEN(@object)+LEN(@object-1)SELECT@ofset=@len+57+@ofset+@rowsINSERTINTO#xref(code)(SELECTSUBSTRING(0000000000+CAST(@ofsetASVARCHAR),LEN(0000000000+CAST(@ofsetASVARCHAR))-9,LEN(0000000000+CAST(@ofsetASVARCHAR)))+@xstr)INSERTINTO#pdf(code)VALUES(CAST(@objectASVARCHAR)+@beg)INSERTINTO#pdf(code)VALUES(@rows)INSERTINTO#pdf(code)VALUES(@end)SELECT@len=LEN(@object)+LEN(@rows)SELECT@ofset=@len+18+@ofsetINSERTINTO#xref(code)(SELECTSUBSTRING(0000000000+CAST(@ofsetASVARCHAR),LEN(0000000000+CAST(@ofsetASVARCHAR))-9,LEN(0000000000+CAST(@ofsetASVARCHAR)))+@xstr)CLOSESysKursorDEALLOCATESysKursorENDINSERTINTO#pdf(code)VALUES(2+@beg)INSERTINTO#pdf(code)VALUES(@a1)INSERTINTO#pdf(code)VALUES(/Type/Catalog)INSERTINTO#pdf(code)VALUES(/Pages3+@ad)INSERTINTO#pdf(code)VALUES(/PageLayout/OneColumn)INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(@end)UPDATE#xrefSETcode=(SELECTcodeFROM#xrefWHEREidnumber=(SELECTMAX(idnumber)FROM#xref))WHEREidnumber=5DELETEFROM#xrefWHEREidnumber=(SELECTMAX(idnumber)FROM#xref)INSERTINTO#pdf(code)VALUES(3+@beg)INSERTINTO#pdf(code)VALUES(@a1)INSERTINTO#pdf(code)VALUES(/Type/Pages)INSERTINTO#pdf(code)VALUES(/Count+CAST(@nopgASVARCHAR))INSERTINTO#pdf(code)VALUES(/MediaBox[00595842])INSERTINTO#pdf(code)VALUES(/Kids[+@page+])INSERTINTO#pdf(code)VALUES(@a2)INSERTINTO#pdf(code)VALUES(@end)SELECT@ofset=@ofset+79UPDATE#xrefSETcode=(SELECTSUBSTRING(0000000000+CAST(@ofsetASVARCHAR),LEN(0000000000+CAST(@ofsetASVARCHAR))-9,LEN(0000000000+CAST(@ofsetASVARCHAR)))+@xstr)WHEREidnumber=6INSERTINTO#xref(code)VALUES(trailer)INSERTINTO#xref(code)VALUES(@a1)SELECT@object=@object+1UPDATE#xrefSETcode=0+CAST(@objectASVARCHAR)WHEREidnumber=2INSERTINTO#xref(code)VALUES(/Size+CAST(@objectASVARCHAR))INSERTINTO#xref(code)VALUES(/Root2+@ad)INSERTINTO#xref(code)VALUES(/Info1+@ad)INSERTINTO#xref(code)VALUES(@a2)INSERTINTO#xref(code)VALUES(startxref)SELECT@len=LEN(@nopg)+LEN(@page)SELECT@ofset=@len+86+@ofsetINSERTINTO#xref(code)VALUES(@ofset)INSERTINTO#xref(code)VALUES(%%+CHAR(69)+CHAR(79)+CHAR(70))INSERTINTO#pdf(code)(SELECTcodeFROM#xref)--SELECTcodeFROM#pdfSELECT@trenutniRed=del+@pdfEXECUTE@ole=sp_OACreateScripting.FileSystemObject,@fsOUTEXECmaster..xp_cmdshell@trenutniRed,NO_OUTPUTEXECUTE@ole=sp_OAMethod@fs,OpenTextFile,@fileOUT,@pdf,8,1DECLARESysKursorINSENSITIVESCROLLCURSORFORSELECTcodeFROM#pdfORDERBYidnumberFORREADONLYOPENSysKursorFETCHNEXTFROMSysKursorINTO@trenutniRedWHILE@@Fetch_Status=0BEGINEXECUTE@ole=sp_OAMethod@file,WriteLine,Null,@trenutniRedFETCHNEXTFROMSysKursorINTO@trenutniRedENDCLOSESysKursorDEALLOCATESysKursorDELETEFROMpsopdfEXECUTE@ole=sp_OADestroy@fileEXECUTE@ole=sp_OADestroy@fs


MySQL这个名字是怎么来的已经不清楚了。基本指南和大量的库和工具带有前缀“my”已经有10年以上，而且不管怎样，MySQLAB创始人之一的的女儿也叫My。

SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

对于微软系列的东西除了一遍遍尝试还真没有太好的办法

记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

大侠们有推荐的书籍和学习方法写下吧。

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);