mssql lacks

[Markon]

Hello
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

[Erland Sommarskog]

Markon (ma****@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, es****@sommarskog.se

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

[David Portas]

All the features you think are lacking are in fact supported by SQL Server.
You just need to learn to write proper SQL instead of missing the Access
quirks that aren't there. Access is a fine application development tool but
as a database it's just a toy. I'm glad SQL Server looks nothing like it.

--
David Portas
SQL Server MVP
--

[Chandra]

Hi

You are the first person I have seen who is disappointed with MS SQL
Server.

MSSQL Server is a RDBMS and Supports all ANSI and T-SQL syntaxes. Just
learn how to write queries in using ANSI SQL and you would definately
love using SQL Server.

The problem u were facing with Grouping can be easily solved using
derived tables or aliasing

..and there are so many user groups available to help you out when u are
stuck.

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Chandra (ch*****@discussions.hotmail.com) writes:

MSSQL Server is a RDBMS and Supports all ANSI and T-SQL syntaxes.

Ehum, there is a whole bunch of ANSI syntax that MS SQL Server does
not support.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Craig Kelly]

"Markon" wrote:

Hello
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

Just so you know... IIf and Format are in Access because the JET database
engine allows you to use native VBA functions. And if you're actually using
Access (instead of just the JET engine via VB, C++, etc), you can also use
VBA functions that you've created.

The problem you're facing is that Access/JET is the only database system
(that I know of) that allows you to use VBA that way. The fact is, if you
want to continue to use them, you'll need to stay with Access (instead of
Oracle, Sybase, SQL Server, DB2, whatever). Your only options would be to
rewrite your SQL (as you mentioned) or implement these functions in your
target environment.

Craig