MSSQL编程：SQL to Excel 的使用 people,return,create,excel,数据库

莫相离 发表于 2015-1-16 22:39:45

MSSQL编程：SQL to Excel 的使用

MySQL的海豚标志的名字叫“sakila”，它是由MySQLAB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者AmbroseTwebaze提供。excel
参考网上材料，本人做的SQLtoExcel事例，
1、必要先安装MS的事例数据库：pubs
2、事后已有的c: emp est.xls(macro代码已写好，包括sheet1和people两张sheet)
3、实行此SQL，可把数据导进test.xls
4、翻开test.xls，按按钮，可发生数据的图表

SQL：
---------------------------------------------------------------------------------------------------------------------------
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.xls
--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,Zipnumeric)
--%%%T-SQLfortablepopulation,notethe4partnamingrequiredbyJet4OLEDB
--INSERTINTOSELECT,INSERTINTOVALUES,andEXECsptypesaresupported
--LinkedServerdoesnotsupportSELECTINTOtypes
SET@SQL=INSERTINTO+@ServerName+...+@WKS_Name+(SSN,Name,Phone,Zip)
SET@SQL=@SQL+SELECTau_idASSSN
SET@SQL=@SQL+,LTRIM(RTRIM(ISNULL(au_fname,)++ISNULL(au_lname,)))ASName
SET@SQL=@SQL+,phoneASPhone
SET@SQL=@SQL+,zipASZip
SET@SQL=@SQL+FROMpubs.dbo.authors
SET@SQL=@SQL+orderbyzip

print1：+@SQL

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

print2：
--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
---------------------------------------------------------------------------------------------------------------------------------------------------

Excel.sheet1.CommandButton宏代码：
-----------------------------------------------------------------------
PrivateSubCommandButton1_Click()
Dimb_PAsBoolean

b_P=False
Fori=1ToSheets.Count
IfSheets(i).Name="People"Then
b_P=True
ExitFor
EndIf
Nexti
Ifb_P=FalseThenExitSub

Charts.Add
ActiveChart.ChartType=xlColumnClustered
ActiveChart.SetSourceDataSource:=Sheets("People").Range("B1:D24"),PlotBy_
:=xlColumns
ActiveChart.SeriesCollection(1).XValues="=People!R2C2:R24C2"
ActiveChart.LocationWhere:=xlLocationAsObject,Name:="Sheet1"
WithActiveChart
.HasTitle=True
.ChartTitle.Characters.Text="Zip"
.Axes(xlCategory,xlPrimary).HasTitle=False
.Axes(xlValue,xlPrimary).HasTitle=False
EndWith

EndSub
----------------------------------------------------------------------------------

mysql的prepare其实是本地PHP客户端模拟的，并没有根据你mysql的设置做字符集的调整。应该交与mysqlserver端做prepare，同时得调用mysql_set_character_set去操作，server才会按照字符集去做转义。

灵魂腐蚀 发表于 2015-1-19 20:55:03

学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

柔情似水 发表于 2015-1-27 11:22:25

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

莫相离 发表于 2015-2-5 07:15:06

比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。

愤怒的大鸟 发表于 2015-2-11 08:04:56

光写几个SQL实在叫无知。

若天明 发表于 2015-3-2 00:13:00

比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。

老尸发表于 2015-3-11 01:26:14

从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。

山那边是海 发表于 2015-3-17 18:08:20

sqlserver的痛苦之处在于有用文档的匮乏，很多只是表明的东西

冷月葬花魂 发表于 2015-3-17 18:08:20

备份方面可能还是一个老大难的问题。不能单独备份几个表总是感觉不爽。灵活备份的问题不知道什么时候才能解决。

飘灵儿 发表于 2015-3-24 17:45:23

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

页: [1]

仓酷云's Archiver

MSSQL编程：SQL to Excel 的使用