July 16, 2009 at 8:43 am
Hi,
Is there any option in t-sql that can send me the result of a select statement to a excel file?
thank you,
Pedro
July 16, 2009 at 1:31 pm
Yes, there is. But it's not like "just add an output=EXCEL parameter"...
An example can be found here [/url].
Another option using an DTS package can be found here [/url].
For more details you might want to search this site for "T-sql export excel" or give a more detailed explanation what you're looking for.
July 17, 2009 at 7:48 am
thank you very much,
I have tried to implement the model, but i have a problem.
Please, look at my script , to see what i mean:
-- 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
--
--alter proc CartoesEmitidosSIAC
--as
-- select
-- year(a.dt_inicio) as ANO_CADASTRAMENTO,
-- convert(varchar(10),month(a.dt_inicio),120) as MES_CADASTRAMENTO,
-- right(a.cod_met_tributario,1) as METODO_TRIBUTARIO,
-- a.NOME,
-- a.NIF,
-- a.nif_antigo as RGC,
-- c.descricao as CADASTRO,
-- b.descricao as SEDE
-- from
-- sgctcentral.dbo.contribuintes as a, rep_fiscais_ as b, rep_fiscais_ as c
-- where
-- a.nif_antigo like 's4.19%'
-- and a.cod_rep_fiscal =b.codigo and replace(convert(varchar(5),a.nif_antigo),'s','') =c.codigo
-- and a.nif_antigo not in
-- (select nif_antigo from cartoes)
-- order by
-- year(a.dt_inicio),month(a.dt_inicio), a.cod_met_tributario, a.nome
--
--go
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:\excel\'+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 = 'SIAC'
-- %%% Rename Table as required, this will also be the XLS Worksheet name
SET @WKS_Name = 'CartoesPorEmitir'
-- %%% Table creation DDL, uses Jet4 syntax,
-- Text data type = varchar(255) when accessed from T-SQL
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (ANO_CADASTRAMENTO number, MES_CADASTRAMENTO number, METODO_TRIBUTARIO number,NOME text, NIF text,RGC text,CADASTRAMENTO text,SEDE 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+' (ANO_CADASTRAMENTO, MES_CADASTRAMENTO, METODO_TRIBUTARIO, NOME, NIF, RGC, CADASTRAMENTO, SEDE) '
SET @sql = @sql+' year(a.dt_inicio) as ANO_CADASTRAMENTO,'
SET @sql = @sql+' convert(varchar(10),month(a.dt_inicio),120) as MES_CADASTRAMENTO,'
SET @sql = @sql+' right(a.cod_met_tributario,1) as METODO_TRIBUTARIO,'
SET @sql = @sql+' a.nif_antigo as RGC,'
SET @sql = @sql+' c.descricao as CADASTRO,'
SET @sql = @sql+' b.descricao as SEDE'
SET @sql = @sql+' sgctcentral.dbo.contribuintes as a, rep_fiscais_ as b, rep_fiscais_ as c'
SET @sql = @sql+' a.nif_antigo like ''s4.19%'''
SET @sql = @sql+' and a.cod_rep_fiscal =b.codigo and replace(convert(varchar(5),a.nif_antigo),''s'','''') =c.codigo'
SET @sql = @sql+' and a.nif_antigo not in'
SET @sql = @sql+' (select nif_antigo from cartoes)'
SET @sql = @sql+' year(a.dt_inicio),month(a.dt_inicio), a.cod_met_tributario, convert(varchar(8000),a.nome,120)'
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 object's 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, can't 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 Excel's 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
the error is this:
Begin CreateXLS script at 2009-07-17 14:39:14.210
Created OLE ADODB.Connection object
Assigned ConnectionString property
Open Connection to XLS, for file Create or Append
Execute DDL to create 'CartoesPorEmitir' worksheet
Destroyed OLE ADODB.Connection object
Created Linked Server 'SIAC' and Login
Populated 'CartoesPorEmitir' table with 4590 Rows
Deleted Linked Server 'SIAC' and Login
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm having this problems, because i need to put in the order by clause
the column nome.
When i put the column nome in the order by clause and do not make a conversion to
varchar, then other error is raised "order by can not have text,ntext,or image data"
Do you have any ideia how can i revolve the question?
thank you
July 17, 2009 at 9:38 am
Hi masters,
I have now putted the code as below and i noticed that when the
code is executed and there is no Excel file yet, it gives me an error , when
the file xls is allready created by this t-sql, that i don't receive the error and data is append to the file with no problem.
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
--
--alter proc CartoesEmitidosSIAC
--as
-- select
-- year(a.dt_inicio) as ANO_CADASTRAMENTO,
-- convert(varchar(10),month(a.dt_inicio),120) as MES_CADASTRAMENTO,
-- right(a.cod_met_tributario,1) as METODO_TRIBUTARIO,
-- a.NOME,
-- a.NIF,
-- a.nif_antigo as RGC,
-- c.descricao as CADASTRO,
-- b.descricao as SEDE
-- from
-- sgctcentral.dbo.contribuintes as a, rep_fiscais_ as b, rep_fiscais_ as c
-- where
-- a.nif_antigo like 's4.19%'
-- and a.cod_rep_fiscal =b.codigo and replace(convert(varchar(5),a.nif_antigo),'s','') =c.codigo
-- and a.nif_antigo not in
-- (select nif_antigo from cartoes)
-- order by
-- year(a.dt_inicio),month(a.dt_inicio), a.cod_met_tributario, a.nome
--
--go
IF EXISTS (SELECT name FROM sysobjects WHERE name ='tmp_XLS' )
DROP TABLE [dbo].[tmp_XLS]
go
create table tmp_XLS
(
ANO_CADASTRAMENTO varchar(100) not null,
MES_CADASTRAMENTO varchar(100) not null,
METODO_TRIBUTARIO varchar(100) not null,
NOME varchar(500) not null,
NIF varchar(21) not null,
RGC varchar(21) not null,
CADASTRO varchar(200) not null,
SEDE varchar(200) not null
)
go
insert into tmp_xls
select
year(a.dt_inicio) as ANO_CADASTRAMENTO,
convert(varchar(10),month(a.dt_inicio),120) as MÊS_CADASTRAMENTO,
right(a.cod_met_tributario,1) as MÉTODO_TRIBUTÁRIO,
a.NOME,
a.NIF,
a.nif_antigo as RGC,
c.descricao as CADASTRO,
b.descricao as SEDE
from
sgctcentral.dbo.contribuintes as a, rep_fiscais_ as b, rep_fiscais_ as c
where
a.nif_antigo like 's4.19%'
and a.cod_rep_fiscal =b.codigo and replace(convert(varchar(5),a.nif_antigo),'s','') =c.codigo
and a.nif_antigo not in
(select nif_antigo from cartoes)
order by
year(a.dt_inicio),month(a.dt_inicio), a.cod_met_tributario,a.nome
go
--drop table tmp_xls
--go
PRINT 'Begin CreateXLS script at '+convert(varchar(20),GETDATE(),120)+' '
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:\excel\'+replace(replace(replace(convert(varchar,convert(smalldatetime,getdate()),120),' ',' '),'-',''),':','')+'.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 = 'SIAC'
-- %%% Rename Table as required, this will also be the XLS Worksheet name
SET @WKS_Name = 'CartoesPorEmitir'
-- %%% Table creation DDL, uses Jet4 syntax,
-- Text data type = varchar(255) when accessed from T-SQL
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (ANO_CADASTRAMENTO number, MES_CADASTRAMENTO number, METODO_TRIBUTARIO number, NOME text, NIF text,RGC text,CADASTRAMENTO text,SEDE 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+' (ANO_CADASTRAMENTO, MES_CADASTRAMENTO, METODO_TRIBUTARIO, NOME, NIF, RGC, CADASTRAMENTO, SEDE) '
SET @sql = @sql+' select * from tmp_xls'
--SET @sql = @sql+' year(a.dt_inicio) as ANO_CADASTRAMENTO,'
--SET @sql = @sql+' convert(varchar(10),month(a.dt_inicio),120) as MES_CADASTRAMENTO,'
--SET @sql = @sql+' right(a.cod_met_tributario,1) as METODO_TRIBUTARIO,'
--SET @sql = @sql+' a.nif_antigo as RGC,'
--SET @sql = @sql+' c.descricao as CADASTRO,'
--SET @sql = @sql+' b.descricao as SEDE'
--SET @sql = @sql+' sgctcentral.dbo.contribuintes as a, rep_fiscais_ as b, rep_fiscais_ as c'
--SET @sql = @sql+' a.nif_antigo like ''s4.19%'''
--SET @sql = @sql+' and a.cod_rep_fiscal =b.codigo and replace(convert(varchar(5),a.nif_antigo),''s'','''') =c.codigo'
--SET @sql = @sql+' and a.nif_antigo not in'
--SET @sql = @sql+' (select nif_antigo from cartoes)'
--SET @sql = @sql+' order by '
--SET @sql = @sql+' year(convert(int,a.dt_inicio)),month(convert(int,a.dt_inicio)), a.cod_met_tributario, convert(varchar(8000),a.nome,120)'
--
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 object's 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, can't 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 Excel's 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
error:
4590 row(s) affected)
Begin CreateXLS script at 2009-07-17 16:36:55
Created OLE ADODB.Connection object
Assigned ConnectionString property
Open Connection to XLS, for file Create or Append
Execute DDL to create 'CartoesPorEmitir' worksheet
Destroyed OLE ADODB.Connection object
Created Linked Server 'SIAC' and Login
Populated 'CartoesPorEmitir' table with 4590 Rows
Deleted Linked Server 'SIAC' and Login
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Finished CreateXLS script at 2009-07-17 16:36:57.677
I need that each time the code runs , it creates a new file, so this can not fail
July 21, 2009 at 2:03 am
can someone help me , please?
I'm realy don't know how to fix this
June 16, 2011 at 8:28 pm
I'm hitting this one too. Anyone have an answer?
I'm running this command:
...
SET @DDL = 'CREATE TABLE ReportName ([fieldName1] Text, [fieldName2] Text, [fieldName3] Text)'
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords
SELECT @hr
...
Result: Excel file is created and populated by the rest of my script, but I can't trap the error. If the file does not exist, @hr is never defined in SQL Server.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply