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.
