Re: A Bug about SQL Query Analysis(SQL 2000 AND SQL Server 2005)

My System Environment:
WIN2000 SP4
SQL Server Version: SQL 2000 Standard Edition [8.00.194(RTM)]
You could follow my way to know the bug.
1.In TempDB DataBase,

CREATE TABLE Lorry1(
ID INT NOT NULL,
cInvcode NVarchar(20) NULL,
cCode NVarchar(20)NULL
)

2.To Insert Something to Lorry1 Table

INSERT INTO Lorry1(ID,cInvCode,cCode)
VALUES(1,N'aaa',N'1')
INSERT INTO Lorry1(ID,cInvCode,cCode)
VALUES(2,N'bbb',N'2')
INSERT INTO Lorry1(ID,cInvCode,cCode)
VALUES(3,N'ccc',N'3')

3.In Master DataBase,
CREATE TABLE Lorry(
ID INT NOT NULL,
cInvcode NVarchar(20) NULL,
)

4.To Insert Something to Lorry Table
INSERT INTO Lorry(ID,cInvCode)
VALUES(1,N'aaa')
INSERT INTO Lorry(ID,cInvCode)
VALUES(5,N'bbb')
INSERT INTO Lorry(ID,cInvCode)
VALUES(6,N'ccc')

5.Go to Master Database, and to execute the following sentence:
SELECT
Lorry.id
cinvcode
FROM TempDB..[Lorry1] AS TmpTotal
LEFT JOIN Lorry ON TmpTotal.id=Lorry.id

plus: you must obey the formation of the sentence,and we can find 2 bugs:
1. Though I didn't write "," between "Lorry.id" and "cinvcode", the Query
Analysis says the sentence is right.
2. the Column "cinvcode" belongs two tables and in this sentence i didn't
write the table name before the column, and the Query Analysis also told the
sentence didn't have any error.

I test it in many computers, and i'm sure the SQL Server 2005 has the same
error too. Please tell me how to resolve it. Thanks.


Lorry

2006-07-04

------------------------------------------------------------------------------------------------


"David Portas" wrote:

Lorry Astra wrote:
I found a bug about SQL Query Analysis, if u write a sql statement like this:

SELECT Tmptable.col1
table.col2
FROM TEMPDB..[Table] AS Tmptable
LEFT JOIN table ON Tmptable.ID=table.ID

we should know, this sentence is wrong, 'cos it seems like something wrong
between "Tmptable.col1" and "table.col2", the right formation is "
Tmptable.col1 , table.col2"

but SQL Query Analysis says the sentence that i feel wrong is just right, it
can show a query result based on it. I think it should be a bug.

Plus:
the right sentence should be like this:

SELECT Tmptable.col1 ,
table.col2
FROM TEMPDB..[Table] AS Tmptable
LEFT JOIN table ON Tmptable.ID=table.ID



You haven't told us what version, edition and service pack you are
using. In SQL Server 2000 SP4 and SQL Server 2005 SP1 (both Dev
edition) your first query gives a syntax error:

Server: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

(the only modification I made was to bracket the name "table" because
it's a reserved word).


Could any one tell me why? and how to resolve it by patching. Thanks.

http://www.microsoft.com/sql/sp1.mspx
http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--


.

Relevant Pages

  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Word 2003/Access2000/SQLSVR
    ... SQL server being where the data is held and this is accessed through a MS ... entire database and that may be say a record for Berkshire. ... "Peter Jamieson" wrote: ... replaced with the first record on the table in use. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MS Access DAO -> ADO.NET Migration
    ... full SQL Server and I see the logic you explained in a multi user ... allow two users to access the same database file Read/Write at any given ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MS Access DAO -> ADO.NET Migration
    ... For that it is much harder to handle the incremental identifier, ... database but although they have the data, they are not connected at the same ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... SQL Server Management Studio is nowhere to be found on my ...
    (microsoft.public.dotnet.framework.adonet)