A SQL2005 query worked before but not now.

Question

A SQL2005 query worked before but not now.

Kay_Lee 101

select name, EDI_code, perdose, perday, howlongday, count(*) AS frequencies from prescriptions_pharmacy group by name, EDI_code, perdose, perday, howlongday

When I execute above query in Server Management Studio Express, Incorrect syntax near '' error is shown. between '', there is nothing.

I'm confused the SQL Server was 2005 or 2014. At this time, I have to use SQL2005 Server.

Please kindly advise me.

Thank you !

Edited-

2 Screenshots are attached, And the Korean language '' 근처의 구문이 잘못되었습니다 is incorrect syntax near ''

According to my records, this query succeeded in March, 2023 for the last time.

The Sql Server Management Studio Express version was 2005, and I change it to 2014 but as you might expect well, change the version of Management Studio didn't solve the problem.

In case of normal situation, normal syntax, of course I try my best to find the answer on my own, but for this case, it seems really strange and so I ask here.

sql query_strange error_1

sql query_strange error_2

Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2024-09-27T21:07:11.4433333+00:00

Can you post a screenshot of the situation so that we can understand better?
Kay_Lee 101 Reputation points

2024-09-30T10:06:50.0066667+00:00

@Erland Sommarskog hello, I edited(posted 2 screenshots) but maybe, some co-ordinator of Microsoft has to approve the edit to be seen. Please kindly wait and thank you !

4 answers

Your answer

Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2024-09-27T21:07:11.4433333+00:00

Can you post a screenshot of the situation so that we can understand better?
Kay_Lee 101 Reputation points

2024-09-30T10:06:50.0066667+00:00

@Erland Sommarskog hello, I edited(posted 2 screenshots) but maybe, some co-ordinator of Microsoft has to approve the edit to be seen. Please kindly wait and thank you !

Answer 1

Olaf Helper 47,586

I'm confused the SQL Server was 2005 or 2014

Very different version numbers, but both have one thing in common: Out-of-support!

There is no syntax failure in your SQL, there must be something else in your Query Window.

Answer 2

LiHongMSFT-4306 31,616

Hi @Kay_Lee

It seems to me that there is no syntax error.

Try to select the whole query with the mouse and then click the Execute button.

Or open another query widow and try again?

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Erland Sommarskog 129.6K MVP Volunteer Moderator

The reason is like to be due to that one of the spaces in the string is a hard space, Unicode character 160. Up to SQL 2008, SQL Server did not recognise the hard space as a white-space character, and would yield a syntax error if it encountered one.

Try deleting all spaces and re-inserting them.

Kay_Lee 101 Reputation points

2024-10-01T08:11:40.1066667+00:00

Above all, I appreciate your excellent insight. I have some habit like ", " (a empty space after comma ,). So, I felt your idea might be positive but failed. Please refer to the self-answer below. To find solution, I just tried a basic way (minimum structure) like 'select xxx from table' and it worked.
Viorel 125.8K Reputation points

2024-10-01T08:43:48.4933333+00:00

The problem can be caused (and reproduced) by other special characters, such as directional marks or zero-width breaks. Such symbols are not visible and are difficult to remove.
Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator

2024-10-01T09:20:15.7366667+00:00

Yeah, one way to find out is to save the text a file and open it an editor that has a hex-viewer capability. I actually did that with the query text in your original post, but nothing stood out. But with copy-paste across multiple environments, things might change under the covers.

Answer 4

Kay_Lee 101

I think this is some bug(?) of SQL Server 2005 (20 years old..)

I tried a basic way like 'select xxx from table' (the minimum structure) and it worked fine.

And I added the rest branches like where, group by, having and it also worked.

Share via

A SQL2005 query worked before but not now.

4 answers

Your answer