Friday, March 23, 2012

OpenDataSource with Microsoft.Jet.OLEDB.4 Excel from stored proced

I have a stored procedure which has the following line:
select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
This stored procedure was called from a VB application. It's odd that
ONLY when ADO connection user need SA database role
AND \\MachineName\shared\account.xls was physical located in the same
machine of DATABASE
the stored procedure would work.
Otherwise it returned error like:
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not give any
information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. The provider did not give any information about the error. 0 7399
-2147217900 Microsoft OLE DB Provider for SQL Server 42000
Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
Any suggestion?Because the security reson, I don't want ADO connection user has the SA role,
and Excel file can located in any shared folder in the LAN.
"david" wrote:
> I have a stored procedure which has the following line:
> select * from FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="\\MachineName\shared\account.xls";...Excel 8.0...
> This stored procedure was called from a VB application. It's odd that
> ONLY when ADO connection user need SA database role
> AND \\MachineName\shared\account.xls was physical located in the same
> machine of DATABASE
> the stored procedure would work.
> Otherwise it returned error like:
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give any
> information about the error.]. 0 7300 -2147217900 Microsoft OLE DB Provider
> for SQL Server 01000OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error. The provider did not give any information about the error. 0 7399
> -2147217900 Microsoft OLE DB Provider for SQL Server 42000
> Database is SQL Server 2000 SP4. Registry DisallowAdhocAccess is 0.
> Any suggestion?
>

No comments:

Post a Comment