January 25, 2012 at 1:07 pm
Thank you.. Sorry Sql Server Management Studio... One query that had issue was:
UPDATE PEOPLE_PROJECT
SET END_DT = NULL
--SELECT * FROM PEOPLE_PROJECT
WHERE PID = 56408
AND END_DT >= '2011-10-12'
It updated all the rows to NULL when I took out the commented line and updated again it was fine...
The query was typed in query analyzer and not copied...
SQLKnowItAll (1/25/2012)
I see... So the query was being run in SQL Server Management Studio, not Visual Studio? Was the query typed in or was it copied and pasted? Do you have a screenshot maybe of the machine that is returning the incorrect results?
January 25, 2012 at 1:11 pm
Can you please post the DDL for the PEOPLE_PROJECT table?
Jared
CE - Microsoft
January 25, 2012 at 1:16 pm
CREATE TABLE [dbo].[PEOPLE_PROJECT](
[BUSINESS_UNIT] [char](5) NOT NULL,
[PROJECT_NAME] [char](50) NOT NULL,
[PID] [decimal](10, 0) NOT NULL,
[COMPANY_ID] [char](10) NOT NULL,
[START_DT] [DATETIME] NULL,
[END_DT] [DATETIME] NULL
) ON [PRIMARY]
SQLKnowItAll (1/25/2012)
Can you please post the DDL for the PEOPLE_PROJECT table?
January 25, 2012 at 1:25 pm
The only rationale that I can come up with is that you had highlighted everything up to, but not including, the WHERE clause and then executed the query. When you did it the second time, you did not have anything highlighted and so it ran the entire query. Not to demean your knowledge, because I don't know how experienced you are, but are you aware that you can highlight sections of script to be run in isolation from the rest of the query editor? I know this has happened to me when I have been a bit careless...
Jared
CE - Microsoft
January 25, 2012 at 1:35 pm
Thanks a lot for the help... I find it so weird too and I am sure I ran the query correctly as we tried several times and same query acts up fine when I run in another desktop. It also runs fine when I remote into the server and run it from there... anyway the issue is with the laptop or the SQL Server installed in that.....
SQLKnowItAll (1/25/2012)
The only rationale that I can come up with is that you had highlighted everything up to, but not including, the WHERE clause and then executed the query. When you did it the second time, you did not have anything highlighted and so it ran the entire query. Not to demean your knowledge, because I don't know how experienced you are, but are you aware that you can highlight sections of script to be run in isolation from the rest of the query editor? I know this has happened to me when I have been a bit careless...
January 25, 2012 at 1:43 pm
fahey.jonathan (1/25/2012)
SQLKnowItAll (1/25/2012)
Where is this query being executed from; i.e. a text file, SSMS, 3rd party query editor?As far as I recall, the code was copied from a hosted SVN service and pasted into SSMS. When the code was pasted into Notepad, there were two or three unprintable characters in random places at the end of some, but not all, lines. The characters were represented by squares that were visible in Notepad but not in SSMS. When I removed the characters and repasted to SSMS, the code worked correctly.
This sounds like a lot of the problem. Those hidden characters could be overriding CRLFs and mixing where components together, etc etc.
In the case of the 'commented' component, it could have ended up bringing the entire where clause onto the same line as far as the compiler was concerned, thus commenting everything after it out.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2012 at 2:07 pm
Yes and after I removed the commented line there WHERE clause worked fine....
Evil Kraig F (1/25/2012)
fahey.jonathan (1/25/2012)
SQLKnowItAll (1/25/2012)
Where is this query being executed from; i.e. a text file, SSMS, 3rd party query editor?As far as I recall, the code was copied from a hosted SVN service and pasted into SSMS. When the code was pasted into Notepad, there were two or three unprintable characters in random places at the end of some, but not all, lines. The characters were represented by squares that were visible in Notepad but not in SSMS. When I removed the characters and repasted to SSMS, the code worked correctly.
This sounds like a lot of the problem. Those hidden characters could be overriding CRLFs and mixing where components together, etc etc.
In the case of the 'commented' component, it could have ended up bringing the entire where clause onto the same line as far as the compiler was concerned, thus commenting everything after it out.
January 25, 2012 at 9:11 pm
Some systems terminate lines with CHAR(0), the NULL character... and that makes a hell of a mess in SSMS.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2012 at 3:22 pm
fahey.jonathan (1/24/2012)
This may be unrelated, but I had a problem once with a query that acted very strangely. Long story short, there were unprintable characters in the texgt that were not appearing on the screen but did affect how the compiler viewed the query. You might check that this is not the case with your query by retyping it from scratch.
I've had the same problem several times.
To see the placeholders for unprintable characters, copy and paste the query and all its comments into the Notepad program. The unprintable characters are immediately visible as squares.
LC
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply