SQLTeam.com | Weblogs | Forums

Incorrect syntax


#1

It has obviously been a long day for me, the code below is failing to run and I have no idea why.
Incorrect syntax near ')'

SELECT
UID as Whenfresh_URN,
MP_URN as CallCredit_URN,
Title,
FirstName,
LastName,
Address_Line1,
Address_Line2,
County,
Town,
Postcode
INTO UserListTransfer..ContactCentre33_PO0188_MatchedData -- SELECT COUNT(*)
FROM (
SELECT UID,
MP_URN,
Title,
Forename,
Surname,
Address1,
Address2,
County,
Town,
Postcode
FROM UserListTransfer..ContactCentre33_CallCredit_PO0188 WITH(NOLOCK)
LEFT JOIN UserListTransfer..Whenfresh_20160116 WITH(NOLOCK)
ON POSTCODE = Postcode
AND FAMILY_NAME = Surname

WHERE
UserListTransfer..Whenfresh_20160116.POSTCODE is not null
and UserListTransfer..Whenfresh_20160116.FAMILY_NAME is not null

)


#2

you need an alias name after the closing parentheses.

BTW, why are you using NOLOCK? Is it OK if your query misses rows, returns deleted or pre-updated rows or duplicate rows? All these and more can happen with NOLOCK


#3

Why are you even using a derived table? It isn't needed and just makes the query longer and more complex than it needs to be.


#4

+1 indeed I think


#5

I now have an ALERT on ERROR 601 ("Data movement during NOLOCK") on our servers (with 3rd party APPs I hasten to add! no sticking NOLOCK in any of our code ... :slightly_smiling: )

I've had 4 alerts in the last two days (and I would regards usage on those databases as "tiny" as very few connected users making only intermittent access.

My presumption is that ERROR 601 is only triggered a small percentage of the time (i.e. NOLOCK would otherwise cause either data to be select twice, or not at all, without raising ANY error - for which there is NO means [that I know of] of detecting / collecting statistics :frowning: ) so I fear that the users are seeing incorrect data "frequently". Let's hope they are not committing the company on a bum-steer ... :frowning:

I can't see that there is any good reason whatsoever, in this day and age, for an end user APP to have any NOLOCK code present. Its taking ages to persuade our 3rd party suppliers to do something about it though ...