Posted by Erland Sommarskog on 07/21/05 01:25

Markon (markon@yahoo.com) writes:
> I have recently decided to upgrade my programs to enable users to have
> mssql databases instead of access. I have since then run into many
> incompatibilities between their sql: access has IIF(x>y,a,b) whereas
> mssql hase case when (this already means hundreds of changes in queries)
> it does not have format(number,'#,##0.00') or format(date,'dd.MM.yyyy')
> but most surprising is following:
>
> access allows this while mssql reports error ("Cannot perform an aggregate
> function on an expression containing an aggregate or a subquery")
> SELECT ....
> SUM(a*b/(SELECT SUM(c) FROM d WHERE e=f))
> FROM ...
> (a-f are fields and tables )
>
> I am totaly dissapointed in MS since I will have to have two variants of
> queries in programs just to enable users to choose between databases.
> Does anyone know an MS e-mail where I could flame them

That would be fairly pointless. Access and MS SQL Server has completely
disjunct histories. I don't really know about the origins of Access, but
recall that SQL Server has its origins at Sybase, and until Microsoft
broke the partnership with Sybase, they did not have any control
over the syntax at all.

And there are too many difference for a merge to be really possible. For
instance on Access you can do this:

SELECT a + b + c AS d, d + p AS f
FROM tbl1, tbl2
WHERE ...

In Access "d" io "d + p" refers to "a + b + c". But on SQL Server, if
there is a column tbl2.d, this is what d refers to. Thus, the two
syntaxes are incompatible.

The best you can do, is to try find syntax that actually works on
both Access and SQL Server. Sometimes this can be difficult. For things
like formatting of dates and number, try to keep that out of the
database part altogether.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]