Query from Access 2000 won't work in MSDE 2000

I have the following SQL query which works great in MS Access 2000
SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
[tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
[tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
[tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
[tbKeymap].[ItemIndex]
This query does not work in MSDE 2000.
I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near '.'.
I have no idea why this is the case since the MDAC is 2.7 (I'm
assuming since this is MSDE 2000).
Any ideas or solutions would be appreciated.
Ali
SQL Server (MSDE) and Access use different dialect of SQL language.
keyword DISTINCTROW does not exist in SQL Server's T-SQL: it is DISTINCT in
SQL Server, while in Access, you have DISTINCTROW and DISTINCT, they do
things slightly different
"Ali Syed" <alijsyed@.hotmail.com> wrote in message
news:26c82868.0408181145.34f0f6e9@.posting.google.c om...
> I have the following SQL query which works great in MS Access 2000
> SELECT DISTINCTROW [tbMenus].[MenuIndex], [tbKeymap].[Key],
> [tbItems].[Item], [tbItems].[price], [tbKeymap].[ItemIndex],
> [tbItems].[tax1], [tbItems].[tax2] FROM [tbItems] INNER JOIN
> ([tbMenus] INNER JOIN [tbKeymap] ON [tbMenus].[MenuIndex] =
> [tbKeymap].[MenuIndex]) ON [tbItems].[ItemIndex] =
> [tbKeymap].[ItemIndex]
>
> This query does not work in MSDE 2000.
> I get the following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
> syntax near '.'.
>
> I have no idea why this is the case since the MDAC is 2.7 (I'm
> assuming since this is MSDE 2000).
>
> Any ideas or solutions would be appreciated.
>
> Ali
|||Thanks Norman that fixed it.
It seems that I will have to compensate for this issue.
Ali

Query From ACCCESS to MSDE

First of all...sorry for writing in english.
I'm trying to pass from access to msde.
I have a query that returns some values to populate a listview.
With access this query works in the right way, but in msde i have some
problems .
That's the query
rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
anagp.data desc", cn, adOpenStatic, adLockOptimistic
I've checked out in the immediate window that the query works well but when
I populate the LW I have this problem
li.SubItems(2) = "" & rs("anagp.nome").Value
where li has the dimention of a listitem...
Error: cannot find anag.nome
Why'
Any suggestion will be very apprecitated
Thanx
IAKwhy dont to try adps
"PiantoTardivo" wrote:

> First of all...sorry for writing in english.
> I'm trying to pass from access to msde.
> I have a query that returns some values to populate a listview.
> With access this query works in the right way, but in msde i have some
> problems .
> That's the query
> rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
> where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
> anagp.data desc", cn, adOpenStatic, adLockOptimistic
> I've checked out in the immediate window that the query works well but whe
n
> I populate the LW I have this problem
> li.SubItems(2) = "" & rs("anagp.nome").Value
> where li has the dimention of a listitem...
> Error: cannot find anag.nome
> Why'
> Any suggestion will be very apprecitated
> Thanx
> IAK
>
>|||Looks like zour problem is the name of the column.
1. make sure that the column names are correct, you wrote once anagp.nome
and once anag.nome
2. instead of doing select * ... and then acessing anag(p).nome consider
someting like this:
select anagp.nome as a_nome, ...
and then you can use
rs("a_nome")
to access the column
HTH, Fred
"PiantoTardivo" wrote:

> First of all...sorry for writing in english.
> I'm trying to pass from access to msde.
> I have a query that returns some values to populate a listview.
> With access this query works in the right way, but in msde i have some
> problems .
> That's the query
> rs.Open "select * from anagp inner join ingrp on anagp.incr = ingrp.rif
> where ingrp.lotto='" & ListView2.SelectedItem.SubItems(1) & "' order by
> anagp.data desc", cn, adOpenStatic, adLockOptimistic
> I've checked out in the immediate window that the query works well but whe
n
> I populate the LW I have this problem
> li.SubItems(2) = "" & rs("anagp.nome").Value
> where li has the dimention of a listitem...
> Error: cannot find anag.nome
> Why'
> Any suggestion will be very apprecitated
> Thanx
> IAK
>
>

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
Message posted via http://www.droptable.com
Hi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FCC7EC4933C0@.droptable.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.droptable.com

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
Message posted via http://www.droptable.comHi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FCC7EC4933C0@.droptable.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.droptable.com

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
--
Message posted via http://www.sqlmonster.comHi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:4FCC7EC4933C0@.SQLMonster.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.sqlmonster.com

query for date range

I use oledb (ACCESS database) in my application. i want to build a query to retrieve the number of Bookings from my Booking table where the appointment_date_time.timeOfADay is in range of 9am-12pm or 14pm- 7pm, that is (>= 9 and <12) or (>= 14 and < 17). Please help to build the query,

I found some query sample like:

select * from tblstudents where classID='1' and studentstartdate between ('2004-12-03') and ('2004-12-12')

or

WHERE DateField BETWEEN @.StartDate AND @.EndDate

But I dont want to search year and month and day, i just want to search the actual hour of a day. i am stuck with the syntax, please help

Did you try the HOUR function. I will return you the value of the actual hours, this can be filtered like any integer column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Query faster in Access than SQL Server

I have an VB application which i recently change from using an Access database to SQL Server. I upsized the Access Database to SQL. The query takes about 1 second to run when run from access. It takes about 1 minute to run from SQL server using query analyser or through my application. I would be greatful for suggestions. The query is:

SELECT *
FROM (((((((WorkLog AS W LEFT JOIN Grp AS G ON w.wrkgrpsysid = g.GrpSysId)
LEFT JOIN Register AS R ON w.wrkregSysId = r.regSysId)
LEFT JOIN Location AS L ON w.wrklocSysId = l.locSysId)
LEFT JOIN CostCenter AS C ON w.wrkcstSysId = c.cstSysId)
LEFT JOIN SubAssembly AS S ON w.wrksubsysid = s.subsysid)
LEFT JOIN Priority AS Pr ON w.wrkpriority = pr.prisysid)
LEFT JOIN Plann AS P ON w.wrkplnsysid = p.plnsysid)
Left join Route RT on w.wrkrtesysid=rt.rtesysid
Where (WrkType = 'PM' or (WrkType = 'PL') or (WrkType = 'UN'))What indexes do you have defined for these tables? One way to get a handle on what's going on is to use Query Analyzer to view the execution plan. Paste your query in to QA and on the menu select Query->Show Execution Plan. or hit Ctrl + K. When your query runs you will see what indexes are being ussed OR if a table scan is being performed.|||The indexes that are defined in these tables are the ones that have been upsized fromthe Access db. They are all primary indexes. There were no indexes for WrkType. I created some and the time did not improve.

Looking at the execution plan it appears that there are a lot of table scans going on and then results are joined, I cant see any reference to the indexes here. Should this be happening when i have indexes?|||if you have usable indexes, yes.

you could use the index wizard. In QU have your query ready to go, select Query -> Index Tunning Wizard. Just follow the steps and select thorough for the tuning mode. This will look at your query, tables, indexes and statistics. If more optimal indexes or statistics are needed you have an option of creating them or scripting them for later execution.

Try this and post back with questions.|||Paul,

I did as you said and it reported that no indexes are used in executing the query. It also reported that no changes were needed.

I cant think of anything other than the indexes that would be causing such a dramatic difference in the execution times. Would clustering some of the indexes help?

It seems strange considering that the DB has been upsized with all the indexes etc.. I presume that this is a common problem?|||I just converted my access database to SQL server (converting most queries to Stored Procedures) and have found that my queries run somewhat slower on my standalone system, but apparently execute much faster than access originally in our companies production system.|||Run UPDATE STATISTICS on your tables. I'm guessing your query plan was originally compiled when there was little or no data in your tables, hence the optimizer chose not to use the indexes.

blindman|||I can't imagine access handling anything better than SQL Server. I'd also say make sure your statistics are updated, and are set to automatically update. Also, make sure you have indexed fields in the most efficient ways. AND make sure your server is using all the processors it has available, etc. If there are table scans going on, then I would think that the indexes aren't set up properly.

I've tried to upsize db's from access before, and had nothing but problems. If I were you, I'd build the db like I wanted in in SQL Server, then export the Access data to a text file or something and Import it into SQL Server. That's proven the best way for me.|||Thanks everyone for your help so far.

Ive tried to see where delay is coming from. When i run the following query:

select WrkSysid
from worklog
where (wrktype = 'PM')

it does an index seek on the field 'wrktype' and an index scan on the field 'Wrksysid'. But when i run the following query:

select WrkSysid, WrkGrpSysid
from worklog
where (wrktype = 'PM')

it does a table scan. This is despite having indexes on all 3 fields. Ive run UPDATE STATISTICS on all the tables. Is this normal?|||I came across such an amount of problems, that I wrote and used my own conversion program.

Your query has a lot of joins; do you have a referential constraint between WorkLog.wrkgrpsysid and Grp.GrpSysID, for example ? If not, make sure that you have an index (simply with duplicates) on wrkgrpsysid and an index (preferabelly UNIQUE) on GrpSysID.|||Is the index a clustered index? Or do you have a seperate index defined for each field? It looks like you have seperate indexes on each field. If so, try using a clustered index containing both of those fields.|||Originally posted by AnSQLQuery
Is the index a clustered index? Or do you have a seperate index defined for each field? It looks like you have seperate indexes on each field. If so, try using a clustered index containing both of those fields.

There is plenty of noise in your remark, Query.

1) I guess you mean with "clustered index" one index on several fields, instead of two indices?! Such an indx does not have a special name.
2) You can't create an index on fields in two tables!
3) With a clustered index, you store the whole table according to the sort order of the index. The question, however, is to join related data to a WorkLog table, which shall be scanned fully without any ordering.

Depending on the selectivity, you may increase the speed by defining an index on the criteria field WrkType.|||1) I guess you mean with "clustered index" one index on several fields, instead of two indices?! Such an indx does not have a special name.

Doc,
This would be called a composite index.|||Thanks Rocket, do you also have a name for an index on just one field?|||Do you have a composite index on wrktype and wrkSysid? If so, your first query would execute solely on the index and never touch the actual table, making it very fast. Your second query would have to reference the table to pull in the WrkGrpSysid value.

Also, a lot depends upon the cardinality of your data, which describes how many unique values are in a given column or combination of columns. If the cardinality of wrktype is low (say there are only two values; "AM" and "PM") then the optimizer gains little or nothing from using an index on that column because it does not significantly reduce the number of pages it has to search through. It would thus ignore the index.

blindman

PS: Indexes on single columns are just called indexes.|||wow, next time i won't drink so much before posting...sql

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
Antonin
The answer is in the message you've got. TempDB has grown and there is no disk space to continue growth.
|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not allocate new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
AntoninThe answer is in the message you've got. TempDB has grown and there is no disk space to continue growth.|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

query failure

I've got an email from one of the users complaining about query failure. He
is doing queries using MS ACCESS. He is getting the following error message:
ODBC - call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Could not alloca
te new page
for database 'TEMPDB'. There are no more pages available in filegroup
DEFAULT. Space can be created by dropping objects, adding additional files,
or allowing file growth. (#1101)
However, the TEMPDB is 60MB in size with 59MB free. It has unrestricted
growth as well. Any suggestions?
Thanks in advance,
AntoninThe answer is in the message you've got. TempDB has grown and there is no di
sk space to continue growth.|||Thanks Ray. I cleaned up the disk and the problem is gone.
Antonin
"Ray D" <ray_d@.mail.ru> wrote in message
news:05B82F27-73AB-4EAA-A10E-4A46A67C71D2@.microsoft.com...
> The answer is in the message you've got. TempDB has grown and there is no
disk space to continue growth.

query execution failed for dataset

have all my reports on a server, and so far have had no problems giving
users access. They are all located in the Cg Role, i created. Here's the
delima, I added a new user and to this role. The user can view report
manager, view the folders, and view the reports..but when they try to run a
report, they get this error.
query execution failed for dataset "CrashStatisticalYear" (rsError
executingcommand)
She has access to everything, why is she the only one getting this error'
Please Help!It seems like she doesnt have access to a specific table inside that
dataset.
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:4088044D-2DD6-4FB1-A9BC-59BED72B1C24@.microsoft.com...
> have all my reports on a server, and so far have had no problems giving
> users access. They are all located in the Cg Role, i created. Here's the
> delima, I added a new user and to this role. The user can view report
> manager, view the folders, and view the reports..but when they try to run
> a
> report, they get this error.
>
> query execution failed for dataset "CrashStatisticalYear" (rsError
> executingcommand)
>
> She has access to everything, why is she the only one getting this error'
>
> Please Help!
>

Query Engine Error?

I have a report which displays some customer information and their transactions. The data is coming from an MS Access Database. The user must specify a customer either by customer Number or by their Last Name. I have C#.NET code which does this. Unfortunatley the data doesn't display on the report if the customer doesn't have any transactions. I asked another developer and he said to change the joins to right joins. The closest option i have is right outer join so i selected that. When i do this and run the report i get "Query Engine Error". It also displays the filepath to the rpt file. The database has been built up over a number of years and is not well normalized. Therefore i have more tables in the report then I would like. The database is quite large and would take alot of time to normalize that database and the program using the database would have to be altered as well. As you may have guessed, that code has also been developed over a number of years and again would take alot of time to change that. Is there an easy solution to my problem. I am not too experienced with Crystal Reports and any help would be greatly appreciated.Run the right join query in Access and see if it works

Query Design in SQL 2000

Hello everyone,
I need to convert Access 2000 dbs (just tables) to SQL
2000. I am in the process of learning how to work with
tables (import,delete,append,update..etc) I am new at SQL
and like many of us, with little or none time to spend
trying to figure out the online help. So I decided to
write to you asking for help. A few basic but important
questions:
Is there a feature in SQL that allows me to graphically
manipulate tables? Just like with queries in Access...or
do I need to learn SQL language to do so?
How do I set the relations among my tables?
Where do I look for help to write DPS packages to import
csv files and update my tables?
Once I am done moving everything to SQL..is it worth it
to link this tables to Access and keep working with it as
usual?...or it would be detrimental in terms of speed?
The only reason I upgrade to SQL is because my dbs grew
too much for Access to handle and I need to build a Data
Warehouse.
I thank in advance to anybody who can answer all or any
of my questions...or maybe just guide me a little.
Gustavo
(ex-Access-wizard now SQL-dumb)
For imports/export Access > SQL Server,
look into the DTS (data transfer services) in
the manager under Tools,
For Access like Tables, Right click on your
table and "Design"
Quick relationships can be made Access-like
by putting your tables into a "Diagram" and
you can link tables together.
It is amazing how little SQL you really have
to know to use SQL server. It is almost like
Access but all grown up...
I'm sure you'll get other tips/tricks from this
message.
Bob M.
"Gustavo" <anonymous@.discussions.microsoft.com> wrote in message
news:282ed01c46396$28707780$a301280a@.phx.gbl...
> Hello everyone,
> I need to convert Access 2000 dbs (just tables) to SQL
> 2000. I am in the process of learning how to work with
> tables (import,delete,append,update..etc) I am new at SQL
> and like many of us, with little or none time to spend
> trying to figure out the online help. So I decided to
> write to you asking for help. A few basic but important
> questions:
> Is there a feature in SQL that allows me to graphically
> manipulate tables? Just like with queries in Access...or
> do I need to learn SQL language to do so?
> How do I set the relations among my tables?
> Where do I look for help to write DPS packages to import
> csv files and update my tables?
> Once I am done moving everything to SQL..is it worth it
> to link this tables to Access and keep working with it as
> usual?...or it would be detrimental in terms of speed?
> The only reason I upgrade to SQL is because my dbs grew
> too much for Access to handle and I need to build a Data
> Warehouse.
> I thank in advance to anybody who can answer all or any
> of my questions...or maybe just guide me a little.
> Gustavo
> (ex-Access-wizard now SQL-dumb)
>
|||Thanks a lot for the tips Bob. It certainly is the Access
big brother
>--Original Message--
>For imports/export Access > SQL Server,
>look into the DTS (data transfer services) in
>the manager under Tools,
>For Access like Tables, Right click on your
>table and "Design"
>Quick relationships can be made Access-like
>by putting your tables into a "Diagram" and
>you can link tables together.
>It is amazing how little SQL you really have
>to know to use SQL server. It is almost like
>Access but all grown up...
>I'm sure you'll get other tips/tricks from this
>message.
>Bob M.
>
>"Gustavo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:282ed01c46396$28707780$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
Access...or[vbcol=seagreen]
import[vbcol=seagreen]
as[vbcol=seagreen]
Data
>
>.
>

Query could not be processed. Access is forbidden

I am using http to access my cube (MS Analysis Server) and there are users
who are getting "Query could not be processed. Access is forbidden"
intermittently. Can someone help out and let me know what is causing this
issue? Some users are not getting this error.Hi

Googling on this and it does not turn anything up directly, although there
does seem to be an underlying implication that is it either connectivity or
permissions that is causing "Query could not be processed.". Without more
details it is not possible to say more, but I suggest that you check the
usual sources for this type of problem, such as: number of connections on
the server, the reliability of the network to connect, if different users
can connect from the same machines, if the machines that can't connect are
using the same versions of software.

Further information may spread more light on this, such how is the
connection being made etc..

John

"Ramir Santos" <ramir@.optonline.net> wrote in message
news:qag3d.80$K85.18831@.news4.srv.hcvlny.cv.net...
> I am using http to access my cube (MS Analysis Server) and there are users
> who are getting "Query could not be processed. Access is forbidden"
> intermittently. Can someone help out and let me know what is causing this
> issue? Some users are not getting this error.
>|||John,
Thanks for spending time to answer my question.
as a followup, here are some of the details
1. we are using office web component to connect to the cube (office xp
sp 2)
2. 2 separate machine, 1 hosting our aspx and the other with Analysis
Services and IIS installed. Analysis services installed using developers
edition
3. we only have 5 users right now
let me know what other information you would need to help me out on
this. appreciate your time and effort!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:414daf9b$0$20644$afc38c87@.news.easynet.co.uk. ..
> Hi
> Googling on this and it does not turn anything up directly, although there
> does seem to be an underlying implication that is it either connectivity
> or
> permissions that is causing "Query could not be processed.". Without more
> details it is not possible to say more, but I suggest that you check the
> usual sources for this type of problem, such as: number of connections on
> the server, the reliability of the network to connect, if different users
> can connect from the same machines, if the machines that can't connect are
> using the same versions of software.
> Further information may spread more light on this, such how is the
> connection being made etc..
> John
>
> "Ramir Santos" <ramir@.optonline.net> wrote in message
> news:qag3d.80$K85.18831@.news4.srv.hcvlny.cv.net...
>> I am using http to access my cube (MS Analysis Server) and there are
>> users
>> who are getting "Query could not be processed. Access is forbidden"
>> intermittently. Can someone help out and let me know what is causing this
>> issue? Some users are not getting this error.
>>
>>
>>
>>
>>|||Hi Ramir

It looks like this may be the problem?
http://tinyurl.com/4obw5

John

"Ramir Santos" <ramir@.optonline.net> wrote in message news:<Qyp3d.150$gR1.273403@.news4.srv.hcvlny.cv.net>...
> John,
> Thanks for spending time to answer my question.
> as a followup, here are some of the details
> 1. we are using office web component to connect to the cube (office xp
> sp 2)
> 2. 2 separate machine, 1 hosting our aspx and the other with Analysis
> Services and IIS installed. Analysis services installed using developers
> edition
> 3. we only have 5 users right now
> let me know what other information you would need to help me out on
> this. appreciate your time and effort!
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:414daf9b$0$20644$afc38c87@.news.easynet.co.uk. ..
> > Hi
> > Googling on this and it does not turn anything up directly, although there
> > does seem to be an underlying implication that is it either connectivity
> > or
> > permissions that is causing "Query could not be processed.". Without more
> > details it is not possible to say more, but I suggest that you check the
> > usual sources for this type of problem, such as: number of connections on
> > the server, the reliability of the network to connect, if different users
> > can connect from the same machines, if the machines that can't connect are
> > using the same versions of software.
> > Further information may spread more light on this, such how is the
> > connection being made etc..
> > John
> > "Ramir Santos" <ramir@.optonline.net> wrote in message
> > news:qag3d.80$K85.18831@.news4.srv.hcvlny.cv.net...
> >> I am using http to access my cube (MS Analysis Server) and there are
> >> users
> >> who are getting "Query could not be processed. Access is forbidden"
> >> intermittently. Can someone help out and let me know what is causing this
> >> issue? Some users are not getting this error.
> >>
> >>
> >>
> >>
> >>

Query by Example in Enterprise Manager

Why hasn't Microsoft adopted the same level of excellent QBE in Enterprise Manager that is included with MS Access for building queries? I am just not a die hard fan of using Query Analyzer and templates for building complex queries. I can understand using QA for performance tuning, etc. but for queries like UPDATE queries using RIGHT OUTER JOINS that have many attribute references, I miss my QBE in Access.

When I did my Masters, I wrote a research paper on Query By Example developed in the 70's by Dr. Mosh Zloof of IBM and there are some really good points about it.

1.) Visually seeing the relationships and joins.

2.) Elimination of typo errors.

3.) Eliminates reference ambiguity.

I don't mind keying in a complex query, but a nice visual tool with the relationships/joined mapped out and the elimination of reference ambituity would make me feel a little better before I hit the Execute Query button. Sometimes the Parse Query button does not exactly give me that warm fuzzy feeling but it helps.

Does anyone know if MS ever plans to adopt the level of QBE provided in MS Access or are there any third party tools available in comparison?

I'm not asking for help with keying in queries through Query Analyzer or any arguments about why that is so much greater. I'm just looking for QBE improvements in SQL server's Enterprise Manager.I understand what you are saying about visualizing the query and seeing the relationship. You are right in saying that MS Access does a good job of that, however, as a DBMS, Access just doesn't cut it. SQL Server is great.

If you are looking to visualize in SQL Server, there is a diagram tool under the database that you are working with so you can build relationships... you can also perform a visual query by right clicking on a table and selecting query from the popup menu. From there you can add tables and make relationships all by pointing and clicking... good thing to do if you aren't too sure about sql... it looks just like access's version and runs almost the same way.

But hey, look at it this way... in Access you are not in control... you simply control where you point and click and Access does the rest. In Query Analyzer, you are more than just a user ... you have flexibility and at the same time you are becoming more and more competant and confident in writing SQL and T-SQL... Remember, there is hardly anything that can't be fixed. So don't be afraid to click that button!!!!! Keep going at it and you'll be a SQL pro in no time!!!!!!|||Believe me, I've been working with SQL Server for 2 1/2 years but I still miss the QBE capability of Grid Analyzer in MS Access. For example, in SQL's Query Analyzer you can't add a joined table for an UPDATE query but you can for a SELECT. What's up with that? Seems pretty basic to me.|||Personally, I think it's funny that everyone balks at using QBE in EM for query building. I found that in Access, my percentage of reference ambiguity using Grid Manager was zero. However, when I use QA and templates, it's probably 5 to 25% and then I have to make corrections. To me, it's like comparing BASIC to VISUAL BASIC. Visual tools are awesome! Why people think it's such an insult to use them is beyond me. Just my two cents.