Bug #13121 access denied - bug or feature?
Submitted: 12 Sep 2005 13:51 Modified: 18 Jul 2007 19:57
Reporter: Oliver Peters Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.11-2 & 5.0.0.-alpha OS:Microsoft Windows (Windows2000)
Assigned to: CPU Architecture:Any

[12 Sep 2005 13:51] Oliver Peters
Description:
I tried to get access to a view from MS EXCEL /MS QUERY and got the following message (in German):

Auf die tabelle rohehastra.sortgrupp71 kann nicht zugegriffen werden

This is the SQL-Statementthat that belongs to the view:

CREATE ALGORITHM=UNDEFINED VIEW `rohehastra`.`sortgrupp71` AS select `r`.`VKH03` AS `Anz Schuppen` from `rohehastra`.`roh2004` `r`;

Deleting a space leads to success

CREATE ALGORITHM=UNDEFINED VIEW `rohehastra`.`sortgrupp70` AS select `r`.`VKH03` AS `AnzSchuppen` from `rohehastra`.`roh2004` `r`;
                            ^^^   
Is this a wanted behaviour? In query browser that's no problem!

How to repeat:
see above
[12 Sep 2005 14:42] Miguel Solorzano
Could you please provide a test case script with all statement
because on my own I was unable to reproduce on Excel 2003.

Thanks in advance.
[12 Sep 2005 16:11] Oliver Peters
no chance to add the view

Attachment: myodbc_excel_bug.png (image/png, text), 27.89 KiB.

[12 Sep 2005 16:11] Oliver Peters
It's the view

CREATE ALGORITHM=UNDEFINED VIEW `rohehastra`.`sortgrupp71` AS select
`r`.`VKH03` AS `Anz Schuppen` from `rohehastra`.`roh2004` `r`;

that makes the problem and the problem is solved by deleting the space in `Anz Schuppen` so that it is `AnzSchuppen`

I can't do the query because I get no access (look at the png) and can't choose to add the view. BTW: I use EXCEL2003 with every available patch
[12 Sep 2005 18:35] Miguel Solorzano
Thank you for the feedback and bug report. Now I understood
what you meant:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.11-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tbl1 (id int, name char(20));
Query OK, 0 rows affected (0.09 sec)

/* the below view not works on Excel */

mysql> create view tbl1_view as select name as "people name"
    -> from tbl1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tbl1 set id=1, name="Peter";
Query OK, 1 row affected (0.03 sec)

mysql> select * from tbl1_view;
+-------------+
| people name |
+-------------+
| Peter       |
+-------------+
1 row in set (0.00 sec)

mysql> drop view tbl1_view;
Query OK, 0 rows affected (0.00 sec)

/* The below view works */

mysql> create view tbl1_view as select name as "peoplename"
    -> from tbl1;
Query OK, 0 rows affected (0.00 sec)
[17 May 2007 20:35] Jim Winstead
Bug #14653 was marked as a duplicate of this bug.
[18 Jul 2007 19:57] Jim Winstead
This is a duplicate of Bug #2966.