OpenDataSource to Excel

OpenDataSource does not work:
SELECT * FROM
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\temp\TEST.XLS;Extended properties=Excel 5.0')...[Tabelle1$]
The error message is:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
On our newest installation with Windows2003Server SP2 running with
SQLServer2005 SP2 the select-statement fails.
(On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
SQLServer-Service is running as a local-user that is an administrator
The user calling the select-statement is the same local-user
The Excel-File is on a local driveGeorg
Yes, I have seen this error many times and could not be able to solve the
problem. It worked just fine on SS2000 and does not on SS2005.
What if you create SSIS package to move the data?
"Georg" <Georg@.discussions.microsoft.com> wrote in message
news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
> OpenDataSource does not work:
> SELECT * FROM
> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
> 5.0')...[Tabelle1$]
> The error message is:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> On our newest installation with Windows2003Server SP2 running with
> SQLServer2005 SP2 the select-statement fails.
> (On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
> SQLServer-Service is running as a local-user that is an administrator
> The user calling the select-statement is the same local-user
> The Excel-File is on a local drive
>
>|||Thank you for quick reply !
I'm afraid SSIS package is no solution for me...
"Uri Dimant" wrote:
> Georg
> Yes, I have seen this error many times and could not be able to solve the
> problem. It worked just fine on SS2000 and does not on SS2005.
> What if you create SSIS package to move the data?
>
> "Georg" <Georg@.discussions.microsoft.com> wrote in message
> news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
> > OpenDataSource does not work:
> >
> > SELECT * FROM
> > OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> > 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
> > 5.0')...[Tabelle1$]
> >
> > The error message is:
> > OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> > returned message "Unspecified error".
> > Msg 7303, Level 16, State 1, Line 1
> > Cannot initialize the data source object of OLE DB provider
> > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> >
> > On our newest installation with Windows2003Server SP2 running with
> > SQLServer2005 SP2 the select-statement fails.
> > (On WindowsXP SP2 with SQLServer2005 SP2 its still working fine)
> >
> > SQLServer-Service is running as a local-user that is an administrator
> > The user calling the select-statement is the same local-user
> > The Excel-File is on a local drive
> >
> >
> >
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:OT1SVhsnIHA.5944@.TK2MSFTNGP03.phx.gbl:
> Georg
> Yes, I have seen this error many times and could not be able to solve
> the problem. It worked just fine on SS2000 and does not on SS2005.
> What if you create SSIS package to move the data?
>
> "Georg" <Georg@.discussions.microsoft.com> wrote in message
> news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
>> OpenDataSource does not work:
>> SELECT * FROM
>> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
>> 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
>> 5.0')...[Tabelle1$]
FWIW, the analogous
SELECT * FROM
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Documents and Settings\Chris\My Documents\RSJ.xls;Extended
properties=Excel 5.0')...[Sheet1$]
works fine for me with SQL Server 2005 Express.|||We could not reproduce the problem on a Test-Server with W2003Server/SP2 +
SS2005/SP2 - here it works fine also. Currently we try to find further
difference(s) between Test- and Production-Server
"Chris.Cheney" wrote:
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:OT1SVhsnIHA.5944@.TK2MSFTNGP03.phx.gbl:
> > Georg
> > Yes, I have seen this error many times and could not be able to solve
> > the problem. It worked just fine on SS2000 and does not on SS2005.
> > What if you create SSIS package to move the data?
> >
> >
> > "Georg" <Georg@.discussions.microsoft.com> wrote in message
> > news:63193B94-1355-44C4-A38E-A645A3F343E1@.microsoft.com...
> >> OpenDataSource does not work:
> >>
> >> SELECT * FROM
> >> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> >> 'Data Source=c:\temp\TEST.XLS;Extended properties=Excel
> >> 5.0')...[Tabelle1$]
> FWIW, the analogous
> SELECT * FROM
> OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\Documents and Settings\Chris\My Documents\RSJ.xls;Extended
> properties=Excel 5.0')...[Sheet1$]
> works fine for me with SQL Server 2005 Express.
>