Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 27th, 2007, 05:40 AM
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
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
:)

Reply With Quote
  #2 (permalink)  
Old April 27th, 2007, 06:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old April 27th, 2007, 07:02 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old April 27th, 2007, 07:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old May 1st, 2007, 08:52 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #6 (permalink)  
Old May 2nd, 2007, 01:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #7 (permalink)  
Old May 2nd, 2007, 02:31 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?


Reply With Quote
  #8 (permalink)  
Old May 2nd, 2007, 04:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL SERVER link problem.please hope me. kukusheng Classic ASP Databases 0 July 29th, 2007 08:26 PM
Raise Event From Dynamic Controls netwizard_01 ASP.NET 1.0 and 1.1 Basics 1 August 3rd, 2006 06:50 AM
Best way to link Access to SQL Server echovue Access VBA 0 May 31st, 2006 04:28 PM
can to link a ms access table to sql server? sugar SQL Server 2000 6 May 23rd, 2006 11:45 AM
how can i link to sql-server with ado.net[B)] zjxgjp ADO.NET 1 December 25th, 2004 09:50 AM



All times are GMT -4. The time now is 02:39 AM.


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