SET ?QUOTED_IDENTIFIER? ON ?
GO
SET ?ANSI_NULLS? ON ?
GO



ALTER ??? proc ?stp_ExportTable
???? @sqlstr ? nvarchar ( 4000 ),???????? -- 查詢語句,如果查詢語句中使用了order?by?,請加上top?100?percent,注意,如果導出表/視圖,用上面的存儲過程
???? @orderstr ? nvarchar ( 255 ),???? -- Order?by?Field
???? @path ? nvarchar ( 1000 ),??????? -- 文件存放目錄
???? @fname ? nvarchar ( 250 ),??????? -- 文件名
???? @sheetname ? varchar ( 250 ) = '' ?????? -- 要創建的工作表名,默認為文件名
as ?
???? declare ? @err ? int , @src ? nvarchar ( 255 ), @desc ? nvarchar ( 255 ), @out ? int
???? declare ? @obj ? int , @constr ? nvarchar ( 1000 ), @sql ? varchar ( 4000 ), @fdlist ? varchar ( 8000 )
???? declare ? @IstmpTB ? as ? bit
???? declare ? @tmpsql ?? as ? varchar ( 4000 )


???? SET ? @IstmpTB = 0
???? -- 參數檢測
???? if ? isnull ( @fname , '' ) = '' ? set ? @fname = ' temp.xls '
???? if ? isnull ( @sheetname , '' ) = '' ? set ? @sheetname = replace ( @fname , ' . ' , ' # ' )
????
???? -- 檢查文件是否已經存在
???? if ? right ( @path , 1 ) <> ' ' ? set ? @path = @path + ' '
???? create ? table ?#tb(a? bit ,b? bit ,c? bit )
???? set ? @sql = @path + @fname

???? -- 數據庫創建語句
???? Insert ? into ?#tb? exec ?master..xp_fileexist? @sql
???? set ? @sql = @path + @fname
????
???? if ? exists ( select ? 1 ? from ?#tb? where ?a = 1 )
????? set ? @constr = ' DRIVER={Microsoft?Excel?Driver?(*.xls)};DSN= '''' ;READONLY=FALSE '
??????????? + ' ;CREATE_DB=" ' + @sql + ' ";DBQ= ' + @sql
???? else
????? set ? @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Extended?Properties="Excel?8.0;HDR=YES '
???????? + ' ;DATABASE= ' + @sql + ' " '



???? -- 連接數據庫
???? -- print?'nn33'
???? exec ? @err = sp_oacreate? ' adodb.connection ' , @obj ?out
???? if ? @err <> 0 ? goto ?lberr
???? -- print?'nn44'
???? exec ? @err = sp_oamethod? @obj , ' open ' , null , @constr
???? if ? @err <> 0 ? goto ?lberr

???? -- 構造temp表的SQL
???? declare ? @tbname ?sysname
???? set ? @tbname = ' ##tmp_ ' + convert ( varchar ( 38 ), newid ())
???? set ? @sql = ' select?*?into?[ ' + @tbname + ' ]?from( ' + @sqlstr + ' )?a '
???? -- print?@sql
???? exec ( @sql )
???? set ? @IstmpTB = 1
????
???? select ? @sql = '' , @fdlist = ''
???? select ? @fdlist = @fdlist + ' ,[ ' + a.name + ' ]? '
?????, @sql = @sql + ' ,[ ' + a.name + ' ]? '
?????? + case ? when ?b.name? in ( ' char ' , ' nchar ' , ' varchar ' , ' nvarchar ' )? then
????????? ' text( ' + cast ( case ? when ?a.length > 255 ? then ? 255 ? else ?a.length? end ? as ? varchar ) + ' ) '
??????? when ?b.name? in ( ' bit ' , ' int ' , ' bigint ' , ' tinyint ' , ' smallint ' )? then ? ' int '
??????? when ?b.name? in ( ' smalldatetime ' , ' datetime ' )? then ? ' datetime '
??????? when ?b.name? in ( ' money ' , ' smallmoney ' )? then ? ' money '
??????? else ?b.name? end
???? FROM ?tempdb..syscolumns?a? left ? join ?tempdb..systypes?b? on ?a.xtype = b.xusertype
???? where ?b.name? not ? in ( ' image ' , ' text ' , ' uniqueidentifier ' , ' sql_variant ' , ' ntext ' , ' varbinary ' , ' binary ' , ' timestamp ' )
????? and ?a.id = ( select ?id? from ?tempdb..sysobjects? where ?name = @tbname )
????
???? select ? @sql = substring ( @sql , 2 , 2000 ), @fdlist = substring ( @fdlist , 2 , 2000 )

???? -- create?table
???? select ? @sql = ' create?table?[ ' + @sheetname + ' ]( ' + @sql + ' ) '
???? -- print?@sql
???? exec ? @err = sp_oamethod? @obj , ' execute ' , @out ?out, @sql
???? if ? @err <> 0 ? goto ?lberr
???? -- print?'nn'
???? -- destroy?ole?object
???? exec ? @err = sp_oadestroy? @obj
???? if ? @err <> 0 ? goto ?lberr
???? -- print?'nn1'
???? -- 導入數據
????
???? set ? @sql = ' openrowset( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel?8.0;HDR=YES
???????;DATABASE= ' + @path + @fname + ''' ,[ ' + @sheetname + ' $]) '
????
???? set ? @tmpsql = ' insert?into? ' + @sql + ' ( ' + @fdlist + ' )?select? ' + @fdlist + ' ?from?[ ' + @tbname + ' ] '
???? -- print?@tmpsql
???? if ? @orderstr ? is ? not ? null ? or ? @orderstr <> ''
???? begin
???????? set ? @tmpsql = @tmpsql + ' ?order?by? ' + @orderstr
???? end
???? -- print?@tmpsql????
???? exec ( @tmpsql )
????
???? set ? @sql = ' drop?table?[ ' + @tbname + ' ] '
???? exec ( @sql )
???? set ? @IstmpTB = 0
????
???? return ? 0

lberr:
???
??????? EXEC ?sp_displayoaerrorinfo? @obj ,? @err
???? -- DELETE?TmpTable?While?Error?
???? IF ? @IstmpTB = 1 ?
???? BEGIN
???????? set ? @sql = ' drop?table?[ ' + @tbname + ' ] '
???????? exec ( @sql )
???? END
???? return ? - 1
lbexit:
???? SELECT ? @sql , @constr , @fdlist


GO
SET ?QUOTED_IDENTIFIER? OFF ?
GO
SET ?ANSI_NULLS? ON ?
GO