how do I access an error cause inside an exec statement

Hello:
how do I access an error code when
SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
fails because the database..table does not exists ?
@.RESULTS comes back with nothing but
I get
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'Internet_Forms.test'. Check
sysobjects.
(Problem since @.NAME is populated from sysobjects.....)
Thanks
TThe batch is aborted after encountering this specific error, so you cannot
followup and catch the error. One thing you could do is check if the table
exists like so:
if object_id('mytable') is not null
begin
<o.k. do something>
end
else
begin
<table does not exist>
end
The following is a good tutorial on the subject:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
"Support" <RemoveThis_Support@.mail.oci.state.ga.us> wrote in message
news:%23VCo9XZoFHA.1468@.TK2MSFTNGP12.phx.gbl...
> Hello:
> how do I access an error code when
> SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
> fails because the database..table does not exists ?
> @.RESULTS comes back with nothing but
> I get
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'Internet_Forms.test'. Check
> sysobjects.
> (Problem since @.NAME is populated from sysobjects.....)
> Thanks
> T
>|||begin transaction
run querry
queryy @.@.error
commit when no error
rollback if erro
"Support" wrote:

> Hello:
> how do I access an error code when
> SELECT @.RESULTS EXEC('DBCC DBREINDEX('''+@.NAME+''') ')
> fails because the database..table does not exists ?
> @.RESULTS comes back with nothing but
> I get
> Server: Msg 2501, Level 16, State 1, Line 1
> Could not find a table or object named 'Internet_Forms.test'. Check
> sysobjects.
> (Problem since @.NAME is populated from sysobjects.....)
> Thanks
> T
>
>|||@.@.error returns noting becuase of JT's reason...
That's the problem
Thanks
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:B6A8654B-67F3-47F0-A93E-24D7E77F02F9@.microsoft.com...
> begin transaction
> run querry
> queryy @.@.error
> commit when no error
> rollback if erro
>
>
> "Support" wrote:
>

How do i "Join" back to the same table and fields ? Please help !

Hi and thanx for reading my post..

Underneat is the current SQL i'm trying to get to work (in Access 2000)

Cant get it to work, cause i'm getting Join errors..

----CODE-----
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname" & _
" FROM Person p, Person p1" & _
" INNER JOIN (PersonPerson pp ON pp.PID = p.PID OR pp.PID2 = p.PID)" & _
" WHERE (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
----END CODE----

----DESCRIPTION-------
1) Got a table "Person" where i'm inserting PID and Lastname.
2) Registering person 1-> PID = 1, Lastname = Doe
3) Registering person 2-> PID = 2, Lastname = John
4) Using table PersonPerson (manually) to connect these two together.
PID1 = 1, PID2 = 2
5) By using this i can use SQL to get the data from both persons taht are
connected
6) So basically, i want to go through all records in the personperson table, to find PID and PID2, and then use _both_ the fields to check against the Person-table. First getting Person.PID and Person.Lastname (For PERSON 1) THEN... do the exact same operation for PID2.

----END DESCRIPTION------

Result should look like this :

Person1 ID, Person1 Lastname, Person2 ID, Person2 Lastname
------------------
1 Doe 2 John

Will be enormously happy if anyone could help me out with this code...

/(_Mirador:(I'm not 100% on what you want. Is this your table structure?

Person
PID Firstname etc
-- ---
1 Joel
2 Bell
3 Bruce
4 Harry

PersonPerson
PID1 PID2
-- --
1 2
4 3

If so - this should help:

strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname
FROM Person p, Person p1, PersonPerson pp
WHERE (p.PID = pp.PID1 AND p1.PID = pp.PID2)
AND (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"

It will generate the following output:

PID Lastname PID Lastname
---- ---- ---- ----
1 Dixon 2 Crawford
4 Potter 3 Shark

(Yes, Person 4's name is Harry Potter - but no - I haven't read the books :) )|||Great ! :)

I will try the code right away..

I'll send you the results i got :)

Mirador.|||Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)|||Super-great !!!!! :)

It worked like a dream..

You have no clue how much you've helped me !

Owe u a digital-beer.

Mirador.|||lol - no worries mate.

But I've gotta drive tonight - so how about a digital soda?|||/Me hands over a digital master-soda :)

hehe..

btw : since you're so master'ish at SQL.. maybe u could try and help me with this query too ?

----CODE-----
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _

" WHERE Person.Etternavn LIKE '%" & Me.txtSearch & "%' OR Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
" OR Person.Alias LIKE '%" & Me.txtSearch & "%' OR Person.Alias LIKE '%" & Me.txtSearch2 & "%' OR Person.Yrke LIKE '%" & Me.txtSearch & "%' OR Person.Yrke LIKE '%" & Me.txtSearch2 & "%'" & _ etc.etc.etc...
---- END CODE -----

---- DESCRIPTION-------
As you can see it's 2 x fields where i want to search..
Basically... i'm running 2 different queries depending on (and checking) if only one, or two fields are filled in for the search... AND...

If both the fields are filled in, it should ex : if Me.txtsearch is "June" and Me.txtSearch2 is "2004" make sure it's only getting a record if both "June" and "2004" is included in _ANY_ of the fields in the record..

I have realized that i cannot only use AND alone, because then all the fields have to match, and i cannot use OR alone either.. because then it grabs if it only matches one of them..

Got any clues on this one ?

Thanx for your help mate..

Mirador.|||So basically - you are trying to select records in which BOTH of the criteria is in ONE of the fields. If this is correct (and if it's not I'm lost :) ) - then this should help (take note of the brackets):

strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.PID" & _

" WHERE (Person.Etternavn LIKE '%" & Me.txtSearch & "%' AND Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
") OR (Person.Alias LIKE '%" & Me.txtSearch & "%' AND Person.Alias LIKE '%" & Me.txtSearch2 & "%') OR Person.Yrke LIKE '%" & Me.txtSearch & "%' AND Person.Yrke LIKE '%" & Me.txtSearch2 & "%')" & _ etc.etc.etc...

Basically you are saying (i'll use a generic example of a Product record. No offence - but I really can't understand the table you have with the column names ;) ):

If txtSearch1 is "big" and txtSearch2 is "round"

SELECT blah
FROM Product
WHERE (Product.Name LIKE '%big%' AND Product.Name LIKE '%round%')
OR (Product.Description LIKE '%big%' AND Product.Description LIKE '%round%')
OR (Product.Comment LIKE '%big%' AND Product.Comment LIKE '%round%')

So you will get products with big AND round in either the Name, Description or Comment column.

Is this what you were after - or have I just spoken pure gibberish?|||Hay mate :) thanx for your reply :)

did u enjoy your digital soda ? hehe.. :)

Well... gotta admit that it's a bit confusion for myself too :)

I'll try to explain a bit better :

---------
Q :
Lets say u want to search for every record that includes both "Green" and "Yellow" in _any_ of the fields in the record, and u want to see _only_ those records.

A:
You find a record where field "lastname" has "Green" in it and field "Comment" has "Yellow" in it -> MATCH!!
----------

If i understood it right, the one you posted has to have both "Green" and "Yellow" in one field right ?
So.. u would get a match if ex. "Comment" field had the text :
"All green people are infact Yellow because they bla-bla.-bla..."

I have to lets say.. use the second searchfield (txtsearch2) to... "narrow" down the search.

Example :
-------
Lets say u want to find a person named "Mike" and u press search. U would maybe get something like 1000 matches if u got a huuge database.
But then u altso know that "Mike" is from "Uganda". So... therefore i type "Mike" in txtsearch and "Uganda" in txtsearch2 to make sure that u get all the "Mike" records which got "Uganda" in any of the other fields..
-------

Yea.. that's about it :) dunno if it's even possible but i surely hope so : )heeh..

Hope that made things a littlebit more clear :)

Thanx for your help btw !!!.. most appreciated..

want another digital soda ? or.. maybe a digital beer this time :)

Best regards
Mirador.|||Ahh - I see what you mean. It's also possible - but the code is quite long, depending on the columns in your table. It's basically the same as my other example above - but switch the ANDs and ORs.

For each search criteria you have to check if it's in any of the columns. Again - using my Product table:

SELECT blah
FROM Product
WHERE (Product.Name LIKE <SearchCriteria1> OR Product.Description LIKE <SearchCriteria1> OR Product.Comment LIKE <SearchCriteria1>)
AND (Product.Name LIKE <SearchCriteria2> OR Product.Description LIKE <SearchCriteria2> OR Product.Comment LIKE <SearchCriteria2>)

Doing this for each column should get what you're after. Basically you're saying SearchCriteria1 needs to be in any of the columns (using the OR) - AND SearchCriteria2 needs to be in any of the columns.

Is that what you're after?

And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :D|||Tjohooo !!..

yea.. that's JUST what i was after..

I had thoughts in this track, but wasn't certain because it would be so extremely long:) hehe.. Didn't know quite how to write it either..

but.. THANK YOU AGAIN !! :)

Double scotch coming up.. or.. maybe it's back to coffee ? heheh !!:

Mirador..|||And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch! :DThese last few weeks (since mid-April) have been awful for me too. Do you suppose that the universe has taken some kind of unusually perverse twist against us "denizens of databases" lately?

-PatP