FIX: Unexpected Behavior During Object Resolution

• Microsoft SQL Server, versions 4.21a and 6.0

SYMPTOMS

During object resolution, Microsoft SQL Server version 6.0 may not successfully resolve the dependency, which can cause a thread access violation.

There are several variations of the behavior depending on whether you are using a view or a stored procedure, and, more specifically, user created objects. User created objects induce errors with stored procedure resolution.

There are two basic situations. First, the table is created, a view or stored procedure is created to reference the table, and then the table is dropped.

The second situation is the same as the first, only the table is re- created. In general, you would expect the first scenario always to error because the object no longer exists, and the second to resolve the table change.

However, the following chart lists the different scenarios that lead to unsuccessful outcomes. The first scenario is referred to as NOTBL and the second outcome is referred to as WITHTBL.

Table created by: dbo

View create by:    dbo

Test run by user: dbo

NOTBL:

   Version: 6.0
   The first dbo connection that attempts to execute the view receives
   error message(208):

       Invalid object name '%s'.

   The second dbo connection receives a thread access violation and the
   SQL Server hangs, meaning no new connections are accepted by the SQL
   Server until it is stopped and restarted.

   Version: 4.21a
   Continues to receive error message (208):

      Invalid object name '%s'

   and 4413 error messages.

Table created by:   dbo
View create by:     dbo
Test run by user:   test_user

NOTBL:

   Version: 6.0
   The first dbo connection that attempts to execute the view receives
   error message (208):

       Invalid object name '%s'.

   The second dbo connection receives a thread access violation and the
   SQL Server hangs, meaning no new connections are accepted by the SQL
   Server until it is stopped and restarted.

   Version: 4.21a
   Continues to receive error message (208):

      Invalid object name '%s'

   and 4413 error messages.

Table created by:   test_user
View create by:     test_user
Test run by user:   test_user

NOTBL:

   Version: 6.0
   The first dbo connection that attempts to execute the view receives
   error message (208):

       Invalid object name '%s'.

   The second dbo connection receives a thread access violation and the
   SQL Server hangs, meaning no new connections are accepted by the SQL
   Server until it is stopped and restarted.

   Version: 4.21a
   Continues to receive error message (208):

      Invalid object name '%s'

   and 4413 error messages.

View create by:    test_user

NOTBL:

   Version: 6.0
   The first dbo connection that attempts to execute the view receives
   error message (208):

       Invalid object name '%s'.

   The second dbo connection receives a thread access violation and the
   SQL Server hangs, meaning no new connections are accepted by the SQL
   Server until it is stopped and restarted.

   Version: 4.21a
   Continues to receive error message (208):

      Invalid object name '%s'

   and 4413 error messages.

   Version: 6.0
   The first dbo connection that attempts to execute the view receives
   error message (208):

      Invalid object name '%s'.

   The second dbo connection receives a thread access violation and the
   SQL Server hangs, meaning no new connections are accepted by the SQL
   Server until it is stopped and restarted.

   Version: 4.21a
   Successful resolution

Table created by:    test_user

Test run by user:    dbo

WITHTBL:

   Version: 6.0
   Continues to generate error message (208):

      Invalid object name '%s'.

   and never resolves the dependency.

   Version: 4.21a
   Successful resolution

WORKAROUND

Drop and recreate the object(s) which rely on the missing or changed dependency object.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 4.21a and 6.0. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.