SQL Server blocked access to procedure 'sys.sp_OACreate'

Exportacion a XLS mediante ADODB.Connection,

Solucione el anterior problema con:
sp_configure 'Ole Automation Procedures',1
Reconfigure

Ahora tengo el error:
Error 0x800A0E7A
Source: ADODB.Connection
Description: Provider cannot be found. It may not be proerly installed

no entiendo muy bien la descripcion de error, tengo que instalar algo??


"Pedro" wrote:

Estoy exportado a excel y me encuentro con unos errores saben por que puede
ser?
Os dajunto los erreres con un pequeño detalle:

Created OLE ADODB.Connection object
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OACreate, Línea 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole
Automation Procedures' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Assigned ConnectionString property
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OASetProperty, Línea 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of component
'Ole Automation Procedures' because this component is turned off as part of
the security configuration for this server. A system administrator can enable
the use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Open Connection to XLS, for file Create or Append
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole
Automation Procedures' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Execute DDL to create 'EXCELSQL' worksheet
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole
Automation Procedures' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Destroyed OLE ADODB.Connection object
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OADestroy, Línea 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole
Automation Procedures' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Created Linked Server 'EXCEL_TEST' and Login
Mens. 121, Nivel 15, Estado 1, Línea 1
The select list for the INSERT statement contains more items than the insert
list. The number of SELECT values must match the number of INSERT columns.
Populated 'EXCELSQL' table with 1 Rows
Deleted Linked Server 'EXCEL_TEST' and Login

Hola Pedro:

Me parece que tienes que instalar los MDAC. Busca en la web de microsoft

Saludos

"Pedro" escribió en el mensaje
news:

Exportacion a XLS mediante ADODB.Connection,

Solucione el anterior problema con:
sp_configure 'Ole Automation Procedures',1
Reconfigure

Ahora tengo el error:
Error 0x800A0E7A
Source: ADODB.Connection
Description: Provider cannot be found. It may not be proerly installed

no entiendo muy bien la descripcion de error, tengo que instalar algo??


"Pedro" wrote:

Estoy exportado a excel y me encuentro con unos errores saben por que
puede
ser?
Os dajunto los erreres con un pequeño detalle:

Created OLE ADODB.Connection object
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OACreate, Línea 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component
'Ole
Automation Procedures' because this component is turned off as part of
the
security configuration for this server. A system administrator can enable
the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Assigned ConnectionString property
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OASetProperty, Línea 1
SQL Server blocked access to procedure 'sys.sp_OASetProperty' of
component
'Ole Automation Procedures' because this component is turned off as part
of
the security configuration for this server. A system administrator can
enable
the use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Open Connection to XLS, for file Create or Append
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
'Ole
Automation Procedures' because this component is turned off as part of
the
security configuration for this server. A system administrator can enable
the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Execute DDL to create 'EXCELSQL' worksheet
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
'Ole
Automation Procedures' because this component is turned off as part of
the
security configuration for this server. A system administrator can enable
the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Destroyed OLE ADODB.Connection object
Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OADestroy, Línea 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component
'Ole
Automation Procedures' because this component is turned off as part of
the
security configuration for this server. A system administrator can enable
the
use of 'Ole Automation Procedures' by using sp_configure. For more
information about enabling 'Ole Automation Procedures', see "Surface Area
Configuration" in SQL Server Books Online.
Created Linked Server 'EXCEL_TEST' and Login
Mens. 121, Nivel 15, Estado 1, Línea 1
The select list for the INSERT statement contains more items than the
insert
list. The number of SELECT values must match the number of INSERT
columns.
Populated 'EXCELSQL' table with 1 Rows
Deleted Linked Server 'EXCEL_TEST' and Login

Juan Diego ,En todo el proceso del scrip donde me falla es aqui:
Por si te puede dar alguna idea:

EXEC @hr = sp_OAMethod @Conn, 'Open'

osea ejecutaria esto, me he sacado los valores de las variables:
sp_OAMethod 16711422, 'Open'

Voya apsar el proceso entero por si tienes alguna idea:

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

SELECT @Recs = 0
detail
, @Log = 1
access
SET @Path = 'C:\TEMP\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'EXCEL_TEST'
SET @WKS_Name = 'People' --nombre de la tabla en xls en munu
insert/name/define
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (SSN Text, Name Text, Phone Text)'
OLE DB
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (SSN, Name, Phone) '
SET @SQL = @SQL+'SELECT au_id AS SSN' -- se asigna el campo au_id d sql al
campo SSN dl xls
SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+''
''+ISNULL(au_lname,''''))) AS Name'
SET @SQL = @SQL+', phone AS Phone ' -- se asigna el campo phone d sql al
campo Phone dl xls
SET @SQL = @SQL+'FROM pubs.dbo.authors' --tabla de SQL de la que se sacan
los datos

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error numbers
BEGIN
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'
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
IF @hr <> 0
BEGIN
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'
parameters
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
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)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
caption,
format
as the
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText
+ adExecuteNoRecords
IF @hr = 0x80040E14
OR @hr = 0x80042732
BEGIN
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
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'
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

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'
linked server
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END

EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with
'+CONVERT(varchar,@@ROWCOUNT)+' Rows'

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










"Juan Diego Bueno" wrote:

Hola Pedro:

Me parece que tienes que instalar los MDAC. Busca en la web de microsoft

Saludos

"Pedro" escribió en el mensaje
news:
> Exportacion a XLS mediante ADODB.Connection,
>
> Solucione el anterior problema con:
> sp_configure 'Ole Automation Procedures',1
> Reconfigure
>
> Ahora tengo el error:
> Error 0x800A0E7A
> Source: ADODB.Connection
> Description: Provider cannot be found. It may not be proerly installed
>
> no entiendo muy bien la descripcion de error, tengo que instalar algo??
>
>
> "Pedro" wrote:
>
>> Estoy exportado a excel y me encuentro con unos errores saben por que
>> puede
>> ser?
>> Os dajunto los erreres con un pequeño detalle:
>>
>> Created OLE ADODB.Connection object
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OACreate, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OACreate' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface Area
>> Configuration" in SQL Server Books Online.
>> Assigned ConnectionString property
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OASetProperty, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OASetProperty' of
>> component
>> 'Ole Automation Procedures' because this component is turned off as part
>> of
>> the security configuration for this server. A system administrator can
>> enable
>> the use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface Area
>> Configuration" in SQL Server Books Online.
>> Open Connection to XLS, for file Create or Append
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface Area
>> Configuration" in SQL Server Books Online.
>> Execute DDL to create 'EXCELSQL' worksheet
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface Area
>> Configuration" in SQL Server Books Online.
>> Destroyed OLE ADODB.Connection object
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OADestroy, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OADestroy' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface Area
>> Configuration" in SQL Server Books Online.
>> Created Linked Server 'EXCEL_TEST' and Login
>> Mens. 121, Nivel 15, Estado 1, Línea 1
>> The select list for the INSERT statement contains more items than the
>> insert
>> list. The number of SELECT values must match the number of INSERT
>> columns.
>> Populated 'EXCELSQL' table with 1 Rows
>> Deleted Linked Server 'EXCEL_TEST' and Login

Bueno, yo te he dicho lo de instalarte MDAC por el error que te da, que
parece bastante claro. Otra cosa es que después a mayores te falle por otra
cosa, pero básicamente lo que te decía antes es que no encuentra el
proveedor ADO y eso teóricamente lo solucionas con la instalación del MDAC
(búscalo en la web de microsoft)

Saludos
"Pedro" escribió en el mensaje
news:

Juan Diego ,En todo el proceso del scrip donde me falla es aqui:
Por si te puede dar alguna idea:

EXEC @hr = sp_OAMethod @Conn, 'Open'

osea ejecutaria esto, me he sacado los valores de las variables:
sp_OAMethod 16711422, 'Open'

Voya apsar el proceso entero por si tienes alguna idea:

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

SELECT @Recs = 0
detail
, @Log = 1
Read/Write
access
SET @Path = 'C:\TEMP\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'EXCEL_TEST'
SET @WKS_Name = 'People' --nombre de la tabla en xls en munu
insert/name/define
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (SSN Text, Name Text, Phone Text)'
OLE DB
supported
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (SSN, Name, Phone)
'
SET @SQL = @SQL+'SELECT au_id AS SSN' -- se asigna el campo au_id d sql al
campo SSN dl xls
SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+''
''+ISNULL(au_lname,''''))) AS Name'
xls
SET @SQL = @SQL+', phone AS Phone ' -- se asigna el campo phone d sql al
campo Phone dl xls
SET @SQL = @SQL+'FROM pubs.dbo.authors' --tabla de SQL de la que se sacan
los datos

IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error
numbers
BEGIN
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'
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
IF @hr <> 0
BEGIN
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'
parameters
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
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)+'Execute DDL to create '''+@WKS_Name+'''
worksheet'
caption,
General
format
as the
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 --
adCmdText
+ adExecuteNoRecords
IF @hr = 0x80040E14
OR @hr = 0x80042732
BEGIN
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
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'
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

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'
linked server
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END

EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with
'+CONVERT(varchar,@@ROWCOUNT)+' Rows'

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










"Juan Diego Bueno" wrote:

Hola Pedro:

Me parece que tienes que instalar los MDAC. Busca en la web de microsoft

Saludos

"Pedro" escribió en el mensaje
news:
> Exportacion a XLS mediante ADODB.Connection,
>
> Solucione el anterior problema con:
> sp_configure 'Ole Automation Procedures',1
> Reconfigure
>
> Ahora tengo el error:
> Error 0x800A0E7A
> Source: ADODB.Connection
> Description: Provider cannot be found. It may not be proerly installed
>
> no entiendo muy bien la descripcion de error, tengo que instalar algo??
>
>
> "Pedro" wrote:
>
>> Estoy exportado a excel y me encuentro con unos errores saben por que
>> puede
>> ser?
>> Os dajunto los erreres con un pequeño detalle:
>>
>> Created OLE ADODB.Connection object
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OACreate, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OACreate' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can
>> enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface
>> Area
>> Configuration" in SQL Server Books Online.
>> Assigned ConnectionString property
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OASetProperty, Línea
>> 1
>> SQL Server blocked access to procedure 'sys.sp_OASetProperty' of
>> component
>> 'Ole Automation Procedures' because this component is turned off as
>> part
>> of
>> the security configuration for this server. A system administrator can
>> enable
>> the use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface
>> Area
>> Configuration" in SQL Server Books Online.
>> Open Connection to XLS, for file Create or Append
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can
>> enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface
>> Area
>> Configuration" in SQL Server Books Online.
>> Execute DDL to create 'EXCELSQL' worksheet
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can
>> enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface
>> Area
>> Configuration" in SQL Server Books Online.
>> Destroyed OLE ADODB.Connection object
>> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OADestroy, Línea 1
>> SQL Server blocked access to procedure 'sys.sp_OADestroy' of component
>> 'Ole
>> Automation Procedures' because this component is turned off as part of
>> the
>> security configuration for this server. A system administrator can
>> enable
>> the
>> use of 'Ole Automation Procedures' by using sp_configure. For more
>> information about enabling 'Ole Automation Procedures', see "Surface
>> Area
>> Configuration" in SQL Server Books Online.
>> Created Linked Server 'EXCEL_TEST' and Login
>> Mens. 121, Nivel 15, Estado 1, Línea 1
>> The select list for the INSERT statement contains more items than the
>> insert
>> list. The number of SELECT values must match the number of INSERT
>> columns.
>> Populated 'EXCELSQL' table with 1 Rows
>> Deleted Linked Server 'EXCEL_TEST' and Login

Creo que tenias razon
no tengo en el servidor Provider=Microsoft.Jet.OLEDB.4.0

estoy tratando de instalarlo

Gracias

"Juan Diego Bueno" wrote:

Bueno, yo te he dicho lo de instalarte MDAC por el error que te da, que
parece bastante claro. Otra cosa es que después a mayores te falle por otra
cosa, pero básicamente lo que te decía antes es que no encuentra el
proveedor ADO y eso teóricamente lo solucionas con la instalación del MDAC
(búscalo en la web de microsoft)

Saludos
"Pedro" escribió en el mensaje
news:
> Juan Diego ,En todo el proceso del scrip donde me falla es aqui:
> Por si te puede dar alguna idea:
>
> EXEC @hr = sp_OAMethod @Conn, 'Open'
>
> osea ejecutaria esto, me he sacado los valores de las variables:
> sp_OAMethod 16711422, 'Open'
>
> Voya apsar el proceso entero por si tienes alguna idea:
>
> 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
>
> SELECT @Recs = 0
> detail
> , @Log = 1
> Read/Write
> access
> SET @Path = 'C:\TEMP\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
> SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source='+@Path+';Extended Properties=Excel 8.0'
> SET @ServerName = 'EXCEL_TEST'
> SET @WKS_Name = 'People' --nombre de la tabla en xls en munu
> insert/name/define
> SET @DDL = 'CREATE TABLE '+@WKS_Name+' (SSN Text, Name Text, Phone Text)'
> OLE DB
> supported
> SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (SSN, Name, Phone)
> '
> SET @SQL = @SQL+'SELECT au_id AS SSN' -- se asigna el campo au_id d sql al
> campo SSN dl xls
> SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+''
> ''+ISNULL(au_lname,''''))) AS Name'
> xls
> SET @SQL = @SQL+', phone AS Phone ' -- se asigna el campo phone d sql al
> campo Phone dl xls
> SET @SQL = @SQL+'FROM pubs.dbo.authors' --tabla de SQL de la que se sacan
> los datos
>
> IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
> EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
> IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error
> numbers
> BEGIN
> 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'
> EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
> IF @hr <> 0
> BEGIN
> 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'
> parameters
> EXEC @hr = sp_OAMethod @Conn, 'Open'
> IF @hr <> 0
> BEGIN
> 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)+'Execute DDL to create '''+@WKS_Name+'''
> worksheet'
> caption,
> General
> format
> as the
> EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 --
> adCmdText
> + adExecuteNoRecords
> IF @hr = 0x80040E14
> OR @hr = 0x80042732
> BEGIN
> 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
> 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'
> EXEC @hr = sp_OADestroy @Conn
> IF @hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
> SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
> RETURN
> END
>
> 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'
> linked server
> EXEC sp_addlinkedsrvlogin @ServerName, 'false'
> END
>
> EXEC (@SQL)
> PRINT char(9)+'Populated '''+@WKS_Name+''' table with
> '+CONVERT(varchar,@@ROWCOUNT)+' Rows'
>
> 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
>
>
>
>
>
>
>
>
>
>
> "Juan Diego Bueno" wrote:
>
>> Hola Pedro:
>>
>> Me parece que tienes que instalar los MDAC. Busca en la web de microsoft
>>
>> Saludos
>>
>> "Pedro" escribió en el mensaje
>> news:
>> > Exportacion a XLS mediante ADODB.Connection,
>> >
>> > Solucione el anterior problema con:
>> > sp_configure 'Ole Automation Procedures',1
>> > Reconfigure
>> >
>> > Ahora tengo el error:
>> > Error 0x800A0E7A
>> > Source: ADODB.Connection
>> > Description: Provider cannot be found. It may not be proerly installed
>> >
>> > no entiendo muy bien la descripcion de error, tengo que instalar algo??
>> >
>> >
>> > "Pedro" wrote:
>> >
>> >> Estoy exportado a excel y me encuentro con unos errores saben por que
>> >> puede
>> >> ser?
>> >> Os dajunto los erreres con un pequeño detalle:
>> >>
>> >> Created OLE ADODB.Connection object
>> >> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OACreate, Línea 1
>> >> SQL Server blocked access to procedure 'sys.sp_OACreate' of component
>> >> 'Ole
>> >> Automation Procedures' because this component is turned off as part of
>> >> the
>> >> security configuration for this server. A system administrator can
>> >> enable
>> >> the
>> >> use of 'Ole Automation Procedures' by using sp_configure. For more
>> >> information about enabling 'Ole Automation Procedures', see "Surface
>> >> Area
>> >> Configuration" in SQL Server Books Online.
>> >> Assigned ConnectionString property
>> >> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OASetProperty, Línea
>> >> 1
>> >> SQL Server blocked access to procedure 'sys.sp_OASetProperty' of
>> >> component
>> >> 'Ole Automation Procedures' because this component is turned off as
>> >> part
>> >> of
>> >> the security configuration for this server. A system administrator can
>> >> enable
>> >> the use of 'Ole Automation Procedures' by using sp_configure. For more
>> >> information about enabling 'Ole Automation Procedures', see "Surface
>> >> Area
>> >> Configuration" in SQL Server Books Online.
>> >> Open Connection to XLS, for file Create or Append
>> >> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
>> >> SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
>> >> 'Ole
>> >> Automation Procedures' because this component is turned off as part of
>> >> the
>> >> security configuration for this server. A system administrator can
>> >> enable
>> >> the
>> >> use of 'Ole Automation Procedures' by using sp_configure. For more
>> >> information about enabling 'Ole Automation Procedures', see "Surface
>> >> Area
>> >> Configuration" in SQL Server Books Online.
>> >> Execute DDL to create 'EXCELSQL' worksheet
>> >> Mens 15281, Nivel 16, Estado 1, Procedimiento sp_OAMethod, Línea 1
>> >> SQL Server blocked access to procedure 'sys.sp_OAMethod' of component
>> >> 'Ole
>> >> Automation Procedures' because this component is turned off as part of
>> >> the
>> >> security configuration for this server. A system administrator can
>> >> enable
>> >> the
>> >> use of 'Ole Automation Procedures' by using sp_configure. For more
>> >> information about enabling 'Ole Automation Procedures', see "Surface
>> >> Area
>> >> Configuration" in SQL Server Books Online.
>> >> Destroyed OLE ADODB.Connection object