OLAP AS Connection Weirdness

I have a stand alone win2k3 report server(i.e. SQL is not on the box).
RS SP1 is installed
I have a SQLRS report developed in visual studio (also loaded on RS
box) that runs MDX against an analysis services cube. I am using the
OLE DB provider for OLAP services 8.0. (Note that we had to run
ptsfull.exe to get the pivot table service and 8.0 provider to be
available)
I can successfully preview the report in visual studio.
When I upload the report to Report Manger, the report bombs with...
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Cannot create a connection to data source 'AAHP'.
(rsErrorOpeningConnection) Get Online Help
Database '<source name>' does not exist.
Am I missing something on the report server?
Thanks!Jim wrote:
> When I upload the report to Report Manger, the report bombs with...
> An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> Cannot create a connection to data source 'AAHP'.
> (rsErrorOpeningConnection) Get Online Help
> Database '<source name>' does not exist.
> Am I missing something on the report server?
The report bombs while uploading?
Do you tried to open the AS-DB in the analysis-manager on the reportserver?
regards
Frank|||Sorry, no. The report bombs when I try to run it via report manager. I
can open the AS-DB and cube in Analysis Manger (which is Not on the
report server). I have a role setup that should allow the stored
credentials to successfully authenticate.|||Jim wrote:
> Sorry, no. The report bombs when I try to run it via report manager. I
> can open the AS-DB and cube in Analysis Manger (which is Not on the
> report server). I have a role setup that should allow the stored
> credentials to successfully authenticate.
Jim,
lets have a look into the eventlog of the server where the analysis cube
resides.
Source MSSQLSERVEROLAPSERVER.
If there errors with something like "NT-Authority/Anonymous" your
security-settings on the report-server are not valid (especially the
settings in internet service manager)
If there aren't any errors it become more difficult...Do you run ptsfull on
reportserver either?
regards
Frank|||Thanks for your replies.
No errors unfortunately.
I ran ptsfull on the reportserver.
As I mentioned I can connect to the AS server via Visual Studio. As
soon as I deploy the report (using a stored connection or shared
connection) to report manager it won't run. I'm running MDAC 2.8 on the
report server.|||Jim wrote:
> As I mentioned I can connect to the AS server via Visual Studio.
Yes..i know. You can connect to the AS from YOUR workstation and your
reportserver can't as you wrote.
The problem should be between reportserver and AS-server but wich problem? I
dont know!
regards
frank|||Just to clarify...
Visual Studio - where I am developing the reports - is on the report
server.|||Jim,
I am having same problems and was wondering if you found solution of this
one. Thanks.
"Jim" wrote:
> Just to clarify...
> Visual Studio - where I am developing the reports - is on the report
> server.
>

ODBC TCP/IP Dynamically Determine Port check box

Hopefully a simple answer:

*editted for clarity*

Where does the "Dynamically determine port" check box come from when creating a ODBC connection using TCP/IP in the client configuration? Only shows up in the control panel/adminstrative tools/Data Sources (ODBC) when it is installed. I have 3 computers here that have it (they are for development) but I am finding my users desktops do not have the box and so I am getting errors trying to connect from their desktops.

I am using Excel and Microsoft Access 2000 to connect to the server using the ODBC connection.

I need my users to connect to SQL Server 2005 so I need the "Dynamically determine port" box checked to find the port. Tried typing in the port but that isn't solving the problem or answering my question. So far I have tried MDAC and .NET 2.0 platform installs with no luck.

Thanks for the help,

WHich driver are you using in those cases, the SQL Server driver or the SQL native CLient driver ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||SQL Server|||"Dynamically determine port" is used with SQL Server driver to locate the port for a named instance. So if you are connecting to a named instance of SQL Server the driver will attempt to dynamically find the port. The port is served up by the SQL Browser service so you need to insure the SQL Browser service is running on the target machine.|||

Matt Neerincx wrote:

"Dynamically determine port" is used with SQL Server driver to locate the port for a named instance. So if you are connecting to a named instance of SQL Server the driver will attempt to dynamically find the port. The port is served up by the SQL Browser service so you need to insure the SQL Browser service is running on the target machine.

Please understand I'm not a DBA and I have to wear severals hats at my company. Everything I'm reading says SQL Browser service runs on the server, not the client. I can get connect to the server fine on all my desktops, even if I set the port manually without checking "Dynamically determine port".

Problem is, I cannot get this to work on other desktops. The "dynamically determine port "checkbox is not present and typing in the port doesn't work either. I think there is simply some driver or install I need to do but I can't seem to figure out which one it is.

|||

What is the version of Windows that does not have the "dynamically determine port" checkbox? Is it possibly Windows Server 2000 (or older)?

From the discussion above I assume that your server is installed as a named instance (i.e. something like <serverName>\<instanceName> instead of just <serverName>. The SQL Server ODBC driver on Windows Server 2000 does not support connections to named instances, so this could be one reson you don't see the checkbox.

If the desktops that do not have the "dynamically determine port" checkbox are Windows Server 2000 (or older) you can install on them the latest version of Microsoft Data Access Components (MDAC), which contains ODBC SQL Server driver supporting named instances.

You can download MDAC from http://msdn.microsoft.com/data/ref/mdac/downloads/. The latest version is MDAC 2.8 SP1 (http://www.microsoft.com/downloads/details.aspx?familyid=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&displaylang=en).

|||

Peter you are the man!

I had loaded MDAC but it was version 2.5. Apparently that wasn't good enough. As soon as I loaded 2.8 sp 1 the check box appeared and more importantly I tested on a desktop that couldn't connect and voila, it worked!

Thanks a heap!

P.S. Did a little more research and this article seems to explain it a little more that MDAC 2.6 and above is the answer...I was soooo close with 2.5 :

http://support.microsoft.com/kb/265808/en-us

ODBC TCP/IP Dynamic Determine Port check box

Hopefully a simple answer:

*editted for clarity*

Where does the "Dynamically determine port" check box come from when creating a ODBC connection using TCP/IP in the client configuration? Only shows up in the control panel/adminstrative tools/Data Sources (ODBC) when it is installed. I have 3 computers here that have it (they are for development) but I am finding my users desktops do not have the box and so I am getting errors trying to connect from their desktops.

I am using Excel and Microsoft Access 2000 to connect to the server using the ODBC connection.

I need my users to connect to SQL Server 2005 so I need the "Dynamically determine port" box checked to find the port. Tried typing in the port but that isn't solving the problem or answering my question. So far I have tried MDAC and .NET 2.0 platform installs with no luck.

Thanks for the help,

WHich driver are you using in those cases, the SQL Server driver or the SQL native CLient driver ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||SQL Server|||"Dynamically determine port" is used with SQL Server driver to locate the port for a named instance. So if you are connecting to a named instance of SQL Server the driver will attempt to dynamically find the port. The port is served up by the SQL Browser service so you need to insure the SQL Browser service is running on the target machine.|||

Matt Neerincx wrote:

"Dynamically determine port" is used with SQL Server driver to locate the port for a named instance. So if you are connecting to a named instance of SQL Server the driver will attempt to dynamically find the port. The port is served up by the SQL Browser service so you need to insure the SQL Browser service is running on the target machine.

Please understand I'm not a DBA and I have to wear severals hats at my company. Everything I'm reading says SQL Browser service runs on the server, not the client. I can get connect to the server fine on all my desktops, even if I set the port manually without checking "Dynamically determine port".

Problem is, I cannot get this to work on other desktops. The "dynamically determine port "checkbox is not present and typing in the port doesn't work either. I think there is simply some driver or install I need to do but I can't seem to figure out which one it is.

|||

What is the version of Windows that does not have the "dynamically determine port" checkbox? Is it possibly Windows Server 2000 (or older)?

From the discussion above I assume that your server is installed as a named instance (i.e. something like <serverName>\<instanceName> instead of just <serverName>. The SQL Server ODBC driver on Windows Server 2000 does not support connections to named instances, so this could be one reson you don't see the checkbox.

If the desktops that do not have the "dynamically determine port" checkbox are Windows Server 2000 (or older) you can install on them the latest version of Microsoft Data Access Components (MDAC), which contains ODBC SQL Server driver supporting named instances.

You can download MDAC from http://msdn.microsoft.com/data/ref/mdac/downloads/. The latest version is MDAC 2.8 SP1 (http://www.microsoft.com/downloads/details.aspx?familyid=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&displaylang=en).

|||

Peter you are the man!

I had loaded MDAC but it was version 2.5. Apparently that wasn't good enough. As soon as I loaded 2.8 sp 1 the check box appeared and more importantly I tested on a desktop that couldn't connect and voila, it worked!

Thanks a heap!

P.S. Did a little more research and this article seems to explain it a little more that MDAC 2.6 and above is the answer...I was soooo close with 2.5 :

http://support.microsoft.com/kb/265808/en-us