IDC资讯中心
create excel xls from t-sql
—————————————————————————
— create xls script dal – 04/24/2003
—
— designed for agent scheduling, turn on “append output for step history”
—
— search for %%% to find adjustable constants and other options
—
— uses ole for ado and ole db to create the xls file if it does not exist
— linked server requires the xls to exist before creation
— uses ole ado to create the xls worksheet for use as a table by t-sql
— uses linked server to allow t-sql access to xls table
— uses t-sql to populate te xls worksheet, very fast
—
print begin createxls script at +rtrim(convert(varchar(24),getdate(),121))+
print
go
set nocount on
declare @conn int — ado connection object to create xls
, @hr int — ole return value
, @src varchar(255) — ole error source
, @desc varchar(255) — ole error description
, @path varchar(255) — drive or unc path for xls
, @connect varchar(255) — ole db connection string for jet 4 excel isam
, @wks_created bit — whether the xls worksheet exists
, @wks_name varchar(128) — name of the xls worksheet (table)
, @servername nvarchar(128) — linked server name for xls
, @ddl varchar(8000) — jet4 ddl for the xls wks table creation
, @sql varchar(8000) — insert into xls t-sql
, @recs int — number of records added to xls
, @log bit — whether to log process detail
— init variables
select @recs = 0
— %%% 1 = verbose output detail, helps find problems, 0 = minimal output detail
, @log = 1
— %%% assign the unc or path and name for the xls file, requires read/write access
— must be accessable from server via sql server service account
— & sql server agent service account, if scheduled
set @path = c:\temp\test_+convert(varchar(10),getdate(),112)+.xls
— assign the ado connection string for the xls creation
set @connect = provider=microsoft.jet.oledb.4.0;data source=+@path+;extended properties=excel 8.0
— %%% assign the linked server name for the xls population
set @servername = excel_test
— %%% rename table as required, this will also be the xls worksheet name
set @wks_name = people
— %%% table creation ddl, uses jet4 syntax,
— text data type = varchar(255) when accessed from t-sql
set @ddl = create table +@wks_name+ (ssn text, name text, phone text)
— %%% t-sql for table population, note the 4 part naming required by jet4 ole db
— insert into select, insert into values, and exec sp types are supported
— linked server does not support select into types
set @sql = insert into +@servername+…+@wks_name+ (ssn, name, phone)
set @sql = @sql+select au_id as ssn
set @sql = @sql+, ltrim(rtrim(isnull(au_fname,)+ +isnull(au_lname,))) as name
set @sql = @sql+, phone as phone
set @sql = @sql+from pubs.dbo.authors
if @log = 1 print created ole adodb.connection object
— create the conn object
exec @hr = sp_oacreate adodb.connection, @conn out
if @hr <> 0 — have to use <> as ole / ado can return negative error numbers
begin
— return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
if @log = 1 print char(9)+assigned connectionstring property
— set a the conn objects connectionstring property
— work-around for error using a variable parameter on the open method
exec @hr = sp_oasetproperty @conn, connectionstring, @connect
if @hr <> 0
begin
— return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
if @log = 1 print char(9)+open connection to xls, for file create or append
— call the open method to create the xls if it does not exist, cant use parameters
exec @hr = sp_oamethod @conn, open
if @hr <> 0
begin
— return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
— %%% this section could be repeated for multiple worksheets (tables)
if @log = 1 print char(9)+execute ddl to create +@wks_name+ worksheet
— call the execute method to create the work sheet with the @wks_name caption,
— which is also used as a table reference in t-sql
— neat way to define column data types in excel worksheet
— sometimes converting to text is the only work-around for excels general
— cell formatting, even though the cell contains text, excel tries to format
— it in a “smart” way, i have even had to use the single quote appended as the
— 1st character in t-sql to force excel to leave it alone
exec @hr = sp_oamethod @conn, execute, null, @ddl, null, 129 — adcmdtext + adexecutenorecords
— 0x80040e14 for table exists in ado
if @hr = 0x80040e14
— kludge, skip 0x80042732 for ado optional parameters (null) in sql7
or @hr = 0x80042732
begin
— trap these ole errors
if @hr = 0x80040e14
begin
print char(9)++@wks_name+ worksheet exists for append
set @wks_created = 0
end
set @hr = 0 — ignore these errors
end
if @hr <> 0
begin
— return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
if @log = 1 print destroyed ole adodb.connection object
— destroy the conn object, +++ important to not leak memory +++
exec @hr = sp_oadestroy @conn
if @hr <> 0
begin
— return ole error
exec sp_oageterrorinfo @conn, @src out, @desc out
select error=convert(varbinary(4),@hr), source=@src, description=@desc
return
end
— linked server allows t-sql to access the xls worksheet (table)
— this must be performed after the ado stuff as the xls must exist
— and contain the schema for the table, or worksheet
if not exists(select srvname from master.dbo.sysservers where srvname = @servername)
begin
if @log = 1 print created linked server +@servername+ and login
exec sp_addlinkedserver @server = @servername
, @srvproduct = microsoft excel workbook
, @provider = microsoft.jet.oledb.4.0
, @datasrc = @path
, @provstr = excel 8.0
— no login name or password are required to connect to the jet4 isam linked server
exec sp_addlinkedsrvlogin @servername, false
end
— have to exec the sql, otherwise the sql is evaluated
— for the linked server before it exists
exec (@sql)
print char(9)+populated +@wks_name+ table with +convert(varchar,@@rowcount)+ rows
— %%% optional you may leave the linked server for other xls operations
— remember that the linked server will not create the xls, so remove it
— when you are done with it, especially if you delete or move the file
if exists(select srvname from master.dbo.sysservers where srvname = @servername)
begin
if @log = 1 print deleted linked server +@servername+ and login
exec sp_dropserver @servername, droplogins
end
go
set nocount off
print
print finished createxls script at +rtrim(convert(varchar(24),getdate(),121))+
go
目的
set @path = c:\temp\test_+convert(varchar(10),getdate(),112)+.xls
源
set @sql = insert into +@servername+…+@wks_name+ (ssn, name, phone)
set @sql = @sql+select au_id as ssn
set @sql = @sql+, ltrim(rtrim(isnull(au_fname,)+ +isnull(au_lname,))) as name
set @sql = @sql+, phone as phone
set @sql = @sql+from pubs.dbo.authors
