Susan, there is nothing to do with Service Packs.

Look at yor data first.

In Staging table vehicle_tmp just contains 2 or more lines  with the same vehicle_nmb. And your query...

Don't forget at the end of the day to compare performance of this solution with performance of simple PIVOT with aggregates.

Why you are so afraid of aggregates?

They do not harm people. Honestly.

If there is only 1 value MAX will just present that value and dod not do any aggregation. Exactly...

First of all get rid of

ISNULL(B.IN_Opening_Stock,0) = 0

and all other such checks.

Replace it with (B.IN_Opening_Stock IS NULL OR B.IN_Opening_Stock = 0)

Looks more heavy but executes times faster.

Second.

You cannot...

Topic "Cast and Convert" in BOL must be really helpful for you.

Probably we need to see full query.

I used to get such kind of errors when there were too many derived tables or subqueries in my statement.

It was long time...

Who says 1?

4 tables including types and subtypes.

Tim, ready to bet on it?

What do you mean: "I see you got there first..."?

I tell you, it's not a matter of guessing if I can do it. I have...

Where this "(UserID=357053)" is dropped from?

It is in one table in my application.

And it works fine and it's very fast.

Do you read only first lines from help topics?

Read again and this time pay some attention to sentence about FORMATFILE.

Than go by the link "Using Format Files" and read closely,...

Bad design. From the beginning.

"Do not multiply entities needlessly." Heard about this?

Now you need either rethink your ideas aboun design or fix the error in UNION query.

select Name as ObjectName,...

You cannot order by value not mentioned in SELECT list.

There is no column "users.country" in your SELECT list, there is only "DISTINCT(users.country)".

Quote from BOL:

The ORDER BY clause can include items...

Yes.

Normalise your tables.

SELECT ID, 'Address1'

FROM Table

WHERE Address1 IS NULL

UNION

SELECT ID, 'Address2'

FROM Table

WHERE Address2 IS NULL

UNION

SELECT ID, 'City'

FROM Table

WHERE city IS NULL

UNION

SELECT ID, 'Country'

FROM Table

WHERE country IS NULL

ORDER BY ID