Can not access Excel file using OpenRowset

I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the 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)".

Any help is appreciated.

Thanks

--

Farhan

Can you post the text of your query?|||

Here you go ...

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\inventory.xls;Extended Properties=Excel 8.0')...[Laptop$]

|||Can we clarify your original statement?
You wrote that when you execute the statement from the server itself it works.
In that case how do you exactly execute the query? And how do you do it from your workstation?|||

Hello Anton,

I ran the query using SQLServer Management Studio both on Server and Workstation.

Thanks.

|||Connecting to the same server with the same credentials?|||

I think this is a point of view issue. Remember that when you perform OpenRowset, it is looking for that location from the server's point of view. If you create a file on the client at C:\file and try to link to it from the server, it will naturally not find C:\file. You have to specify where the server would find that file, not where it is on the client. This is the same kind of issue that would come up if you try to load a CLR assembly on the server that is built on the client. The file is not within the scope of the server's view and definitely not in the same place so it fails to find it and fails to load, but if you perform the exact same operation on the server, it succeeds. This is because, when performing this operation on the server, the client and server are the same machine and have the same filesystem view. You can work around this issue in a few ways if you need to dynamically link the server to a client file. I suggest either: 1.) upload the file through TCP or an extended stored procedure and link to it from the directory path in the server's view, 2.) create a share and place files on the share that you want to push over to the server, specifying the share should give you a uniform location across client and server and effectively abstract out this detail.

Hope that helps,

John

sql

Can not access Excel file using OpenRowset

I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the 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)".

Any help is appreciated.

Thanks

--

Farhan

Can you post the text of your query?|||

Here you go ...

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\inventory.xls;Extended Properties=Excel 8.0')...[Laptop$]

|||Can we clarify your original statement?
You wrote that when you execute the statement from the server itself it works.
In that case how do you exactly execute the query? And how do you do it from your workstation?|||

Hello Anton,

I ran the query using SQLServer Management Studio both on Server and Workstation.

Thanks.

|||Connecting to the same server with the same credentials?|||

I think this is a point of view issue. Remember that when you perform OpenRowset, it is looking for that location from the server's point of view. If you create a file on the client at C:\file and try to link to it from the server, it will naturally not find C:\file. You have to specify where the server would find that file, not where it is on the client. This is the same kind of issue that would come up if you try to load a CLR assembly on the server that is built on the client. The file is not within the scope of the server's view and definitely not in the same place so it fails to find it and fails to load, but if you perform the exact same operation on the server, it succeeds. This is because, when performing this operation on the server, the client and server are the same machine and have the same filesystem view. You can work around this issue in a few ways if you need to dynamically link the server to a client file. I suggest either: 1.) upload the file through TCP or an extended stored procedure and link to it from the directory path in the server's view, 2.) create a share and place files on the share that you want to push over to the server, specifying the share should give you a uniform location across client and server and effectively abstract out this detail.

Hope that helps,

John

Can not access Excel file using OpenRowset

I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the 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)".

Any help is appreciated.

Thanks

--

Farhan

Can you post the text of your query?|||

Here you go ...

Select * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\inventory.xls;Extended Properties=Excel 8.0')...[Laptop$]

|||Can we clarify your original statement?
You wrote that when you execute the statement from the server itself it works.
In that case how do you exactly execute the query? And how do you do it from your workstation?|||

Hello Anton,

I ran the query using SQLServer Management Studio both on Server and Workstation.

Thanks.

|||Connecting to the same server with the same credentials?|||

I think this is a point of view issue. Remember that when you perform OpenRowset, it is looking for that location from the server's point of view. If you create a file on the client at C:\file and try to link to it from the server, it will naturally not find C:\file. You have to specify where the server would find that file, not where it is on the client. This is the same kind of issue that would come up if you try to load a CLR assembly on the server that is built on the client. The file is not within the scope of the server's view and definitely not in the same place so it fails to find it and fails to load, but if you perform the exact same operation on the server, it succeeds. This is because, when performing this operation on the server, the client and server are the same machine and have the same filesystem view. You can work around this issue in a few ways if you need to dynamically link the server to a client file. I suggest either: 1.) upload the file through TCP or an extended stored procedure and link to it from the directory path in the server's view, 2.) create a share and place files on the share that you want to push over to the server, specifying the share should give you a uniform location across client and server and effectively abstract out this detail.

Hope that helps,

John

Can I just make the necessary registry changes to turn on error reporting in SQL2000 SP3

http://support.microsoft.com/default...b;en-us;328446
The part that describes how to turn off Error Reporting for a default =
instance is unclear in this article. It looks to be missing stuff. So =
maybe all I have to do to enable Error Reporting, is to manually make =
the change in the registry. That way if I want to turn it off I just =
change 1 to 0 as is in the article. Is that OK will that be sufficient? =
Thanks.
--=20
George Hester
__________________________________
You are correct that the artilce is missing something. The proper method to
enable this is as follows:
To turn on error reporting by using SQL Server Enterprise
Manager or Analysis Manager, follow these steps:
1. Click to select the name of the server.
2. Right-click the server name, and then click Properties.
3. Click to select the Enable Error Reporting Feature check box.
It is always best to use the interface than to manually edit the registry.
Thanks for the catch. I am getting the article updated.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Oh cool yeah I'd rather do it that way. Thanks.
--=20
George Hester
__________________________________
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message =
news:#PLYRQnPEHA.1516@.cpmsftngxa10.phx.gbl...
> You are correct that the artilce is missing something. The proper =
method to=20
> enable this is as follows:
>=20
> To turn on error reporting by using SQL Server Enterprise
> Manager or Analysis Manager, follow these steps:
>=20
> 1. Click to select the name of the server.
> 2. Right-click the server name, and then click Properties.
> 3. Click to select the Enable Error Reporting Feature check box.
>=20
> It is always best to use the interface than to manually edit the =
registry.
>=20
> Thanks for the catch. I am getting the article updated.
>=20
> Rand
> This posting is provided "as is" with no warranties and confers no =
rights.
>

Can I just make the necessary registry changes to turn on error reporting in SQL2000 SP3

http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;328446
The part that describes how to turn off Error Reporting for a default = instance is unclear in this article. It looks to be missing stuff. So = maybe all I have to do to enable Error Reporting, is to manually make = the change in the registry. That way if I want to turn it off I just = change 1 to 0 as is in the article. Is that OK will that be sufficient? = Thanks.
-- George Hester
__________________________________You are correct that the artilce is missing something. The proper method to
enable this is as follows:
To turn on error reporting by using SQL Server Enterprise
Manager or Analysis Manager, follow these steps:
1. Click to select the name of the server.
2. Right-click the server name, and then click Properties.
3. Click to select the Enable Error Reporting Feature check box.
It is always best to use the interface than to manually edit the registry.
Thanks for the catch. I am getting the article updated.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Oh cool yeah I'd rather do it that way. Thanks.
-- George Hester
__________________________________
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message =news:#PLYRQnPEHA.1516@.cpmsftngxa10.phx.gbl...
> You are correct that the artilce is missing something. The proper =method to > enable this is as follows:
> > To turn on error reporting by using SQL Server Enterprise
> Manager or Analysis Manager, follow these steps:
> > 1. Click to select the name of the server.
> 2. Right-click the server name, and then click Properties.
> 3. Click to select the Enable Error Reporting Feature check box.
> > It is always best to use the interface than to manually edit the =registry.
> > Thanks for the catch. I am getting the article updated.
> > Rand
> This posting is provided "as is" with no warranties and confers no =rights.
>

Can I just make the necessary registry changes to turn on error reporting in SQL2000 S

http://support.microsoft.com/defaul...kb;en-us;328446
The part that describes how to turn off Error Reporting for a default =
instance is unclear in this article. It looks to be missing stuff. So =
maybe all I have to do to enable Error Reporting, is to manually make =
the change in the registry. That way if I want to turn it off I just =
change 1 to 0 as is in the article. Is that OK will that be sufficient? =
Thanks.
--=20
George Hester
__________________________________You are correct that the artilce is missing something. The proper method to
enable this is as follows:
To turn on error reporting by using SQL Server Enterprise
Manager or Analysis Manager, follow these steps:
1. Click to select the name of the server.
2. Right-click the server name, and then click Properties.
3. Click to select the Enable Error Reporting Feature check box.
It is always best to use the interface than to manually edit the registry.
Thanks for the catch. I am getting the article updated.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Oh cool yeah I'd rather do it that way. Thanks.
--=20
George Hester
__________________________________
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message =
news:#PLYRQnPEHA.1516@.cpmsftngxa10.phx.gbl...
> You are correct that the artilce is missing something. The proper =
method to=20
> enable this is as follows:
>=20
> To turn on error reporting by using SQL Server Enterprise
> Manager or Analysis Manager, follow these steps:
>=20
> 1. Click to select the name of the server.
> 2. Right-click the server name, and then click Properties.
> 3. Click to select the Enable Error Reporting Feature check box.
>=20
> It is always best to use the interface than to manually edit the =
registry.
>=20
> Thanks for the catch. I am getting the article updated.
>=20
> Rand
> This posting is provided "as is" with no warranties and confers no =
rights.
>