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>
阅读更多
文章标签: sql excel path table c
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭