MS Sql Server Express vs. Visual Studio? Embedded DISTINCT functionality?

[Joe Willis]

I've been having issues running certain commands in Sql Server Express 2008.

One of the specific commands in question looks like this:

SELECT COUNT(DISTINCT Sno) as NumberShipping
FROM SHIPMENT;

This commands works perfectly in visual studio 2008, but I get an error in sql server express.

The error is:
There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = DISTINCT ]


I was also unable to use varchar(), float, real, and have been unable to get an "except" statement working. Does sql server express not support these commands, or did I more than likely end up with a bad installation?

Thanks!

[NeoPa]

Try :

SELECT   Count(*)
FROM     (
    SELECT DISTINCT [SNO]
    FROM   [Shipment]
    )

Joe Willis:
BTW: I'm wondering if my SQL Server Express installed correctly. Currently, it doesn't recognize varchar's, and I have yet to be able to get an "except" clause working without an error.

Sounds like a completely separate question. If you're interested, then post a new thread. It's not relevant here.

[NeoPa]

Joe, I know you posted the first time before registering, but do you really think double posting your question will get you help any quicker?

I suggest all you'll get is grief from all the experts whose time you've wasted. Not a great start.

PS. I've moved the other response in here and deleted the older thread. If the response isn't an exact match to the question then you know why.

[Joe Willis]

I'm sorry NeoPa, I couldn't find my original post before registering.

    SELECT   Count(*)
    FROM     (
        SELECT DISTINCT [SNO]
        FROM   [Shipment]
        )

I had tried this aswell. Didn't work in sql express 2008.


On a side note, both of the above queries work in visual studio.

[NeoPa]

Joe Willis:
On a side note, both of the above queries work in visual studio.

Bearing in mind Visual Studio is simply a view by which you see whichever SQL Server you're using, I'm guessing this is actually referring to a full SQL server. I have no idea why the Express edition would behave any differently from the full edition, but that would be the best area to look in if that's the case. It would be a surprise for me I admit, but if that's what you're seeing I doubt playing with the SQL code will help greatly.