469,088 Members | 1,272 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

Ignore Error in SP

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.
Jul 20 '05 #1
2 3196

"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
Jul 20 '05 #2
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?
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Thomas Bartkus | last post: by
5 posts views Thread by Chris Mantoulidis | last post: by
1 post views Thread by windandwaves | last post: by
23 posts views Thread by FrancisC | last post: by
4 posts views Thread by Jackson Miller | last post: by
5 posts views Thread by ma740988 | last post: by
6 posts views Thread by sandy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.