View Single Post
  #1 (permalink)  
Old April 27th, 2007, 05:40 AM
bashiiui bashiiui is offline
Registered User
Join Date: Apr 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Raise an Error in dynamic SQL with Link Server

Hi All,
Could any body guide me in this situation?

I run following query then it is reading an exception, I know the btTable.Name is duplicated and raiser Unitke Contrant exception, but I want to trace this error into @@Error variable to notify my application that an error is raised, but it is not even running the statement Print 'Display error for testing'. dkpc166 and sql09 are the Link Servers into my local sql server and the query is running into my machine. Is there any way to Raise the error from here?

DECLARE @err int
EXECUTE sp_ExecuteSQL N'INSERT INTO dkpc166.f4aLocal.dbo.[dbTables] SELECT * FROM sql09.finance4all_alfa.dbo.[dbTables] WHERE Guid = ''51FCA305-BCD2-407F-8B40-290B32F2D74C'''
SELECT @err = coalesce(nullif(@err, 0), @@error)
IF @err <> 0
            Print 'Display error for testing'
            RaiseError (‘abc’, 15,-1)

ERROR Raised into Analyzer:
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'IX_dbTables'. Cannot insert duplicate key in object 'dbTables'.
The statement has been terminated.