Error handling...

I am needing to check for an error code after a SQL statement has run, but
the error is being trapped internal to the Select statement. How do I make
this work?
I am trying to set up some code that will try the SQL statement and if it
gets an error, it will wait 30 seconds and then try again. And if it trys 3
times (or so) with out success THEN return the error message (in the error
dialog box that halts everything till you click "OK") or better yet, just
log that the error occured and allow the process to continue and take
appropriate action based on the error number.
The file pointed to by the OPENROWSET, was just copied from a local PC to
the server. The problem is that the file still has write processes occuring
in the background after the scripted copy thinks it is finished and passes
control to the next line.
I am trying to make this work with out resorting to DTS or SSIS. The
product I am using has it's own proprietary scripting set up that can call i
n
and run Transact-SQL statements.
*** SQL Code ***
Declare @.trys int
Set @.trys = 1
PRINT 'At top'
While @.trys < 3
BEGIN
Print 'Attempting Try # ' + Cast(@.Trys as char(4))
SELECT SN.CustomerID, C.CustomerID, C.FirstName, C.LastName, C.Status
FROM Customers AS C
LEFT OUTER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\StudentList.mdb';'admin';'', StudentNumbers) AS SN
ON (C.CustomerID = SN.CustomerID)
WHERE SN.CustomerID Is not null
If @.@.ERROR = 0
BREAK
Else
BEGIN
WAITFOR DELAY '000:00:30'
Set @.Trys = @.Trys + 1
CONTINUE
END
END
Print 'At bottom'
Regards,
JohnJohn Keith (JohnKeith@.discussions.microsoft.com) writes:
> I am needing to check for an error code after a SQL statement has run,
> but the error is being trapped internal to the Select statement. How do
> I make this work?
It may prove difficult if you are on SQL 2000. On SQL 2000 many errors
are not trappable in SQL, because they abort the batch. There is a trick
with setting up a linked server, but it's fairly kludgy.
On SQL 2005 you can use TRY CATCH.
For lots of details in error handlin in SQL Server, see this
article: http://www.sommarskog.se/error-handling-I.html. The linked-
server trick is described somewhere in the middle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for that link.
Your page was very informative on the hows-and-whys. I can tell from your
sarcasm on the page that we are in the same boat on this. The remark about
"Now, taste that concept: an informational error." made me laugh.
I'm checking into SQL Server 2005 to see if my package-application (not DTS,
just an externaly written bought app) will support the newer SQL Server 2005
.
Regards,
John
"Erland Sommarskog" wrote:

> John Keith (JohnKeith@.discussions.microsoft.com) writes:
> It may prove difficult if you are on SQL 2000. On SQL 2000 many errors
> are not trappable in SQL, because they abort the batch. There is a trick
> with setting up a linked server, but it's fairly kludgy.
> On SQL 2005 you can use TRY CATCH.
> For lots of details in error handlin in SQL Server, see this
> article: http://www.sommarskog.se/error-handling-I.html. The linked-
> server trick is described somewhere in the middle.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>