SQL导出数据到EXCEL文件

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

CreateXLSfromT-SQL
---------------------------------------------------------------------------
--CreateXLSscriptDAL-04/24/2003
--
--DesignedforAgentscheduling,turnon"Appendoutputforstephistory"
--
--Searchfor%%%tofindadjustableconstantsandotheroptions
--
--UsesOLEforADOandOLEDBtocreatetheXLSfileifitdoesnotexist
--  LinkedserverrequirestheXLStoexistbeforecreation
--UsesOLEADOtoCreatetheXLSWorksheetforuseasatablebyT-SQL
--UsesLinkedServertoallowT-SQLaccesstoXLStable
--UsesT-SQLtopopulateteXLSworksheet,veryfast
--
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_'+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=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>