社区 › MSSQL数据库开发 › MSSQL网站制作之SQL导出数据到EXCEL文件

小魔女 发表于 2015-1-16 22:34:27

MSSQL网站制作之SQL导出数据到EXCEL文件

一个语句分成两个event(实际上不止，其他可以忽略)，一个table_mapevent和一个Rows_log_event。Table_mapevent是一样的，主要看Rows_log_event。excel|导出数据
CreateExcelXLSfromT-SQL
---------------------------------------------------------------------------
--CreateXLSscriptDAL-04/24/2003
--
--DesignedforAgentscheduling,turnon"Appendoutputforstephistory"
--
--Searchfor%%%tofindadjustableconstantsandotheroptions
--
--UsesOLEforADOandOLEDBtocreatetheXLSfileifitdoesnotexist
--LinkedserverrequirestheXLStoexistbeforecreation
--UsesOLEADOtoCreatetheXLSWorksheetforuseasatablebyT-SQL
--UsesLinkedServertoallowT-SQLaccesstoXLStable
--UsesT-SQLtopopulateteXLSworksheet,veryfast
--
PRINTBeginCreateXLSscriptat+RTRIM(CONVERT(varchar(24),GETDATE(),121))+
PRINT
GO

SETNOCOUNTON
DECLARE@Connint--ADOConnectionobjecttocreateXLS
,@hrint--OLEreturnvalue
,@srcvarchar(255)--OLEErrorSource
,@descvarchar(255)--OLEErrorDescription
,@Pathvarchar(255)--DriveorUNCpathforXLS
,@Connectvarchar(255)--OLEDBConnectionstringforJet4ExcelISAM
,@WKS_Createdbit--WhethertheXLSWorksheetexists
,@WKS_Namevarchar(128)--NameoftheXLSWorksheet(table)
,@ServerNamenvarchar(128)--LinkedServernameforXLS
,@DDLvarchar(8000)--Jet4DDLfortheXLSWKStablecreation
,@SQLvarchar(8000)--INSERTINTOXLST-SQL
,@Recsint--NumberofrecordsaddedtoXLS
,@Logbit--Whethertologprocessdetail

--Initvariables
SELECT@Recs=0
--%%%1=Verboseoutputdetail,helpsfindproblems,0=minimaloutputdetail
,@Log=1
--%%%assigntheUNCorpathandnamefortheXLSfile,requiresRead/Writeaccess
--mustbeaccessablefromserverviaSQLServerserviceaccount
--&SQLServerAgentserviceaccount,ifscheduled
SET@Path=C:TEMPTest_+CONVERT(varchar(10),GETDATE(),112)+.xls
--assigntheADOconnectionstringfortheXLScreation
SET@Connect=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=+@Path+;ExtendedProperties=Excel8.0
--%%%assigntheLinkedServernamefortheXLSpopulation
SET@ServerName=EXCEL_TEST
--%%%RenameTableasrequired,thiswillalsobetheXLSWorksheetname
SET@WKS_Name=People
--%%%TablecreationDDL,usesJet4syntax,
--Textdatatype=varchar(255)whenaccessedfromT-SQL
SET@DDL=CREATETABLE+@WKS_Name+(SSNText,NameText,PhoneText)
--%%%T-SQLfortablepopulation,notethe4partnamingrequiredbyJet4OLEDB
--INSERTINTOSELECT,INSERTINTOVALUES,andEXECsptypesaresupported
--LinkedServerdoesnotsupportSELECTINTOtypes
SET@SQL=INSERTINTO+@ServerName+...+@WKS_Name+(SSN,Name,Phone)
SET@SQL=@SQL+SELECTau_idASSSN
SET@SQL=@SQL+,LTRIM(RTRIM(ISNULL(au_fname,)++ISNULL(au_lname,)))ASName
SET@SQL=@SQL+,phoneASPhone
SET@SQL=@SQL+FROMpubs.dbo.authors

IF@Log=1PRINTCreatedOLEADODB.Connectionobject
--CreatetheConnobject
EXEC@hr=sp_OACreateADODB.Connection,@ConnOUT
IF@hr0--havetouseasOLE/ADOcanreturnnegativeerrornumbers
BEGIN
--ReturnOLEerror
EXECsp_OAGetErrorInfo@Conn,@srcOUT,@descOUT
SELECTError=convert(varbinary(4),@hr),Source=@src,Description=@desc
RETURN
END

IF@Log=1PRINTchar(9)+AssignedConnectionStringproperty
--SetatheConnobjectsConnectionStringproperty
--Work-aroundforerrorusingavariableparameterontheOpenmethod
EXEC@hr=sp_OASetProperty@Conn,ConnectionString,@Connect
IF@hr0
BEGIN
--ReturnOLEerror
EXECsp_OAGetErrorInfo@Conn,@srcOUT,@descOUT
SELECTError=convert(varbinary(4),@hr),Source=@src,Description=@desc
RETURN
END

IF@Log=1PRINTchar(9)+OpenConnectiontoXLS,forfileCreateorAppend
--CalltheOpenmethodtocreatetheXLSifitdoesnotexist,cantuseparameters
EXEC@hr=sp_OAMethod@Conn,Open
IF@hr0
BEGIN
--ReturnOLEerror
EXECsp_OAGetErrorInfo@Conn,@srcOUT,@descOUT
SELECTError=convert(varbinary(4),@hr),Source=@src,Description=@desc
RETURN
END

--%%%ThissectioncouldberepeatedformultipleWorksheets(Tables)
IF@Log=1PRINTchar(9)+ExecuteDDLtocreate+@WKS_Name+worksheet
--CalltheExecutemethodtoCreatetheworksheetwiththe@WKS_Namecaption,
--whichisalsousedasaTablereferenceinT-SQL
--NeatwaytodefinecolumndatatypesinExcelworksheet
--Sometimesconvertingtotextistheonlywork-aroundforExcelsGeneral
--Cellformatting,eventhoughtheCellcontainsText,Exceltriestoformat
--itina"Smart"way,Ihaveevenhadtousethesinglequoteappendedasthe
--1stcharacterinT-SQLtoforceExceltoleaveitalone
EXEC@hr=sp_OAMethod@Conn,Execute,NULL,@DDL,NULL,129--adCmdText+adExecuteNoRecords
--0x80040E14fortableexistsinADO
IF@hr=0x80040E14
--kludge,skip0x80042732forADOOptionalparameters(NULL)inSQL7
OR@hr=0x80042732
BEGIN
--TraptheseOLEErrors
IF@hr=0x80040E14
BEGIN
PRINTchar(9)++@WKS_Name+Worksheetexistsforappend
SET@WKS_Created=0
END
SET@hr=0--ignoretheseerrors
END
IF@hr0
BEGIN
--ReturnOLEerror
EXECsp_OAGetErrorInfo@Conn,@srcOUT,@descOUT
SELECTError=convert(varbinary(4),@hr),Source=@src,Description=@desc
RETURN
END

IF@Log=1PRINTDestroyedOLEADODB.Connectionobject
--DestroytheConnobject,+++importanttonotleakmemory+++
EXEC@hr=sp_OADestroy@Conn
IF@hr0
BEGIN
--ReturnOLEerror
EXECsp_OAGetErrorInfo@Conn,@srcOUT,@descOUT
SELECTError=convert(varbinary(4),@hr),Source=@src,Description=@desc
RETURN
END

--LinkedServerallowsT-SQLtoaccesstheXLSworksheet(Table)
--ThismustbeperformedaftertheADOstuffastheXLSmustexist
--andcontaintheschemaforthetable,orworksheet
IFNOTEXISTS(SELECTsrvnamefrommaster.dbo.sysserverswheresrvname=@ServerName)
BEGIN
IF@Log=1PRINTCreatedLinkedServer+@ServerName+andLogin
EXECsp_addlinkedserver@server=@ServerName
,@srvproduct=MicrosoftExcelWorkbook
,@provider=Microsoft.Jet.OLEDB.4.0
,@datasrc=@Path
,@provstr=Excel8.0
--nologinnameorpasswordarerequiredtoconnecttotheJet4ISAMlinkedserver
EXECsp_addlinkedsrvlogin@ServerName,false
END

--HavetoEXECtheSQL,otherwisetheSQLisevaluated
--forthelinkedserverbeforeitexists
EXEC(@SQL)
PRINTchar(9)+Populated+@WKS_Name+tablewith+CONVERT(varchar,@@ROWCOUNT)+Rows

--%%%OptionalyoumayleavetheLinkedServerforotherXLSoperations
--RememberthattheLinkedServerwillnotcreatetheXLS,soremoveit
--Whenyouaredonewithit,especiallyifyoudeleteormovethefile
IFEXISTS(SELECTsrvnamefrommaster.dbo.sysserverswheresrvname=@ServerName)
BEGIN
IF@Log=1PRINTDeletedLinkedServer+@ServerName+andLogin
EXECsp_dropserver@ServerName,droplogins
END
GO

SETNOCOUNTOFF
PRINT
PRINTFinishedCreateXLSscriptat+RTRIM(CONVERT(varchar(24),GETDATE(),121))+
GO

目标
SET@Path=C:TEMPTest_+CONVERT(varchar(10),GETDATE(),112)+.xls

源
SET@SQL=INSERTINTO+@ServerName+...+@WKS_Name+(SSN,Name,Phone)
SET@SQL=@SQL+SELECTau_idASSSN
SET@SQL=@SQL+,LTRIM(RTRIM(ISNULL(au_fname,)++ISNULL(au_lname,)))ASName
SET@SQL=@SQL+,phoneASPhone
SET@SQL=@SQL+FROMpubs.dbo.authors

对于insert和delete，event中包含了插入/删除的记录的所有字段的值(太爽了。。)

小魔女 发表于 2015-1-19 16:55:54

SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.

活着的死人 发表于 2015-1-28 08:54:42

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

乐观 发表于 2015-2-5 20:17:55

还不是性能有问题！否则面向对象的数据库早就实现了！建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。

谁可相欹 发表于 2015-2-13 12:17:38

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

第二个灵魂 发表于 2015-3-3 21:16:08

这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。

柔情似水 发表于 2015-3-11 13:50:21

呵呵，这就是偶想说的

山那边是海 发表于 2015-3-18 21:30:09

我们学到了什么？思考问题的时候从表的角度来思考问

飘灵儿 发表于 2015-3-26 19:10:42

是要和操作系统进行Socket通讯的场景。否则建议慎重！

查看完整版本: MSSQL网站制作之SQL导出数据到EXCEL文件