Ignore Error in SP

[Trev]

Is there a way to make a SP ignore an error?

e.g. I'm looping through each database on a server, checking of a table
exists then selecting a value from that table. Now I have a database put
onto the server where the table exists but all column names are
different, my SP is not interested in this database so when it errors
with invalid column name I want it to move onto the next databse and not
display any error message.

[Simon Hayes]

"Trev@Work" <no.email@please> wrote in message
news:41***********************@news.easynet.co.uk. ..

Is there a way to make a SP ignore an error?

e.g. I'm looping through each database on a server, checking of a table
exists then selecting a value from that table. Now I have a database put
onto the server where the table exists but all column names are different,
my SP is not interested in this database so when it errors with invalid
column name I want it to move onto the next databse and not display any
error message.

No - you can't trap errors in MSSQL, you must pass them to the client:

http://www.sommarskog.se/error-handling-II.html

Without more information on what you're trying to do, it's hard to say what
the best solution is, but one option is to test if the column you're
interested in exists:

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '???'
AND COLUMN_NAME = '???'
)
BEGIN
-- DO SOMETHING WITH THE TABLE
END
ELSE
BEGIN
-- MOVE TO NEXT DATABASE
END

Simon

[Trev]

Simon Hayes wrote:

"Trev@Work" <no.email@please> wrote in message
news:41***********************@news.easynet.co.uk. ..

Is there a way to make a SP ignore an error?

e.g. I'm looping through each database on a server, checking of a table
exists then selecting a value from that table. Now I have a database put
onto the server where the table exists but all column names are different,
my SP is not interested in this database so when it errors with invalid
column name I want it to move onto the next databse and not display any
error message.

No - you can't trap errors in MSSQL, you must pass them to the client:

http://www.sommarskog.se/error-handling-II.html

Without more information on what you're trying to do, it's hard to say what
the best solution is, but one option is to test if the column you're
interested in exists:

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '???'
AND COLUMN_NAME = '???'
)
BEGIN
-- DO SOMETHING WITH THE TABLE
END
ELSE
BEGIN
-- MOVE TO NEXT DATABASE
END

I'm actually identifying the database as belonging to a particular
application, we have a simple profiles table that will have a signature,
e.g.

select [value] from zstblProfile where [key] = 'AppSignature'

This is so I can loop all databases and display a list of databases to
the front end's login screen (only dbs applicable to the FE app).

The exist code above will help, thanks.

Unless there's an easier way to stamp a database?