Access query fails in SQL server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access query fails in SQL server 2000

Author

Topic

belrik
Starting Member

3 Posts

Posted - 2005-08-10 : 12:08:50

I have a very large but straightforward query in access to count and assess some software licences. It works perfectly in access 2003 but fails in SQL server 2000, I have tried changing the ' to " and vice versa but get the same errors. Could someone please help me.

SELECT * FROM
(SELECT "Office" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE '*office*') as A,
(SELECT COUNT(office) AS Licenses_used FROM raw WHERE office IS NOT NULL) as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE licenses.product='All' AND licenses.licensee=raw.owner AND raw.office IS NOT NULL)
UNION
SELECT * FROM
(SELECT "Project" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE 'project*') as A,
(SELECT COUNT(project) AS Licenses_used FROM raw WHERE project IS NOT NULL) as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE licenses.product='All' AND licenses.licensee=raw.owner AND raw.project IS NOT NULL)
UNION
SELECT * FROM
(SELECT "Visio" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE '*visio*') as A,
(SELECT COUNT(visio) AS Licenses_used FROM raw WHERE visio IS NOT NULL) as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE licenses.product='All' AND licenses.licensee=raw.owner AND raw.visio IS NOT NULL)
UNION
SELECT * FROM
(SELECT ".NET" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE '*.NET*') as A,
(SELECT COUNT(VS) AS Licenses_used FROM raw WHERE VS IS NOT NULL) as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE (licenses.product='All' OR licenses.product LIKE '*VS*') AND licenses.licensee=raw.owner AND raw.VS IS NOT NULL)
UNION
SELECT * FROM
(SELECT "SQL Server 2000 standard" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product = 'SQL 2K') as A,
(SELECT COUNT(DB) AS Licenses_used FROM raw WHERE DB='sql 2k') as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE licenses.product='All' AND licenses.licensee=raw.owner AND raw.DB='sql 2k')
UNION
SELECT * FROM
(SELECT "SQL Server 7 standard" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE 'SQL 7*') as A,
(SELECT COUNT(DB) AS Licenses_used FROM raw WHERE DB LIKE 'sql 7*') as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE licenses.product='All' AND licenses.licensee=raw.owner AND raw.DB LIKE 'sql 7*')
UNION
SELECT * FROM
(SELECT "SQL Server Enterprise" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE 'SQL*ent*') as A,
(SELECT COUNT(DB) AS Licenses_used FROM raw WHERE DB LIKE 'sql*ent*') as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE raw.DB = 'rubbish')
UNION
SELECT * FROM
(SELECT "SQL Server Developr Edition" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE '*msde*') as A,
(SELECT COUNT(DB) AS Licenses_used FROM raw WHERE DB LIKE '*msde*') as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE licenses.product='All' AND licenses.licensee=raw.owner AND raw.DB LIKE '*msde*')
UNION
SELECT * FROM
(SELECT "MSDN" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.license_name LIKE '*MSDN*') as A,
(SELECT COUNT(license_name) AS Licenses_used FROM licenses WHERE licenses.license_name LIKE '*MSDN*') as B,
(SELECT COUNT(owner) AS MSDN_licenses FROM raw,licenses WHERE raw.DB = 'rubbish')
ORDER BY product;

Also for some reason I can't use "SELECT "NA" AS MSDN_licenses" in access 2003 even though its valid SQL. I just wanted to put NA for a couple of values. Pain in the arse.

Any help is appreciated
Stephen

PETE314
Starting Member

37 Posts

Posted - 2005-08-10 : 12:48:05

On your Like statements.....doesn't SQL 2000 use the % as opposed to the *

belrik
Starting Member

3 Posts

Posted - 2005-08-10 : 13:58:24

Cool- I'll try that asap; very irritating that MS feels the need to create its own standard for SQL server and then drift even further off course in Access. I was assuming that while they weren't necessarily fully SQL compliant they were at least the same. Foolish me.

quote:
Originally posted by PETE314

On your Like statements.....doesn't SQL 2000 use the % as opposed to the *

belrik
Starting Member

3 Posts

Posted - 2005-08-11 : 04:24:12

This hasn't changed a thing- I get a ton of errors still:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'UNION'.
Server: Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'UNION'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'UNION'.
Server: Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'UNION'.
Server: Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'UNION'.
Server: Msg 156, Level 15, State 1, Line 35
Incorrect syntax near the keyword 'UNION'.
Server: Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'UNION'.
Server: Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'ORDER'.

It seems that the SELECT "<string value" doesn't work. Yet it should, any ideas? It seems to dislike my UNION commands as well which should work, and indeed do work in Access.

PETE314
Starting Member

37 Posts

Posted - 2005-08-11 : 15:06:58

Well Transact SQL does not like the double quotes instead use the single quotes. Other than that....have you thought of creating these individual as Views and then you wouldn't have as complicated SQL statement...for example your SELECT "Office" AS Product,COUNT(product) AS Licences_bought FROM licenses WHERE licenses.product LIKE '*office*' could be a name of a veiw. Or you could do each section between the Union Commands as a separate view and in that way you can have Select * from A Union B Union C....etc....

TheZEDMan
Starting Member

1 Post

Posted - 2007-07-13 : 11:06:21

Also something to note: in SQL server if you put a SELECT query inside parenthesis, it must have an "AS" clause after the ending parenthesis.

I found this out by using a perfectly valid in MySQL query "(SELECT a, b FROM c ORDER BY a)" and in SQL Server I got "Incorrect syntax near the keyword 'ORDER'." as a result.

To fix this I did the following "SELECT a, b FROM c ORDER BY a".

I also read in some post (4th or 7th page on Google) that you can also do "SELECT * FROM (SELECT a, b FROM c ORDER BY a) AS P" or to get really fancy "SELECT * FROM (SELECT a, b FROM c ORDER BY a) AS P ORDER BY P.b".

Reference:
http://www.webdeveloper.com/forum/archive/index.php/t-42236.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources