March 14, 2019 at 1:36 pm
I need to schedule a procedure to copy a large number of tables from AS400 via ODBC
That part is fine, however my problem is this. When the job runs, some of the tables may be unavailable. It's important that the procedure not stop, but to skip the missing tables and continue .
How do I make sure that will happen?
...
-- FORTRAN manual for Xerox Computers --
March 14, 2019 at 1:42 pm
Use SSIS, and have separate items for each table, so that you can control each one individually, and set each one to NOT fail the package on errors. You may not know the difference between a missing table and a genuine problem, however, so caveat emptor.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2019 at 6:33 am
There are about 100 tables in a list, I am using Excel to create the SQL line by line code from the list of tables. If the list changes it's a straightforward move to edit the list in Excel and roll out a new script.
I'm only familiar with the graphic SSIS editor, not sure how I would do this without creating objects for each table.
GO statements after each does the job, but cannot be included in a procedure.. I need to schedule this
What I'm looking for is something like the old Basic "on error resume next" directive.
...
-- FORTRAN manual for Xerox Computers --
March 15, 2019 at 7:12 am
jay-h - Thursday, March 14, 2019 1:36 PMI need to schedule a procedure to copy a large number of tables from AS400 via ODBCThat part is fine, however my problem is this. When the job runs, some of the tables may be unavailable. It's important that the procedure not stop, but to skip the missing tables and continue .
How do I make sure that will happen?
You could use a pattern such as this ...BEGIN TRY
-- IMPORT TABLE 1 HERE
END TRY
BEGIN CATCH
-- Log the fact that Table 1 was not available
END CATCH;
BEGIN TRY
-- IMPORT TABLE 2 HERE
END TRY
BEGIN CATCH
-- Log the fact that Table 2 was not available
END CATCH;
BEGIN TRY
-- IMPORT TABLE n HERE
END TRY
BEGIN CATCH
-- Log the fact that Table n was not available
END CATCH;
March 15, 2019 at 8:00 am
jay-h - Friday, March 15, 2019 6:33 AMThere are about 100 tables in a list, I am using Excel to create the SQL line by line code from the list of tables. If the list changes it's a straightforward move to edit the list in Excel and roll out a new script.I'm only familiar with the graphic SSIS editor, not sure how I would do this without creating objects for each table.
GO statements after each does the job, but cannot be included in a procedure.. I need to schedule thisWhat I'm looking for is something like the old Basic "on error resume next" directive.
Stop using Excel, put the list in a table somewhere, use dynamic SQL to roll through all of the table values.
Use DesNorton's suggestion to wrap in a Try/Catch, and log the error somewhere if it fails, then move on to the next row in the table. (but only have to write one try/catch because you're using dynamic SQL)
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 15, 2019 at 8:04 am
The TRY/CATCH seemed promising, however apparently the process checks the ODBC connections before executing anything and completely aborts if any connections don't work.
too bad, looked like a good idea
...
-- FORTRAN manual for Xerox Computers --
March 15, 2019 at 8:10 am
ok, so how about this, why are the tables unavailable? Can you do something to make sure they are all available as a first step, or to identify which are available and which are not before you run it?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
March 15, 2019 at 8:22 am
jonathan.crawford - Friday, March 15, 2019 8:10 AMok, so how about this, why are the tables unavailable? Can you do something to make sure they are all available as a first step, or to identify which are available and which are not before you run it?
looking to tighten this whole thing up.
However I'm working with the other teams (SQL server, AS400 and ORACLE teams are involved in this) and we'll run the few highly time critical tables as a separate job (these were needed for a special project over the weekend, but won't be ready til 4AM saturday). Apparently many of the tables can be handled later without panic
Like many things, if things get messy, it pays to step back and rethink
...
-- FORTRAN manual for Xerox Computers --
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply