Can one turn visual studio's 'smart' sql query assistant off?

Question

When creating this query in the SQL querybuilder window in visual studio (2008):

 UPDATE       outgoing_messages
 SET                readstatus = 5
 FROM            outgoing_messages INNER JOIN
                     connections ON outgoing_messages.connectionid = connections.connectionid
 WHERE        (outgoing_messages.msgreference = '103') AND (connections.providerid = 9)

Visual studio knows better and criples it by turning it into:

 UPDATE       outgoing_messages
 SET                readstatus = 5
 FROM            outgoing_messages AS outgoing_messages_1 INNER JOIN
                     connections ON outgoing_messages_1.connectionid = connections.connectionid CROSS JOIN
                     outgoing_messages
 WHERE        (outgoing_messages_1.msgreference = '103') AND (connections.providerid = 9)

Which instead of singling out that 1 record with the specific msgreference and connectionid, it updates tons of records.

Now the crazy part is: when using the visual query builder and I drag and drop the query, it results in the exact same query, but now visual studio doesn't mess with it and executes it and all is fine.

If I copy and paste it again into a new querywindow, all is crippled again.

Is there a workaround for this 'smart' query crippler? (Turn it off for instance?)

Thanks!

EDIT: p.s. this has been posted as a bug on Microsoft. Please start voting for it ;^)

here is the link to microsoft

Answer 1

Do you get the same problem if you alias the table in the UPDATE clause rather than using the explicit name, i.e. this instead:

UPDATE       om
SET                readstatus = 5
FROM            outgoing_messages om INNER JOIN
              connections c ON om.connectionid = c.connectionid
WHERE        (om.msgreference = '103') AND (c.providerid = 9)

Answer 2

Microsoft said they'll add the option to turn smart sql re-ordering off in the upcoming VisualStudio (2010)