FIX: Syntax Error in CREATE PROC May Cause Dropped Connection
  
PSS ID Number: Q111870
Article last modified on 10-31-1994
 
4.20
 
Windows
 

----------------------------------------------------------------------
The information in this article applies to:
 
  - Microsoft SQL Server version 4.2
----------------------------------------------------------------------
 
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.
