Using EXEC() AT Continued

My previous blogs on this topic demonstrate a way to use EXEC(...) AT ... to pass through a query to a linked server. The performance of this method is much better than a T-SQL query directly with the linked server. I tried it with an very big Oracle database table with great speed.

Today, I found another very interest issue with this pass-through query method. If the execution on the remote server has any error such conflict with constrains or wrong field name, the execution does not stop. The errors will be thrown at the end of execution. For example, the following codes will be executed completely:

DECLARE @sql NVARCHAR(MAX);
DECLARE @myCount INT;
-- Initialize setting
SET @myCount = -1; -- Initailize it
-- Build sql query
SET @sql = N'
BEGIN
  SELECT COUNT(*) INTO :myCount
    FROM owner.myTable
    WHERE id1 = ' + CAST(@id AS VARCHAR) + N';
END;';
-- id1 is an incorrect field name
EXEC (@sql, @myCount OUTPUT) AT linedOracleServer;
PRINT 'Count: ' + CAST(@myCount AS VARCHAR);
-- Prints Count: -1 Not stopped!

I tried similar codes with T-SQL directly using the lined server:

DECLARE @myCount INT;
-- Initialize setting
SET @myCount = NULL;
-- Use T_SQL query
SELECT @myCount = COUNT(*) 
  FROM linkedOracleServer.owner.myTable
  WHERE id1 = 1;
-- NO prints, syntax error right away!
PRINT 'Count: ' + CAST(@myCount AS VARCHAR);

I found this interesting problem when I run a stored procedure with EXEC() AT to update value on Oracle side. It runs fine but today I found one error at the end of execution. The SP did not stop. Finally I figured out there were several violations of constraints. Since the whole stored procedure was completed and no exception to stop the program, the continuous codes set flags to mark insertion successful in another log table.

I tried to run a test scheduled job with this problem SP. The job log does indicate exception and step failure. However, the SP was executed completed.

EXEC() AT is a good way to leverage a remote server's full power; however, you have to be very careful about the process. Test your codes thoroughly before putting it into production. Another way may be to schedule a job to run the codes. If there is any failure, rollback any changes.