Accessing Linked Excel Server

I created a linked Excel server that is stored in a SQL2000 database.

I can run the following from the SQL server with no problem.

Select * From CSCNEDI...EDI$

When I try and run the select from my WinXP computer I get the following from both SQL2000 Query Analyzer or SQL2005 Management Studio (these are configured for client access)

[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, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

Thanks

David Davis

Schuette Inc.

Hi, David,

The error above simply indicates a failure of the Provider to open a "connection", in this case - the MDB file. Unfortunately, this is quite generic. How are you connecting to the SQL Server? Are you using SQL or NT Authentication? Is your Excel file local on the SQL box or is it on a file share? What we might be facing here seems to be an authentication problem. Here're a couple of ideas:

== If the Excel file is on a share, try to put it locally on the SQL box (reconfigure the linked server) and try the query from the workstation again

== If you are using NT authentication, try using SQL authentication to see if this changes the effect

== To confirm if this is an authentication/permission issue, use FileMon tool (http://www.microsoft.com/technet/sysinternals/FileAndDisk/Filemon.mspx) and capture the file activity when you get the failure (a good idea is to recycle SQL Server and capture the first attempt). Check the log for your excel file name and for error like "Access Denied".

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Accessing Excel functions in SQL SP

Hi,
I am trying to access an Excel function in a stored procedure. First of all,
is it possible, if it is, can someone give me an example.
Thank you.
--
RamIt may be possible with sp_OAMethod, but why do it to yourself? Even if you
can get to work without blowing up your computer, performance is going to be
rubbish!
SQL has loads of functions, and you can roll your own, do you know about
user-defined functions? They're great!
Anyway, tell us which function you want to emulate and we'll see what we can
do.
Plus, also bear in mind, SQL might have the function you need, but you just
don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there's
a
ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
some conditional logic, there's the CASE statement instead of Excel's IF.
Remember to post some DDL, sample data, expected results etc.
Damien
First of all, there are few things you can't do in SQL
"ram4tech" wrote:

> Hi,
> I am trying to access an Excel function in a stored procedure. First of al
l,
> is it possible, if it is, can someone give me an example.
> Thank you.
> --
> Ram|||Hi Damien:
The looked in BOL and on the net, but didn't had much luck. I wasn't aware
that there might be some performance issues. The excel function I am plannin
g
on using is IRR().
Thank you.
--
Ram
"Damien" wrote:
> It may be possible with sp_OAMethod, but why do it to yourself? Even if y
ou
> can get to work without blowing up your computer, performance is going to
be
> rubbish!
> SQL has loads of functions, and you can roll your own, do you know about
> user-defined functions? They're great!
> Anyway, tell us which function you want to emulate and we'll see what we c
an
> do.
> Plus, also bear in mind, SQL might have the function you need, but you jus
t
> don't know it's name; eg MID in Excel is called SUBSTRING in T-SQL, there'
s a
> ROUND function, CHARINDEX is the same as FIND in Excel, if you need to do
> some conditional logic, there's the CASE statement instead of Excel's IF.
> Remember to post some DDL, sample data, expected results etc.
> Damien
> First of all, there are few things you can't do in SQL
> "ram4tech" wrote:
>|||Hello Ram !
http://groups.google.de/group/micro...5d4e46703ec82cd
HTH, jens Suessmeyer.|||Right, well you'd need to know the formula that sits behind IRR to recreate
it, but I guess it boils down to addition, subtraction, maybe an average or
two? T-SQL can do all that, but Excel is better at sums, I'll give it that.
So maybe you should play to the strengths, ie SQL for holding data,
concurrent access, raw power, Excel for sums.
Have you considered linking in to your server, eg a pivot table or external
query?
"ram4tech" wrote:
> Hi Damien:
> The looked in BOL and on the net, but didn't had much luck. I wasn't aware
> that there might be some performance issues. The excel function I am plann
ing
> on using is IRR().
> Thank you.
> --
> Ram
>
> "Damien" wrote:
>|||Here is a T-SQL implementation of IRR I posted a while back.
It is probably less robust than the Excel version, but it may still
work for you:
http://groups.google.co.uk/groups?q...eam+kass+newton
Steve Kass
Drew University
ram4tech wrote:

>Hi Damien:
>The looked in BOL and on the net, but didn't had much luck. I wasn't aware
>that there might be some performance issues. The excel function I am planni
ng
>on using is IRR().
>Thank you.
>

Accessing Cube from Excel 2003

Analysis services Cube is created using SQL Server 2005 (CTP June). Server is in Prod domain.
There is no problems accessing and playing with Cube in the same box or within PROD domain.

While trying to access Cube from excel 2003 from a computer in other domain QUAL gives the following error

"an error was encountered in the transportation layer"

Any suggestions ?

I'm not exactly sure how IE Security settings interact with PTS, but your problem is making me think of the 'Access Data Sources across Domains' option in IE / Tools / Options / Security / Custom Level / Misc.

Is that option enabled?

Accessing Cube from Excel 2003

Analysis services Cube is created using SQL Server 2005 (CTP June). Server is in Prod domain.
There is no problems accessing and playing with Cube in the same box or within PROD domain.

While trying to access Cube from excel 2003 from a computer in other domain QUAL gives the following error

"an error was encountered in the transportation layer"

Any suggestions ?

I'm not exactly sure how IE Security settings interact with PTS, but your problem is making me think of the 'Access Data Sources across Domains' option in IE / Tools / Options / Security / Custom Level / Misc.

Is that option enabled?

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

|||Having the same issue here. No idea how to fix it at this point. Any ideas would be much appreciated. I am trying to access a cube in AS2005 from EXCEL 2003. Access through excel to the cube works fine from the server hosting the AS 2005 cube. It does not work when connecting to the cube from a remote machine.|||Error was fixed by adding the domain name to the username when logging into the Analysis Services server. <domain name>\<username>|||

In SQL2000 you could extract ptsfull.exe or ptslite.exe from the distribution disks and run these on the client to install OLAP 8.0 without installing the whole SQL Client tools suite. Pivot table services would then work in Excel against a SQL2000 Analysis Services database.

Try as I might I can't find these files on the SQL2005 distribution disks. Have they been renamed or is this approach no longer supported? Being able to install OLAP 9.0 on clients would be very useful.

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

You can also get to the same page by googling Analysis Services OLE DB 9.0 if the link fails. In addtion to installing this, I also had to install XML6.0 first. The installer for OLE DB 9.0 will let you know if this is needed. The XML 6.0 upgrade is also available on MS site though I was only able to locate through a Google search looking for "XML 6.0 download". I then pulled down the install called "msxml6.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

|||Having the same issue here. No idea how to fix it at this point. Any ideas would be much appreciated. I am trying to access a cube in AS2005 from EXCEL 2003. Access through excel to the cube works fine from the server hosting the AS 2005 cube. It does not work when connecting to the cube from a remote machine.|||Error was fixed by adding the domain name to the username when logging into the Analysis Services server. <domain name>\<username>|||

In SQL2000 you could extract ptsfull.exe or ptslite.exe from the distribution disks and run these on the client to install OLAP 8.0 without installing the whole SQL Client tools suite. Pivot table services would then work in Excel against a SQL2000 Analysis Services database.

Try as I might I can't find these files on the SQL2005 distribution disks. Have they been renamed or is this approach no longer supported? Being able to install OLAP 9.0 on clients would be very useful.

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

You can also get to the same page by googling Analysis Services OLE DB 9.0 if the link fails. In addtion to installing this, I also had to install XML6.0 first. The installer for OLE DB 9.0 will let you know if this is needed. The XML 6.0 upgrade is also available on MS site though I was only able to locate through a Google search looking for "XML 6.0 download". I then pulled down the install called "msxml6.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

|||Having the same issue here. No idea how to fix it at this point. Any ideas would be much appreciated. I am trying to access a cube in AS2005 from EXCEL 2003. Access through excel to the cube works fine from the server hosting the AS 2005 cube. It does not work when connecting to the cube from a remote machine.|||Error was fixed by adding the domain name to the username when logging into the Analysis Services server. <domain name>\<username>|||

In SQL2000 you could extract ptsfull.exe or ptslite.exe from the distribution disks and run these on the client to install OLAP 8.0 without installing the whole SQL Client tools suite. Pivot table services would then work in Excel against a SQL2000 Analysis Services database.

Try as I might I can't find these files on the SQL2005 distribution disks. Have they been renamed or is this approach no longer supported? Being able to install OLAP 9.0 on clients would be very useful.

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

You can also get to the same page by googling Analysis Services OLE DB 9.0 if the link fails. In addtion to installing this, I also had to install XML6.0 first. The installer for OLE DB 9.0 will let you know if this is needed. The XML 6.0 upgrade is also available on MS site though I was only able to locate through a Google search looking for "XML 6.0 download". I then pulled down the install called "msxml6.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

|||Having the same issue here. No idea how to fix it at this point. Any ideas would be much appreciated. I am trying to access a cube in AS2005 from EXCEL 2003. Access through excel to the cube works fine from the server hosting the AS 2005 cube. It does not work when connecting to the cube from a remote machine.|||Error was fixed by adding the domain name to the username when logging into the Analysis Services server. <domain name>\<username>|||

In SQL2000 you could extract ptsfull.exe or ptslite.exe from the distribution disks and run these on the client to install OLAP 8.0 without installing the whole SQL Client tools suite. Pivot table services would then work in Excel against a SQL2000 Analysis Services database.

Try as I might I can't find these files on the SQL2005 distribution disks. Have they been renamed or is this approach no longer supported? Being able to install OLAP 9.0 on clients would be very useful.

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

You can also get to the same page by googling Analysis Services OLE DB 9.0 if the link fails. In addtion to installing this, I also had to install XML6.0 first. The installer for OLE DB 9.0 will let you know if this is needed. The XML 6.0 upgrade is also available on MS site though I was only able to locate through a Google search looking for "XML 6.0 download". I then pulled down the install called "msxml6.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing April CTP OLAP cube from Excel 2003

I installed Office 2003 first on a Windows XP machine.

I then installed SQL 2005 April CTP.

When I try accessing cubes from Excel, I get the error
"An error was encountered in the transport layer"

This error occurs when I try to setup the connection to my OLAP cube.

Thanks for any help you can offer

TaylorDo you have msolap80.dll on your machine? Somehow the 80 dll may have made itself the default for olap access. Try to re-register the olap 9.0 dll at command prompt

regsvr32 "%program files%\common files\system\ole db\msolap90.dll"|||

Hi
I have the same problem and i did the solotion to registe the olap 9.0.dll and it don't resolve my problems. Do you know why.

Thank you for your help.

|||

Hi,

I've the same problem and this solution has changed anything.

Please help me!!!!!!!!

|||Having the same issue here. No idea how to fix it at this point. Any ideas would be much appreciated. I am trying to access a cube in AS2005 from EXCEL 2003. Access through excel to the cube works fine from the server hosting the AS 2005 cube. It does not work when connecting to the cube from a remote machine.|||Error was fixed by adding the domain name to the username when logging into the Analysis Services server. <domain name>\<username>|||

In SQL2000 you could extract ptsfull.exe or ptslite.exe from the distribution disks and run these on the client to install OLAP 8.0 without installing the whole SQL Client tools suite. Pivot table services would then work in Excel against a SQL2000 Analysis Services database.

Try as I might I can't find these files on the SQL2005 distribution disks. Have they been renamed or is this approach no longer supported? Being able to install OLAP 9.0 on clients would be very useful.

Has anyone discovered the secret to this yet?

Regards

Nick

|||

You can install OLE DB 9.0 via a download from MS. I think this is what you are asking.... I found it at the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

I downloaded the file called "SQLServer2005_ASOLEDB9.msi".

You can also get to the same page by googling Analysis Services OLE DB 9.0 if the link fails. In addtion to installing this, I also had to install XML6.0 first. The installer for OLE DB 9.0 will let you know if this is needed. The XML 6.0 upgrade is also available on MS site though I was only able to locate through a Google search looking for "XML 6.0 download". I then pulled down the install called "msxml6.msi".

Hope this helps!

|||

Spot on - thanks very much

Regards

Nick

|||

I'm still having trouble with this as I still get the error.

I have XML6.0 installed, OLE DB 9.0 installed but I still get the error when trying to connect to the OLAP server. Anymore ideas?

Thanks

|||Save your password creating odc file (mark checkbox "Save password in file"). Should help

Accessing Analysis Services 2005 from MS Excel using HTTP from Windows 2000 Server

Hye Friends,
I have installed and configured SQL Server 2005 Analysis Services and MS Office 2000 with the latest Service Packs on Windows 2000 Server. I am able to connect to my Analysis Services from MS Excel using the following URl in Excel ...
http://<ip address>/olap/msmdpump.dll ("olap" being my Virtual Directory)
I was wondering whether I would be able to connect to the same service without using the fully qualified path with only IP-Address... i.e... using only
http://<ip address>
I have been able to implement the above in Windows 2003 Server, but having problems doing the same in Windows 2000 Server. Can anyone help?
Thanks in Advance

How were you able to do so in Windows 2003? I've tried using IIS 6.0 to redirect, but without success.