Cannot Query Excel Linked Server

Hello,

I have attempted to set up a linked server to an Excel 2003 workbook, and I get an OLEDB error when I attempt to query against it. Some notes about the workbook;

-It has one worksheet in it named 'Add Revenue Accts'.
-The name of the workbook is 'Revenue_to_All_Accounts.xls'
-Its location is \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts

I have the linked server configured as follows;

-Linked Server; REVENUE_TO_ALL_ACCOUNTS
-Provider; Microsoft Jet 4.0 OLE DB Provider
-Data Source; \\cdnbwfin1\data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls
-Provider String; Microsoft.Jet.OLEDB.4.0;Data Source=\\Cdnbwfin1\Data\CDunn\Comdata\Reports\Reba_Holmes\Revenue_All_Accounts\Revenue_to_All_Accounts.xls;Persist Security Info=False

When I attempt the following query;
SELECT * FROM OPENQUERY(REVENUE_TO_ALL_ACCOUNTS, 'SELECT * FROM [Add Revenue Accts$]')

The following message appears, and no results are returned;

[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

I have Googled this error, but I have not found anything that really points to what the problem might be. What could be the problem?

Thank you for your help!

cdun2

Hi,

how are you connecting to the SQL Server while using this query ? If you are connecting to the server using a SQL Server login, the process will try to impersonate the SQL Server account (that one that runs the service of SQL Server) to acces the network file. If the Service account is not priviledged to access the file nor the network share, the process will fail. Could you describe your used enviuronment for a better understanding if the above description is your problem ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Cannot open offline cube

Hi,

I created an offline cube file (.cub) using SSAS 2005 sp1. I tried to open the cube file by following these steps:

1. Open excel 2003. On the toolbar, click on Data and select PivotTable and PivotChart report.

2. Select External data source as the data you want to analyze then click on next.

3. Click on the Get Data button on the next step.

4. Choose the OLAP Cubes tab on the pop-up window, select <New Data Source> and Click OK.

5. Type "Sales" as the name of the data source.

6.Choose Microsoft OLE DB Provider for Analysis Services 9.0 as the OLAP provider. Click on Connect button

7. Select the cube file option and browse for the location of the cube file. Click Finish.

8. On the fourth step (here comes the problem),on the Select the Cube that contains the data you want list, nothing is available in the dropdown list. And since I cannot finish this step, the OK button remains disabled and I was not able to open the cube file.

Can any one help me on this?

Thanks in advance,

Destry

I have exactly the same problem, as a matter of fact I was about to ask that question.

Should you find a workaround on your own, I would appreciate it if you could post it here as well too. Tia.

|||

There are several things can be going wrong. Here are several ideas for you to try.

1. Try and use Microsoft OLE DB Provider for Analysis Services 8.0 to connect to your local cube.

2. Try and use MDXSample application. In the connection dialog, as server name specify a path to your local cube file. Something like c:\my cubes\cube1.cub. If you can connect and but cant see cube name there, most likely something wrong with your local cube file.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cannot Get CSV or XML to Render

Hello,

I've just installed SQL Server 2005 Express, and RS (all with SP2). I can run reports fine for HTML, PDF, and Excel, but whenever I specify a format with "CSV" or "XML", I get the following error:

An attempt has been made to use a rendering extension that is not registered for this report serverAn attempt has been made to use a rendering extension that is not registered for this report server

I'd really like these report formats to work. I haven't modified any system configuration files.

Has anyone else seen this error?

Thanks,

Michael

Sorry, Michael.

CSV, XML, and TIFF don't work in Express.

From TechNet: http://technet.microsoft.com/en-us/library/ms365166(SQL.90).aspx

You'll have to upgrade to another version to use those extension.

-Jessica

|||

Jessica,

What's strange is that I ran XML and CSV reports right after getting the report server installed (with SQL Express), and then they just stopped working the next day when I shut down and then re-started my machine. VERY strange indeed. That lends some argument that it is possible. There were a few points in that link you sent me (thanks for the link, BTW), that were wrong. For example, I can reference my local report server with http://localhost/Reports, whereas the page says that I need to reference my report server with http://localhost/Reports$sqlexpress. That was not correct, or is not correct with SP2. So I am wondering if they changed the allowed formats too. Thanks for the info.

Michael

Cannot get "LIKE" to Work in My Query

Excel 2003. SQL Server 2000. I have an Excel module that grabs some data
from an SQL database using an ADODB connection. If I use the following in
the SQL query, it returns no data:
(WHERE INVOICENUMBER LIKE 'MS*')
However, if I use the following, I get all the data I expect:
(WHERE INVOICENUMBER>='MS' AND INVOICENUMBER<='MS9999999999999')
What am I doing wrong? How may I properly use the LIKE phrase? Thanks for
any help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.orgUse % instead of *.
"Chaplain Doug" <ChaplainDoug@.discussions.microsoft.com> wrote in message
news:2433FFE0-85C8-419F-9EB8-2C3302E5058B@.microsoft.com...
> Excel 2003. SQL Server 2000. I have an Excel module that grabs some data
> from an SQL database using an ADODB connection. If I use the following in
> the SQL query, it returns no data:
> (WHERE INVOICENUMBER LIKE 'MS*')
> However, if I use the following, I get all the data I expect:
> (WHERE INVOICENUMBER>='MS' AND INVOICENUMBER<='MS9999999999999')
> What am I doing wrong? How may I properly use the LIKE phrase? Thanks
for
> any help.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org|||in SQL, the wildcard character is % not *. Single character wildcard is _
instead of ?
"Chaplain Doug" <ChaplainDoug@.discussions.microsoft.com> wrote in message
news:2433FFE0-85C8-419F-9EB8-2C3302E5058B@.microsoft.com...
> Excel 2003. SQL Server 2000. I have an Excel module that grabs some data
> from an SQL database using an ADODB connection. If I use the following in
> the SQL query, it returns no data:
> (WHERE INVOICENUMBER LIKE 'MS*')
> However, if I use the following, I get all the data I expect:
> (WHERE INVOICENUMBER>='MS' AND INVOICENUMBER<='MS9999999999999')
> What am I doing wrong? How may I properly use the LIKE phrase? Thanks
> for
> any help.
> --
> Dr. Doug Pruiett
> Good News Jail & Prison Ministry
> www.goodnewsjail.org