Strange behaviour in SQL Server 2000? 
Author Message
 Strange behaviour in SQL Server 2000?

I have the following TSQL in a stored procedure...


"CREATE UNIQUE CLUSTERED INDEX uqcidx_cuscode_code ON



When run, the CREATE INDEX statement fails with...

"CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is 'CAM1884   '.
The statement has been terminated."

The problem is that the entire stored procedure is then simply terminated.

thing is that this used to work, but now doesn't!

Any ideas much appreciated...

--
Best regards...
Mark Baldwin
CAM Systems (Dursley) Ltd
Tel: +44 (0)1453 891000



Sun, 09 Jul 2006 11:02:48 GMT
 Strange behaviour in SQL Server 2000?

This error is with severity 14 so it must not terminate further
execution....
Probably another setting somewhere


Sun, 09 Jul 2006 12:02:35 GMT
 Strange behaviour in SQL Server 2000?
Hi Mark,

The reason why it fails its quite simple, the unique index
already exists within your table.

The rest of it is a bit of a cop out. SQL Server has a
hierachy of severity of errors. Unfortunatly the hierachy
has not been documented that well, so based upon the
severity of the error it will even continue or throw a
wobbly and exit out of the T-SQL.

J

Quote:
>-----Original Message-----
>I have the following TSQL in a stored procedure...


>"CREATE UNIQUE CLUSTERED INDEX uqcidx_cuscode_code ON



>When run, the CREATE INDEX statement fails with...

>"CREATE UNIQUE INDEX terminated because a duplicate key
was found for index
>ID 1. Most significant primary key is 'CAM1884   '.
>The statement has been terminated."

>The problem is that the entire stored procedure is then
simply terminated.

called. The strange
>thing is that this used to work, but now doesn't!

>Any ideas much appreciated...

>--
>Best regards...
>Mark Baldwin
>CAM Systems (Dursley) Ltd
>Tel: +44 (0)1453 891000

>.



Sun, 09 Jul 2006 12:09:28 GMT
 Strange behaviour in SQL Server 2000?
The error that you get when you try to create an index on a combination of
columns that is not unique will terminate the batch and any code after the
statement that caused the error, including your error handler, will never
get executed.

I very much doubt that your error handler used to work, as this behaviour
has always been in SQL Server.
Why do you have multiple tables with the same unique key by the way? Can't
you use temporary tables instead?

--
Jacco Schalkwijk
SQL Server MVP


Quote:
> I have the following TSQL in a stored procedure...


> "CREATE UNIQUE CLUSTERED INDEX uqcidx_cuscode_code ON



> When run, the CREATE INDEX statement fails with...

> "CREATE UNIQUE INDEX terminated because a duplicate key was found for
index
> ID 1. Most significant primary key is 'CAM1884   '.
> The statement has been terminated."

> The problem is that the entire stored procedure is then simply terminated.

strange
> thing is that this used to work, but now doesn't!

> Any ideas much appreciated...

> --
> Best regards...
> Mark Baldwin
> CAM Systems (Dursley) Ltd
> Tel: +44 (0)1453 891000



Sun, 09 Jul 2006 12:16:02 GMT
 Strange behaviour in SQL Server 2000?
Hi Bojidar,

If only the implementation of errors in SQL Server was that simple that you
could predict the behaviour of an error from the severity..... There is no
correlation between the behaviour of the error and it's severity except that
errors with severity 0 or 10 will never terminate anything, and errors with
severity 20 or higher will always terminate the connection.

Errors of severity 14 are actually more likely to be batch terminating
errors (and prevent the execution of further code) than errors of severity
16

--
Jacco Schalkwijk
SQL Server MVP


Quote:
> This error is with severity 14 so it must not terminate further
> execution....
> Probably another setting somewhere



Sun, 09 Jul 2006 12:20:52 GMT
 Strange behaviour in SQL Server 2000?
Thanks, Jacco
Can you refer me to source where people experimentally have documented this
behaviour, or your own findings?


Sun, 09 Jul 2006 12:51:49 GMT
 Strange behaviour in SQL Server 2000?
There no complete list errors and their behaviour, but Erland Sommarkog has
2 good articles about error handling on his website,
http://www.sommarskog.se/error-handling-I.html being the one with a lot of
background information.

And you can have a look at the slides of a presentation I gave a while ago:
file:///C:/Documents%20and%20Settings/jaccos/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/Z6SJB9KD/257,1,Error
handling in SQL Server

--
Jacco Schalkwijk
SQL Server MVP


Quote:
> Thanks, Jacco
> Can you refer me to source where people experimentally have documented
this
> behaviour, or your own findings?



Sun, 09 Jul 2006 13:17:33 GMT
 Strange behaviour in SQL Server 2000?
This error handler definately used to work. When the CREATE INDEX fails, the
stored proc returns an error code. This trapped by the ADO client program
that runs the stored proc which then generates an email to our support
dept - I have copies of these emails showing that at some times in the past,
this code has picked up the exact same error but without terminating.

For example, on 7/11/2003 the system sent me an email stating...

--------------------
This message is an automated response.

Update for publisher "ELI" failed.

"[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX
terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'CAM1594   '"
--------------------

The only difference here is that previously I was using the ODBC driver to
initiate the stored proc, but now I use ADO. Could this be something to do
with it or could the terminate behaviour have been changed with a service
pack perhaps?

Best regards...
Mark Baldwin


Quote:
> The error that you get when you try to create an index on a combination of
> columns that is not unique will terminate the batch and any code after the
> statement that caused the error, including your error handler, will never
> get executed.

> I very much doubt that your error handler used to work, as this behaviour
> has always been in SQL Server.
> Why do you have multiple tables with the same unique key by the way? Can't
> you use temporary tables instead?

> --
> Jacco Schalkwijk
> SQL Server MVP



> > I have the following TSQL in a stored procedure...


> > "CREATE UNIQUE CLUSTERED INDEX uqcidx_cuscode_code ON



> > When run, the CREATE INDEX statement fails with...

> > "CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index
> > ID 1. Most significant primary key is 'CAM1884   '.
> > The statement has been terminated."

> > The problem is that the entire stored procedure is then simply
terminated.

> strange
> > thing is that this used to work, but now doesn't!

> > Any ideas much appreciated...

> > --
> > Best regards...
> > Mark Baldwin
> > CAM Systems (Dursley) Ltd
> > Tel: +44 (0)1453 891000



Sun, 09 Jul 2006 13:39:07 GMT
 Strange behaviour in SQL Server 2000?

Quote:
> There no complete list errors and their behaviour, but Erland Sommarkog
has
> 2 good articles about error handling on his website,
> http://www.sommarskog.se/error-handling-I.html being the one with a lot of
> background information.

Thanks. I will check this out.

Quote:
> And you can have a look at the slides of a presentation I gave a while
ago:

file:///C:/Documents%20and%20Settings/jaccos/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/Z6SJB9KD/257,1,Error

Quote:
> handling in SQL Server

Nice URL :)

Quote:
> Jacco Schalkwijk
> SQL Server MVP

Bojidar Alexandrov


Sun, 09 Jul 2006 14:31:58 GMT
 Strange behaviour in SQL Server 2000?
Just shoot in the dark  -
Try to set XACT_ABORT OFF before executing of your sp


Sun, 09 Jul 2006 14:34:49 GMT
 Strange behaviour in SQL Server 2000?
Nice catch - just readed article provided by Jacco Schalkwijk
Read here:
http://www.sommarskog.se/error-handling-I.html#XACT_ABORT


Sun, 09 Jul 2006 14:36:34 GMT
 Strange behaviour in SQL Server 2000?
Hi Mark,

The difference is indeed caused by having ADO instead of ODBC. The error you
saw under ODBC doesn't come from your error handler, but is the error that
was raised directly by the failure of the create index statement. Errors in
T-SQL are always raised to the client application, no matter what.

ADO however handles errors differently than ODBC (using ADO or ODBC has no
effect on the SQL side of course). I am no expert in ADO, but I think the
issue is caused because ADO treats the "n rows effected" message as a
separate, empty resultset. "n rows affected" doesn't have an errors attached
to it, so you have to move to the next resultset in ADO to get your error.
You can avoid this by using SET NOCOUNT ON in your stored procedure, so that
the "n rows effected" message is not returned to your client. This is good
practice anyway.

If the previous sounds a bit confusing, please read the error handling
articles at www.sommarskog.se, and specially the bit:
http://www.sommarskog.se/error-handling-II.html#NOCOUNT

--
Jacco Schalkwijk
SQL Server MVP


Quote:
> This error handler definately used to work. When the CREATE INDEX fails,
the
> stored proc returns an error code. This trapped by the ADO client program
> that runs the stored proc which then generates an email to our support
> dept - I have copies of these emails showing that at some times in the
past,
> this code has picked up the exact same error but without terminating.

> For example, on 7/11/2003 the system sent me an email stating...

> --------------------
> This message is an automated response.

> Update for publisher "ELI" failed.

> "[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX
> terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'CAM1594   '"
> --------------------

> The only difference here is that previously I was using the ODBC driver to
> initiate the stored proc, but now I use ADO. Could this be something to do
> with it or could the terminate behaviour have been changed with a service
> pack perhaps?

> Best regards...
> Mark Baldwin



> > The error that you get when you try to create an index on a combination
of
> > columns that is not unique will terminate the batch and any code after
the
> > statement that caused the error, including your error handler, will
never
> > get executed.

> > I very much doubt that your error handler used to work, as this
behaviour
> > has always been in SQL Server.
> > Why do you have multiple tables with the same unique key by the way?
Can't
> > you use temporary tables instead?

> > --
> > Jacco Schalkwijk
> > SQL Server MVP



> > > I have the following TSQL in a stored procedure...


> > > "CREATE UNIQUE CLUSTERED INDEX uqcidx_cuscode_code ON



> > > When run, the CREATE INDEX statement fails with...

> > > "CREATE UNIQUE INDEX terminated because a duplicate key was found for
> > index
> > > ID 1. Most significant primary key is 'CAM1884   '.
> > > The statement has been terminated."

> > > The problem is that the entire stored procedure is then simply
> terminated.

> > strange
> > > thing is that this used to work, but now doesn't!

> > > Any ideas much appreciated...

> > > --
> > > Best regards...
> > > Mark Baldwin
> > > CAM Systems (Dursley) Ltd
> > > Tel: +44 (0)1453 891000



Sun, 09 Jul 2006 15:02:43 GMT
 Strange behaviour in SQL Server 2000?
Jacco,

Your a genius! It now works as expected, thx

--
Best regards...
Mark Baldwin


Quote:
> Hi Mark,

> The difference is indeed caused by having ADO instead of ODBC. The error
you
> saw under ODBC doesn't come from your error handler, but is the error that
> was raised directly by the failure of the create index statement. Errors
in
> T-SQL are always raised to the client application, no matter what.

> ADO however handles errors differently than ODBC (using ADO or ODBC has no
> effect on the SQL side of course). I am no expert in ADO, but I think the
> issue is caused because ADO treats the "n rows effected" message as a
> separate, empty resultset. "n rows affected" doesn't have an errors
attached
> to it, so you have to move to the next resultset in ADO to get your error.
> You can avoid this by using SET NOCOUNT ON in your stored procedure, so
that
> the "n rows effected" message is not returned to your client. This is good
> practice anyway.

> If the previous sounds a bit confusing, please read the error handling
> articles at www.sommarskog.se, and specially the bit:
> http://www.sommarskog.se/error-handling-II.html#NOCOUNT

> --
> Jacco Schalkwijk
> SQL Server MVP



> > This error handler definately used to work. When the CREATE INDEX fails,
> the
> > stored proc returns an error code. This trapped by the ADO client
program
> > that runs the stored proc which then generates an email to our support
> > dept - I have copies of these emails showing that at some times in the
> past,
> > this code has picked up the exact same error but without terminating.

> > For example, on 7/11/2003 the system sent me an email stating...

> > --------------------
> > This message is an automated response.

> > Update for publisher "ELI" failed.

> > "[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX
> > terminated because a duplicate key was found for index ID 1. Most
> > significant primary key is 'CAM1594   '"
> > --------------------

> > The only difference here is that previously I was using the ODBC driver
to
> > initiate the stored proc, but now I use ADO. Could this be something to
do
> > with it or could the terminate behaviour have been changed with a
service
> > pack perhaps?

> > Best regards...
> > Mark Baldwin



> > > The error that you get when you try to create an index on a
combination
> of
> > > columns that is not unique will terminate the batch and any code after
> the
> > > statement that caused the error, including your error handler, will
> never
> > > get executed.

> > > I very much doubt that your error handler used to work, as this
> behaviour
> > > has always been in SQL Server.
> > > Why do you have multiple tables with the same unique key by the way?
> Can't
> > > you use temporary tables instead?

> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP



> > > > I have the following TSQL in a stored procedure...


> > > > "CREATE UNIQUE CLUSTERED INDEX uqcidx_cuscode_code ON



> > > > When run, the CREATE INDEX statement fails with...

> > > > "CREATE UNIQUE INDEX terminated because a duplicate key was found
for
> > > index
> > > > ID 1. Most significant primary key is 'CAM1884   '.
> > > > The statement has been terminated."

> > > > The problem is that the entire stored procedure is then simply
> > terminated.

> > > strange
> > > > thing is that this used to work, but now doesn't!

> > > > Any ideas much appreciated...

> > > > --
> > > > Best regards...
> > > > Mark Baldwin
> > > > CAM Systems (Dursley) Ltd
> > > > Tel: +44 (0)1453 891000



Fri, 14 Jul 2006 12:17:03 GMT
 
 [ 13 post ] 

 Relevant Pages 

1. Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S

2. Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S

3. SQL Server 2000 behaviour is different in Server 2000 and Windows NT Workstation

4. Strange behaviour of xp_cmdshell on SQL 2000

5. strange data control behaviour with DAO 3.6 and SQL server

6. SQL Server 7.0 BUG: very strange behaviour !!!

7. Strange behaviour from Stored procedure after starting SQL Server

8. SQL Server 2000 log and database behaviour

9. SQL Server 2000 database and log file behaviour

10. locking behaviour with linked SQL Server tables in an Access 2000 application

11. Very strange server behaviour. Please help

12. Strange Behaviour on MSSQL Server: TimeOut Expired.


 
Powered by phpBB® Forum Software