[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

invalid column name when using ".exist"

hi.

i am trying to issued this sql statement:

SELECT    AAA.Table1.*,  
                AAA.Table2.id  as myId,
FROM      AAA.Table1, AAA.Table2
WHERE   AAA.Table1.id = AAA.Table2.id
               and
              ( myColumnForXML.exist('field[value="cat"]') = 1 )
order by AAA.Table1.id

-  if i try to execute that sql stmt it gives me a "AAA column name not found" error.
- and the other weird thing is when i hit the "execute" icon , it does some pre formats on my sql statement (see below):

//notice tha the * is gone and was replaced to all the fields i have in Table1
//notice also that it added the "CROSS JOIN"
SELECT    AAA.Table1.id,  AAA.Table1.name, AAA.Table1.add, AAA.Table1.email, AAA.Table1.others
                AAA.Table2.id  as myId,
FROM      AAA.Table1  CROSS JOIN AAA.Table2
WHERE   AAA.Table1.id = AAA.Table2.id
               and
              ( myColumnForXML.exist('field[value="cat"]') = 1 )
order by AAA.Table1.id

i tried puting extra parenthesis around my exist like this:
( ( (myColumnForXML).exist('field[value="cat"]') = 1 )  )'
 because my colleague had the same problem before and that did the trick.. but mine just deletes the 2 sets of parenthesis i added and im still getting the "column name not found" error.

is this an SQL Server-version related? because we tried running it to my other colleague's sql server and it's working ok.... im using SQL Server Management Studio 2005 on windows 2000....

thanks in advance!

0
ayeen
Asked:
ayeen
  • 6
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
run @@version and check out:
support.microsoft.com/kb/321185

if that returns sql 2005 positively, the other issue might be the database compatibility being set to 80 (sq 2000), hence ignoring the new sql 2005 xml data type and it's implicit functions (like exist)
0
 
ayeenAuthor Commented:
actually i can run the ".exist" just fine if im just using just one table ...
0
 
ayeenAuthor Commented:
but if i started using two tables that's when the "invalid column name" appears
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the service pack on the server?
did you apply the same service pack on your client workstation?
0
 
ayeenAuthor Commented:
is it the service pack for the OS or the sql server itself?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for sql server
0
 
ayeenAuthor Commented:
the service pack for both the server and my client workstation is the same....

i tried running my sql using an application i did and it's running ok but if i'll try running my sql statement using SQL Server Management Studio 2005 on windows 2000 that's when i get errors and it's like it's pre-formatting my sql statements
0
 
ayeenAuthor Commented:
hi moderator...

i still haven't solved my problem and i'm abandoning this open question...
but..

can i refund my points? can i get back the 40 pts and you give 30pts to angelIII ....i'd like to give her points for the effort .....

thanks!
0
 
ayeenAuthor Commented:
cs97jjm3: please see my previous post..

the one that says:

hi moderator...

i still haven't solved my problem and i'm abandoning this open question...
but..

can i refund my points? can i get back the 40 pts and you give 30pts to angelIII ....i'd like to give her points for the effort .....
0
 
Computer101Commented:
PAQed with points refunded (70)

Computer101
EE Admin
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now