OLAP Client - your suggestions

Hello,

I am trying to Upgrade my AS 2000 to AS 2005 but end up hitting wall. Our clients is Excel 2002/2003 on Windows 2000 OS. I managed to convince group about advantage to moving AS 2005. At last I managed to convince advantage to moving Excel 2007 which added new project of upgrading office 2007 in selected workstations. Then I find out we can not install Excel 2007 in Windows 2000. Upgrading Clients workstations to XP is too much to ask for, so whole thing end up to ground zero. What you guys think what options I have. Is there any cheep out of box client/ or option of min. development which I can use in Windows 2000 workstations so that I can move on with AS 2005 migration.

I know I can still use excel 2002/2003 in client with AS 2005 but then all new features and measure groups are not coming ordered in Excel 2002/Excel2003.

Thanks for you advice,

-Ashok

This add-in can enhance ExcelXP and Excel2003 clients:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

It is not as good as Excel 2007 but still better than the basic functionality in these clients.

HTH

Thomas Ivarsson

ODBC Timeout problems but very hard to pin down

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.

Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).

APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)

All connect to the same database server - different databases, natch.

1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace

APP 1 - ACCESS APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. Open a Search form and the default recordset
is displayed correctly.
NOTE: This SQL captured on trace.

2. Enter a criterion into one of the controls on the search form and
press "Filter". Wait a minute and watch the hourglass. Eventually
ODBC Timeout error displayed.
NOTE: This SQL captured on trace.

3. Right-click on the appropriate column on the sub-form and enter
the same criterion as for step 2 above. NO ODBC TIMEOUT - CORRECT
RECORDSET RETURNED WITHIN TWO SECONDS
NOTE: This SQL captured on trace.

NOTE: If I open the (linked) tables in the Microsoft Access database
pane I can manipulate the data at will. However, if I try to open any
stored queries in Access they time out.

APP 2 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display for the
logged-on user contains no records.
2. Change the parameters to allow some records to be displayed.
Watch the progress bar do nothing and then the system displays the
"Object reference not set to an instance of an object" exception.
NOTE: This SQL captured on trace.

APP 3 - ASP.NET APPLICATION

1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display contains
no records.
2. Attempt to add a new record. System pretty slow.
3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.

If the above wasn't weird enough for you, get this.

If I cut and paste the SQL that's being captured by the Profiler at
each point and paste it into Query Analyser, it totally barfs. I
waited 11 minutes for one query but it just kept running, never
returning. EVEN THE QUERY AT APP 1 POINT 3.

Needless to say, if I run these queries back at the office it all goes
according to plan.

I think they've got a problem....

Edward(teddysnips@.hotmail.com) writes:

Quote:

Originally Posted by

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.

They always say that. :-)

Quote:

Originally Posted by

Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).
>
APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)
>
All connect to the same database server - different databases, natch.
>
1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace

As long as you only talk about the first two applications, the symptom
is not unknown. Keep in mind that the optimizer builds the query plans
based on estimates from statistics sampled about the data. There are
two different ways that this can go wrong:

1) The plans falls out of cache for some reason; someone runs a huge query,
or the server is restared. The queries are such that they ask for rows
added the last few days, but statistics have not been updated to reflect
this, so the optimizer thinks there are very rows when in fact there are
very many, and picks a bad plan.

2) Statistics are auto-updated, and the plans are recompiled in reaction
to this. The new data causes the optimizer to pick a new plan, which
unfortunately is not as the good as the old one.

The first of these two cases are easiest to address: make sure that
statistics for the updated tables are up to date. The second case may
require tweaking the query and possible adding an index hint.

But from what happens here:

Quote:

Originally Posted by

3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.

It appears that someone has loaded a lot of data. Of course, this can affect
both performance and results.

I get a little nervous when you say that you have each item four times in
the lookup tables. Doesn't these tables have a primary key?

It could be that they have a corruption and you could run DBCC CHECKDB to
find out. But I'm quite willing to bet that the corruption they have is
on application level. That is, someone has loaded data he shouldn't have
loaded.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ODBC? What year is it?

If you have Access and/or Sql Server, there's not really a good reason to
use ODBC.

...

Change to the other providers, and heck, it'll start working faster, and
you'll be a hero.

<teddysnips@.hotmail.comwrote in message
news:1187268689.901968.272820@.d55g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

One of our clients has reported a problem. Everything was working
fine on Monday, but since Tuesday all is going wrong. The sysadmin
assures me that there have been no changes to the network, or the
servers.
>
Three applications, one back-end database server (SQL Server 2000 with
all service packs etc.).
>
APP 1: Access 2000 database
APP 2: ASP.NET (VS 2003)
APP 3: ASP.NET (VS 2003)
>
All connect to the same database server - different databases, natch.
>
1 & 2 have reported ODBC timeout issues. 3 had other problems. This
is how they present themselves. I ran SQL Profiler to capture the
trace
>
APP 1 - ACCESS APPLICATION
>
1. Login works fine. You have to supply credentials which are
validated by SQL Server. Open a Search form and the default recordset
is displayed correctly.
NOTE: This SQL captured on trace.
>
2. Enter a criterion into one of the controls on the search form and
press "Filter". Wait a minute and watch the hourglass. Eventually
ODBC Timeout error displayed.
NOTE: This SQL captured on trace.
>
3. Right-click on the appropriate column on the sub-form and enter
the same criterion as for step 2 above. NO ODBC TIMEOUT - CORRECT
RECORDSET RETURNED WITHIN TWO SECONDS
NOTE: This SQL captured on trace.
>
NOTE: If I open the (linked) tables in the Microsoft Access database
pane I can manipulate the data at will. However, if I try to open any
stored queries in Access they time out.
>
APP 2 - ASP.NET APPLICATION
>
1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display for the
logged-on user contains no records.
2. Change the parameters to allow some records to be displayed.
Watch the progress bar do nothing and then the system displays the
"Object reference not set to an instance of an object" exception.
NOTE: This SQL captured on trace.
>
APP 3 - ASP.NET APPLICATION
>
1. Login works fine. You have to supply credentials which are
validated by SQL Server. As it happens, the default display contains
no records.
2. Attempt to add a new record. System pretty slow.
3. New record form displayed. All drop-downs contain incorrect data
- for example, in the Supply Priority list, there should be 14 items -
there are, in fact, 56 - each item duplicated four times. This has
happened in all the tables that I can see. I've taken a dump of the
live data and compared it with our archived version - for lookup
tables, there is no duplication in our copy, but in their copy every
item appears four times.
>
If the above wasn't weird enough for you, get this.
>
If I cut and paste the SQL that's being captured by the Profiler at
each point and paste it into Query Analyser, it totally barfs. I
waited 11 minutes for one query but it just kept running, never
returning. EVEN THE QUERY AT APP 1 POINT 3.
>
Needless to say, if I run these queries back at the office it all goes
according to plan.
>
I think they've got a problem....
>
Edward
>

ODBC Timeout expired error

Some of my clients cannot access an application, and it seems there is
a communication issue(?). The application server is a bit slow, the
database server (MS SQL Server 2000, SQL SP4) is brand new and fast.
Users that have the problem are behind a firewall that allows
everything from the client network to the server network. I have
configured the ODBC DSN, tested it, and the test succeeds. When
starting the application however, they get an error, [Microsoft][ODB
C
SQL Server Driver]Timeout expired. I have traced the startup, and the
log file is pasted below:
Vbus 100-938 ENTER SQLAllocEnv
HENV * 013FC478
Vbus 100-938 EXIT SQLAllocEnv with return code 0
(SQL_SUCCESS)
HENV * 0x013FC478 ( 0x02031540)
Vbus 100-938 ENTER SQLAllocConnect
HENV 02031540
HDBC * 01AC4ED0
Vbus 100-938 EXIT SQLAllocConnect with return code 0
(SQL_SUCCESS)
HENV 02031540
HDBC * 0x01AC4ED0 ( 0x020315e8)
Vbus 100-938 ENTER SQLDriverConnectW
HDBC 020315E8
HWND 001902C6
WCHAR * 0x74329A38 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x74329A38
SWORD 2
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
Vbus 100-938 EXIT SQLDriverConnectW with return code -1
(SQL_ERROR)
HDBC 020315E8
HWND 001902C6
WCHAR * 0x74329A38 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x74329A38
SWORD 2
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
DIAG [S1T00] [Microsoft][ODBC SQL Server Driver]Timeout expired
(0)
Vbus 100-938 ENTER SQLErrorW
HENV 02031540
HDBC 020315E8
HSTMT 00000000
WCHAR * 0x00208B48 (NYI)
SDWORD * 0x01AC4EDC
WCHAR * 0x00208748
SWORD 512
SWORD * 0x0020F08C
Vbus 100-938 EXIT SQLErrorW with return code 0
(SQL_SUCCESS)
HENV 02031540
HDBC 020315E8
HSTMT 00000000
WCHAR * 0x00208B48 (NYI)
SDWORD * 0x01AC4EDC (0)
WCHAR * 0x00208748 [ 50] "[Microsoft][ODBC SQL
Server Driver]Timeout expired"
SWORD 512
SWORD * 0x0020F08C (50)
It seems that the SQLDriverConnectW is what generates the error, but I
am not a database administrator, nor developer, and knows nothing about
what it is or means.
The ODBC-driver (odbc32.dll) version on the machines that get this
error, is identical to those that don't.
Can anyone interpret this trace for me? What goes on, what is the
ODBC-driver (or anything else) complaining about? Would be highly
appreciated!
Bjorn Henrik Formo.Any luck on this issue. I am running into the same error. The ODBC test
connects fine but from my application I cannot.
-Markus
markusr
---
markusr's Profile: http://www.dbtalk.net/m568
View this thread: http://www.dbtalk.net/t169537

ODBC System DSN Questions

We are running an SBS2000 network with around 40 clients being 60% Windows
2000 Pro and 40% WinXP Pro. We have around 3 member servers (not DC) and one
of the servers is a SQL server running SQL 2000.
A user on the domain has created a dBasev5.7 form that needs an ODBC System
DSN connection from the client to the SQL server in order for his form to be
populated with data. He wanted to roll out the DSNs to all the machines on
the network via Group Policy - I did a bit of digging around in the
Newsgroups and someone suggested this automated way by running a vbs script:
http://www.databasejournal.com/featu...e.php/2238221. Although
this works - we have a slight problem. We have no idea if this is to do with
dBase (it uses 16-bit architecture) but when we roll out the DSNs using the
script the registry gets updated (HKEYLOCAL
MACHINE>SOFTWARE>ODBC>ODBC.INI...) with all the right keys (as if you set it
up manually in Control Panel's ODBC) but we need to go through the System DSN
wizard (to the last page of the wizard) and click the "Test connection"
button in order for the dBase program to access the ODBC database connection.
It seems as if the ODBC System DSN is dead without clicking the "Test
Connection" button.
Questions:
1. Is it a requirement to hit the Test Network Connection button on the
last page of the wizard. I thought that this was only for troubleshooting?
2. It appears that this "Test Connection" button in the wizard posts a
registry key to allow the ODBC SYstem DSN (it has created) to be used. Where
abouts is this registry key?
3. Is there a Group Policy way of doing this? Chances are we may change
settings in the future and may want a server-side script running to update
any changes to clients automatically.
Please help.
THanks,
Skc
I am not usre why this is happening, but I can tell you that the Test
Connection button does not write a registry entry anywhere. It is just used
for testing the connection. Possibly teh script leaves something out that
is necessary and going through the wizard completes the process.
It is possible that an alias to the SQL Server is geing created when the
wizard is accessed. There is a cliconfg tuiltiy that you can use to verify
this.
Run the script on a client machine and then run cliconfg from Start - Run.
Check the alias tab to see if there is an alias to this SQL Server. If not,
then run the Wizard and go through the steps. After it completes go back to
cliconfg and see if there is an alias. In many case the cliconfg utility
will create an alias or a SQL Server when a DSN has been created.
Rand
This posting is provided "as is" with no warranties and confers no rights.

ODBC System DSN Questions

We are running an SBS2000 network with around 40 clients being 60% Windows
2000 Pro and 40% WinXP Pro. We have around 3 member servers (not DC) and on
e
of the servers is a SQL server running SQL 2000.
A user on the domain has created a dBasev5.7 form that needs an ODBC System
DSN connection from the client to the SQL server in order for his form to be
populated with data. He wanted to roll out the DSNs to all the machines on
the network via Group Policy - I did a bit of digging around in the
Newsgroups and someone suggested this automated way by running a vbs script:
http://www.databasejournal.com/feat...le.php/2238221. Although
this works - we have a slight problem. We have no idea if this is to do wit
h
dBase (it uses 16-bit architecture) but when we roll out the DSNs using the
script the registry gets updated (HKEYLOCAL
MACHINE>SOFTWARE>ODBC>ODBC.INI...) with all the right keys (as if you set it
up manually in Control Panel's ODBC) but we need to go through the System DS
N
wizard (to the last page of the wizard) and click the "Test connection"
button in order for the dBase program to access the ODBC database connection
.
It seems as if the ODBC System DSN is dead without clicking the "Test
Connection" button.
Questions:
1. Is it a requirement to hit the Test Network Connection button on the
last page of the wizard. I thought that this was only for troubleshooting?
2. It appears that this "Test Connection" button in the wizard posts a
registry key to allow the ODBC SYstem DSN (it has created) to be used. Wher
e
abouts is this registry key?
3. Is there a Group Policy way of doing this? Chances are we may change
settings in the future and may want a server-side script running to update
any changes to clients automatically.
Please help.
THanks,
SkcI am not usre why this is happening, but I can tell you that the Test
Connection button does not write a registry entry anywhere. It is just used
for testing the connection. Possibly teh script leaves something out that
is necessary and going through the wizard completes the process.
It is possible that an alias to the SQL Server is geing created when the
wizard is accessed. There is a cliconfg tuiltiy that you can use to verify
this.
Run the script on a client machine and then run cliconfg from Start - Run.
Check the alias tab to see if there is an alias to this SQL Server. If not,
then run the Wizard and go through the steps. After it completes go back to
cliconfg and see if there is an alias. In many case the cliconfg utility
will create an alias or a SQL Server when a DSN has been created.
Rand
This posting is provided "as is" with no warranties and confers no rights.

ODBC security problem

Hi, I've a Access 2003 program with linked tables to a sqlexpress configured
with mixed mode security.
From clients XP no problem.
From clients 2000 MSACCESS don't get the right configuration from ODBC
source, because try to conect with Windows Authentication Mode security, but
the source is configurated with mixed mode. If I refresh the linked tables
and change the password, works fine, but when I close MSACCESS and open
again, fail.
Anybody knows where's the bug? In ODBC driver? MSACCESS?You could be hitting an MDAC bug where the authentication
mode setting is ignored and windows authentication is always
used. Refer to:
FIX: SQL Server ODBC Driver Ignores Authentication Setting
http://support.microsoft.com/?id=279526
You can use component checker to verify the version and MDAC
installation on any of the clients. You can download
component checker and MDAC versions from this site:
http://msdn.microsoft.com/data/ref/mdac/downloads/
-Sue
On Fri, 18 Aug 2006 11:27:01 -0700, Negri
<Negri@.discussions.microsoft.com> wrote:

>Hi, I've a Access 2003 program with linked tables to a sqlexpress configure
d
>with mixed mode security.
>From clients XP no problem.
>From clients 2000 MSACCESS don't get the right configuration from ODBC
>source, because try to conect with Windows Authentication Mode security, bu
t
>the source is configurated with mixed mode. If I refresh the linked tables
>and change the password, works fine, but when I close MSACCESS and open
>again, fail.
>Anybody knows where's the bug? In ODBC driver? MSACCESS?|||Thanks Sue, I'll try it next monday and tell you.
"Sue Hoegemeier" wrote:

> You could be hitting an MDAC bug where the authentication
> mode setting is ignored and windows authentication is always
> used. Refer to:
> FIX: SQL Server ODBC Driver Ignores Authentication Setting
> http://support.microsoft.com/?id=279526
> You can use component checker to verify the version and MDAC
> installation on any of the clients. You can download
> component checker and MDAC versions from this site:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> -Sue
> On Fri, 18 Aug 2006 11:27:01 -0700, Negri
> <Negri@.discussions.microsoft.com> wrote:
>
>

ODBC over VPN

We have designed a thick client accounting system using Microsoft Access 200
3.
The thick clients connect to MS SQL Server 2000 or 2005 via ODBC.
This works well in a LAN environment. We have customers that want us to
connect their many branches together now. These branch offices are located i
n
different geographical regions, which could stretched over 100 to 200km apar
t.
We know that having these systems designed in Web would solve our problems,
but since we do not have much time and the budget to convert all these
systems from Access 2003 to web, we thought that the fastest way out is to
connect the thick clients (located in these branch offices) to the Head
Quarters (which has the centralised MS SQL server databases) is still using
ODBC over a high speed broadband from between 1 MBps to 3 MBps.
To increase security, we though of creating different VPNs connect each
branch to the HQ, and have all these branch systems linked via ODBC over VPN
to the HQ.
Please advise me whether the above concept is alright or not, in terms of:
1. whether such networking will work in the first place? any need of doing
further software modification?
2. whether using VPN would solve security problems during data transfer
between offices?
3. whether the existing broadband speeds, as mentioned above, is sufficient
to do the job?
thank you all in advance
JOSEPH YOONGJoseph,
I think you'll find that ODBC over a VPN connection to have very poor
performance. The overhead of the VPN, combined with the overhead at the
application layer is a bad recipe for client/server connectivity. I've
seen this with a couple different OBDC/SQL based applications. The
bottom line is almost always to either port the application to something
web-based or to use Terminal Services with or without Citrix.
Greg
*** Sent via Developersdex http://www.codecomments.com ***|||"Greg Lara" wrote:

> Joseph,
> I think you'll find that ODBC over a VPN connection to have very poor
> performance. The overhead of the VPN, combined with the overhead at the
> application layer is a bad recipe for client/server connectivity. I've
> seen this with a couple different OBDC/SQL based applications. The
> bottom line is almost always to either port the application to something
> web-based or to use Terminal Services with or without Citrix.
> Greg
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
Hi Greg
Thanks a million for the reply.
I understand what you mean.
In my country, I have tested this ODBC over VPN with a 2MB fibre optic
broadband speed and it works OK for now.
This is only 1 client connected to 1 server. The distance between the client
and server is about 2km away. Not sure how this will fair if we have the
following scenarios:
1. distances between 50km to 500km or
2. clients between 5 to 100 per server?
I am not sure whether 2MB (upload and download speed will be sufficient for
now)
Web-based solution will definitely solve my situation here, I know. But I am
not willing to spend another 2 years for a major re-write. I have taken abou
t
6 years already to complete this software, when web based solutions were
still in their infancy stage. .NET technologies were not yet available then.
Is there a quick way of transferring my MS Access 2003 windows based thick
client to .NET 2 web based soluton? if yes, do let me know. I don't mind
spending some money (not time) to do the conversion.
Otherwise, please elaborate how terminal services can work. What I know
about terminal services is that everything is done by the server. The client
PC merely acts as a "dummy" terminal, only transferring key strokes to the
connected server. How can this improve the performance of client / server?
I have heard of Citrix before, but frankly not sure what this is. Could you
please help me understand.
Thanks
regards
joseph yoong

odbc over internet

We are running an sql server database on a Windows 2000 server, aproximately
15 clients on our lan use odbc connections to connect with acces 2002 front
end. No problem. Then i came up with the idea to connect a home-worker via
internet, using a vpn tunnel over isdn. Somehow this vpn-over-isdn
connection is unstable, as a matter of fact only few records can be entered
before the connection is lost. Is this a limitation of odbc connections or
just a miserable isdn solution wich need to be replaced?"b.binnenweg" <bobby01@.nospamxs4all.nl> wrote in message
news:402e0d12$0$557$e4fe514c@.news.xs4all.nl...
> We are running an sql server database on a Windows 2000 server,
aproximately
> 15 clients on our lan use odbc connections to connect with acces 2002
front
> end. No problem. Then i came up with the idea to connect a home-worker via
> internet, using a vpn tunnel over isdn. Somehow this vpn-over-isdn
> connection is unstable, as a matter of fact only few records can be
entered
> before the connection is lost. Is this a limitation of odbc connections or
> just a miserable isdn solution wich need to be replaced?
>
That sounds like a connection issue... are you experiencing high latency
from the client to the server? Also, linked Access tables are not that
efficient and not recommended for WAN access.
Steve