p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Raise an Error in dynamic SQL with Link Server (http://p2p.wrox.com/showthread.php?t=57197)

bashiiui April 27th, 2007 05:40 AM

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
BEGIN
            Print 'Display error for testing'
            RaiseError (‘abc’, 15,-1)
END

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.

BR,
BASH
:)


happygv April 27th, 2007 06:32 AM

Code:

EXECUTE sp_ExecuteSQL N'INSERT INTO dkpc166.f4aLocal.dbo.[dbTables]
SELECT * FROM sql09.finance4all_alfa.dbo.[dbTables]
WHERE Guid = ''51FCA305-BCD2-407F-8B40-290B32F2D74C'''

Try printing @@ERROR after this and see what you get, then you can take it forward.

cheers.

_________________________
- Vijay G
Strive for Perfection

bashiiui April 27th, 2007 07:02 AM

Actually it is not going to execute any statement after it. It seems like an exception is raised into the language code snippet and skip rest of the statements. So same behavior even I try to print @@ERROR.

Cheers


happygv April 27th, 2007 07:29 AM

Sorry, I over looked it. I read your post yet again carefully. Your code violates the UNIQUE constraint. When you say you know that duplicates can happen and still trying to insert into a table where UNIQUE contraint is set, you should be handling it such that you ignore duplicates and allow INSERT, using a where clause. Else you shouldn't be setting Unique constraint.

How are you executing this statement from your application? Are you using a Stored proc with BEGIN TRAN and ROLLBACK/COMMIT TRAN statements something like that?

_________________________
- Vijay G
Strive for Perfection

bashiiui May 1st, 2007 08:52 AM

Sorry, I could not get back due to another issue. Well, the idea is as follows: I am synchronizing the all records of table named (dbTables) from one database (on sql09 machine) to another (on dkpc166). dbTables from source database have UNIQUE constraint and having 700 records in place (out of them 3 records are repreated inspite of UNIQUE contraint is there, it is situation today, may be contraint was created without existing data checking). Now when I try to copy all the records from source database dbTables to destination database dbTables, unique key violation error occurs. I qouted above exaple as one of the case if there is a voilation of contraint into the remote database server, then this behavior is raised and could not find way to get the error into @@Error.
Yes, transaction is an other issue into it. I could not use Transaction or distributed transaction into this type of query so far even if DTC is running on all of the parties. It raise the error in SQLOLEDB provider.

Regards and thanks Vijay :)
BASH


happygv May 2nd, 2007 01:55 AM

Then you should consider not using an UNIQUE constraint in the destination table to have it to work.

_________________________
- Vijay G
Strive for Perfection

bashiiui May 2nd, 2007 02:31 AM

Yes, I have exactly same table schema and constraints on both sides thats where Violation of UNIQUE KEY constraint 'IX_dbTables' occurs. The point is that when the constraints are there, On violation into destination table on dkpc166, why I get following exception in query analyzer.

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
BEGIN
            Print 'Display error for testing'
            RaiseError (‘abc’, 15,-1)
END

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.

Issue: Why Print statement is not get executed, how I am going to execute the Print statement?



happygv May 2nd, 2007 04:10 AM

ERROR is about voiolation of Unique contraint. The destination table has the same row that you are trying to insert again. Since it is set with unique contraint, it wont let you proceed further.

Try this and see how it works
Code:

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

_________________________
- Vijay G
Strive for Perfection


All times are GMT -4. The time now is 09:55 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.