Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  no @@ERROR sp_executesql remote OPENROWSET

Author  Topic  pchak Starting Member 2 Posts Posted - 2009-08-03 : 22:37:38 I am using dynamic sql to create some queries to hit a number of remote servers to replicate data. It works fine (no issues w/syntax), and is more efficient than any other segment of the process. It returns 4-7 tables with different schemas from 15 remote locations, and is run hourly as part of a DTS package. Dynamic SQL being built: "INSERT INTO SlitRollDetail SELECT '00' as Site, remote.*, getdate() as ReplicationTimeStamp FROM OPENROWSET('SQLOLEDB',servername;'uid';pwd,'SELECT [LotID],[LineNumber],[SequenceNumber],[RunNumber],[RollNumber],[Item],[ItemDesc],[RollWidth],[RollWidthUOM],[RollLength],[RollLengthUOM],[SalesOrder],[SOLineNumber],[TappiID],[Completed],[DispositionCode],[Splices],[Disposition],[Created],[DeliveryDate],[DeliveryPref],[ExtraRunFlag] FROM remoteservername.dbo.SlitRollDetail where Created > ''07/24/09 9:00:00 AM'' ORDER BY Created') as remote" This is sufficent while working. However, a potential failure would be a server/db being down, WAN connection, etc. I would expect to get an error code (pertinent part of sproc): BEGIN SET NOCOUNT ON; --exec(@RowsetCode) --THIS DOES NOT RETURN @@ERROR either exec @err = sp_executesql @RowsetCode --SELECT @err = @@error select 'return: ' = @@error --debug REGARDLESS-THIS SHOWS 0 on error DECLARE @currtimeholder datetime set @currtimeholder = current_timestamp ----error handling -WANT THIS TO RUN SELECT @err = @@error If @err <> 0 BEGIN EXECUTE WriteLogRecord @site, @db, @table, @currtimeholder , @err , 'issue with getting result from remote server' return @err --leave END (otherwise other processing happens) If I mimic a failure " such as inserting a bad password, instead of a return code result when executing the sproc (and in the debug select statements, I get a run-time message: (1 row(s) affected) Msg 18456, Level 14, State 1, Line 1 Login failed for user 'fropro'. The sproc stops executing at this point. I thought that sp_executesql avoids the scope issue with dynamic sql. I just want to capture the return code that something happend, rather than having a complete failure, which is presenting the rest of my flow from occurring. I've considered the architectural cumbersomeness, anti-practice, sql injection, etc. of how this is designed, but time and current architecture dictate this method of retrieval. I would have liked to set up hourly SQL replication, but the schemas being hit do not allow for it. Thanks!