SQL Server Task Export excle

The--SQL statement uses the following stored procedure

/*--Data Export Excel

Export the data in the query to Excel, including the field name, the file as the real Excel file
If the file does not exist, the file will be created automatically
The table is created automatically if the table does not exist
Based on commonality considerations, only export of standard data types is supported

How to use:

Direct copy execution Create stored procedure
--Chen 2014.04--*/

/*--Invocation Example

P_EXPORTTB @sqlstr = ' select * from table name ', @path = ' \ c ', @fname = ' Aa.xls ', @sheetname = ' Sheet1 '
--*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_EXPORTTB] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_EXPORTTB]
GO
Create proc P_EXPORTTB
@sqlstr sysname,--query statement, if you use order by in the query statement, add top percent
@path nvarchar (1000),--File storage directory
@fname nvarchar (250),--File name
@sheetname varchar (250) = "--the name of the sheet to create, default to file name
As
declare @err int, @src nvarchar (255), @desc nvarchar (255), @out int
declare @obj int, @constr nvarchar (+), @sql varchar (8000), @fdlist varchar (8000)

--Parameter detection
If IsNull (@fname, ') = ' Set @fname = ' Temp.xls '
If IsNull (@sheetname, ') = ' Set @sheetname =replace (@fname, '. ', ' # ')

--Check if the file already exists
If Right (@path, 1) <> ' \ ' set @[email protected]+ ' \ '
CREATE TABLE #tb (a bit,b bit,c bit)
Set @[email Protected][email protected]
INSERT into #tb exec master. Xp_fileexist @sql

--Database Creation statement
Set @[email Protected][email protected]
if exists (select 1 from #tb where a=1)
Set @constr = ' Driver={microsoft Excel DRIVER (*.xls)};D sn= ""; Readonly=false '
+ '; create_db= "' [email protected]+ '";D bq= ' [email protected]
Else
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "Excel 5.0; Hdr=yes '
+ ';D atabase= ' [email protected]+ ' '

--Connect to database
exec @err =sp_oacreate ' adodb.connection ', @obj out
If @err <>0 goto Lberr

EXEC @err =sp_oamethod @obj, ' open ', NULL, @constr
If @err <>0 goto Lberr

--Create the SQL for the table
declare @tbname sysname
Set @tbname = ' # #tmp_ ' +convert (varchar), newid ())
Set @sql = ' select * into [' [E-mail protected]+ '] from (' [email protected]+ ') a '
EXEC (@sql)

Select @sql = ", @fdlist ="
Select @[email protected]+ ', ' +a.name
, @[email protected]+ ', [' +a.name+ '] '
+case when b.name in (' char ', ' nchar ', ' varchar ', ' nvarchar ') then
' Text (' +cast (a.length>255 then 255 else a.length end as varchar) + ') '
When b.name in (' Tynyint ', ' int. ', ' bigint ', ' tinyint ') 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 [email protected])
Select @sql = ' CREATE TABLE [' [email protected]
+ '] (' +substring (@sql, 2,8000) + ') '
, @fdlist =substring (@fdlist, 2,8000)

EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
If @err <>0 goto Lberr

EXEC @err =sp_oadestroy @obj

--Import data
Set @sql = ' OpenRowset (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes
;D atabase= ' [email protected][email protected]+ ', [' [Email protected]+ ' $] '

EXEC (' insert INTO ' [email protected]+ ' (' [email protected]+ ') SELECT ' [E-mail protected]+ ' from [' [email protected]+ '] ')

Set @sql = ' drop table [' [Email protected]+ '] '
EXEC (@sql)
Return

Lberr:
EXEC sp_OAGetErrorInfo 0, @src out, @desc out
Lbexit:
Select CAST (@err as varbinary (4)) as error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist

SQL Server Task Export excle