SQLto的应用

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

参考网上资料，自己做的SQLtoExcel 事例，
   1、需要先安装MS的事例：pubs
   2、预先已有的c:/temp/test.xls(macro代码已写好，包含'sheet1'和'people'两张sheet)
   3、执行此SQL，可把数据导入test.xls
   4、打开test.xls，按按钮，可产生数据的图表




SQL：
---------------------------------------------------------------------------------------------------------------------------
PRINT'BeginCreateXLSscriptat'+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
--  mustbeaccessablefromserverviaserviceaccount
--  &SQLServerAgentserviceaccount,ifscheduled
SET@Path='C:/TEMP/Test.xls'
--SET@Path='C:/TEMP/Test_'+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'

print'1：'+@SQL

IF@Log=1PRINT'CreatedOLEADODB.Connectionobject'
--CreatetheConnobject
EXEC@hr=sp_OACreate'ADODB.Connection',@ConnOUT1<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>