Microsoft KB Archive/111870

FIX: Syntax Error in CREATE PROC May Cause Dropped Connection

PSS ID Number: Q111870 Article last modified on 10-31-1994

4.20

Windows

BUG# NT: 641 (4.2)

= SYMPTOMS =

A syntax error in a CREATE PROCEDURE statement may cause the client connection to be dropped with the following error messages:

Msg 213, Level 16, State 4 Insert error: column name or number of supplied values does not match table definition.

Msg 1203, Level 20, State 1 Caller of lock manager is incorrectly trying to unlock an unlocked object. spid=5 locktype=6 dbid=2 lockid=50.

The SQL Server is terminating this process.

The first error is expected, but the connection should not be dropped. This problem is seen in a CREATE PROC statement that creates a temporary table and then attempts to insert a record into the temporary table, but does not specify the correct syntax on the insert. For example:

create proc testproc as create table #testtab(pub_id char(4), pub_name varchar(40), city varchar(20), state char(2)) insert #testtab select pub_id, pub_name, state from publishers go

In the Event viewer, the 1203 lock manager error is followed by:

Msg 17655, Pss found with open sdes … Msg 17306, Process being freed while holding Dataserver Semaphore

= WORKAROUND =

Connect to SQL Server and correct the syntax error, then re-issue the CREATE PROC statement.

= STATUS =

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.2. This problem was corrected in SQL Server version 4.21. For more information, contact your primary support provider.

Additional reference words: 4.20 Transact-SQL Windows NT KBCategory: kbprg kberrmsg KBSubcategory: SSrvWinNT

=
================================================================

Copyright Microsoft Corporation 1994.