January 24, 2012 at 10:22 am
Hi Guys,
wanted to check if someone has seen this type of issue... and what was the reason behind it...
My colleague was running SQL with couple of AND
Ex:
SELECT * FROM PROJECT
WHERE PID = '3850' --CHAR(10)
AND INDID = 8565 --DECIMAL(10, 0)
AND NAME LIKE 'Contract%' --CHAR(100)
Somehow AND did not work correctly... when I ran the query in my computer it was fine.... This was very random.. She even showed me and when I changed the Order of PID and INDID in her computer it ran fine...
Not sure what might be the issue here as we want to make sure there is no issue in the DB..
Please let me know if you know anything on this..
Thanks,
Laura
January 24, 2012 at 10:27 am
What exactly was the issue? You really haven't explained anything here; kind of like saying my computer doesn't work and just leaving it at that 🙂
Can you please post sample data, ddl for the table, the faulty results, and the expected results.
Jared
CE - Microsoft
January 24, 2012 at 10:44 am
Sorry...
SELECT * FROM PROJECT
WHERE PID = '3850' --CHAR(10)
AND INDID = 8565 --DECIMAL(10, 0)
AND NAME LIKE 'Contract%' --CHAR(100)
The query above should have pulled Name starting with Contract for those PID and INDID, but it pulled all the records with those PID and INDID... I changed the order of PID and INDID the select worked fine; however, same issue was there for DELETE and UPDATE query... it is so incosistent... I thought the query was wrong first, but it did not seem like it...
January 24, 2012 at 11:18 am
Laura_SqlNovice (1/24/2012)
The query above should have pulled Name starting with Contract for those PID and INDID, but it pulled all the records with those PID and INDID... I changed the order of PID and INDID the select worked fine; however, same issue was there for DELETE and UPDATE query... it is so incosistent... I thought the query was wrong first, but it did not seem like it...
There is nothing syntactically wrong with the query you posted. The only thing I could consider is that it's continuing the 'comment' from the line before to the new line, which is just wierd, or that the name field which is a keyword is acting oddly (should be ...AND [NAME] like...). What SP/version is your co-worker on? Double check via select @@version, and check Help-About for the interface.
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 24, 2012 at 11:24 am
Comments I added for the forum...
This is the test server where we got the issue...
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Do we need to apply hotfix or something? We will be upgrading to 2008 sometime this year...
Even the Transaction did not work correctly while we were trying on her computer, but worked correctly when I did it using my computer....
Thanks,
Laura
January 24, 2012 at 11:49 am
Laura_SqlNovice (1/24/2012)
Comments I added for the forum...
Then we haven't seen the original, which makes troubleshooting things like this tougher.
This is the test server where we got the issue...
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
SP4 though. Now, you mention server, but that's a personal edition. I assume it's a desktop? If not, does the issue only arrive when she connects to that server, or any server that she connects to from her desktop? Need to nail down the offending component.
Do we need to apply hotfix or something?
Since one machine works correctly and another doesn't, hotfix doesn't seem appropriate here unless there's significant differences between the systems.
Even the Transaction did not work correctly while we were trying on her computer, but worked correctly when I did it using my computer....
Thanks,
Laura
Is it connecting to a SQL server on her actual desktop? Does she have similar problems connecting to a DB Engine on another machine? When you connect to the server on her desktop do you still not have any problems?
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 24, 2012 at 1:41 pm
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.
January 25, 2012 at 12:09 pm
I think we found the issue but do not know why it is happening...
The query had a line that was commented out with ('--') and very inconsistently the line after the commented line is also treated as comments...
We changed query many times and had that issue when there was a line that was commented.... the line after that was also treated as comments and the "where clause" was not recognized for a query that had a commented line before the WHERE Clause..
We took the commented line out from the query and the results were fine... Did anyone have the same issue.. I think it is her computer coz I did not have that issue in my computer...
Thanks,
Laura
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.
January 25, 2012 at 12:17 pm
Different text editors use different line terminators so it is possible that a different machine was reading whatever combination of CHAR(13) and CHAR(10) was being used to terminate the line differently that what the screen was showing. One way to ensure that only what you want commented is commented is to use the block comment characters:
SELECT * FROM PROJECT
WHERE PID = '3850' /* CHAR(10) */
AND INDID = 8565 /* DECIMAL(10, 0) */
AND NAME LIKE 'Contract%' /* CHAR(100) */
That way, no matter how the line is wrapped, the comments will be properly terminated and cannot accidently comment another section.
January 25, 2012 at 12:18 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.
Where is this query being executed from; i.e. a text file, SSMS, 3rd party query editor?
Jared
CE - Microsoft
January 25, 2012 at 12:35 pm
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.
January 25, 2012 at 12:51 pm
The database is actually sql 2000 but using SQL 2008 R2 visual studio in the laptop.
January 25, 2012 at 12:53 pm
Laura_SqlNovice (1/25/2012)
The database is actually sql 2000 but using SQL 2008 R2 visual studio in the laptop.
So, you are running this query in which part of Visual Studio? Is this an asp.net solution? Is this a C# program? SSIS?
Jared
CE - Microsoft
January 25, 2012 at 12:56 pm
in query analyzer not in any application...
January 25, 2012 at 12:59 pm
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?
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply