Technical: Microsoft – SQLServer (v2008/R2) – Management Studio – Generate and Publish Script – Error Message – “There is already an object named ‘#tempdep’ in the database”

Technical: Microsoft – SQL Server (v2008/R2) – Management Studio – Generate and Publish Script – Error Message – “There is already an object named ‘#tempdep’ in the database”

Introduction

In Management Studio v2008/R2, when trying to script the database objects using “Generate and Publish Script”, we are running into the error pasted below.

Error


Error: 2714, Severity: 16, State: 6

There is already an object named '#tempdep' in the database.

CREATE TABLE #tempdep (objid int NOT NULL, objname sysname NOT NULL, objschema 
sysname NULL, objdb sysname NOT NULL, objtype smallint NOT NULL)

Identify Error

When we choose to script objects and “have the include dependency” option included, the first task performed is to investigate dependency checks.


declare @find_referencing_objects int
set @find_referencing_objects = 0
-- parameters:
-- 1. create table #tempdep (objid int NOT NULL, objtype smallint NOT NULL)
--    contains source objects
-- 2. @find_referencing_objects defines ordering
--    1 order for drop
--    0 order for script

declare @must_set_nocount_off bit
set @must_set_nocount_off = 0

IF @@OPTIONS & 512 = 0 
   set @must_set_nocount_off = 1
set nocount on

.. in security conscious environments, the user trying to perform these steps might encounter permission problems such as:

  • Missing “VIEW ANY DEFINITION”  (SQL Server Instance) /  “VIEW ANY DEFINITION” (Database permission) permission issue.
  • Missing db_datareader permissions; relevant in cases where object’s data are been scripted, as well

Error

 

Post granting of permissions, when I tried again now getting the error pasted below:

GettingTheListofObjectsFromDatabase - Error

Chose to "Save Report".

The Saved Report contains meaningful errors:
Microsoft.SqlServer.Management.Smo.SmoException: An exception occurred while executing a Transact-SQL statement. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: There is already an object named '#tempdep' in the database. at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteImmediate(String query) at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query) at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.EnumDependencies(Object ci, DependencyRequest rd) at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.EnumDependencies(Object connectionInfo, DependencyRequest dependencyRequest) at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Image:

GettingTheListofObjectsFromDB

SQL Server Profiler

Retried while running SQL Server Profiler, and got the following error.

SQLServerProfiler

SQL Server Database Schema

Query:


use [tempdb];

select 
            tblObject.name 
          , tblObject.object_id 
          , SCHEMA_NAME(tblObject.schema_id) as schemaName 
          , tblObject.type_desc as [type] 
          , tblObject.create_date 
          , tblObject.modify_date 
from sys.objects tblObject 
where tblObject.type = 'U' 
order by tblObject.create_date desc 

Image:
sysObjects

From the screen shot above, we obviously have a table name #tempdb already in the tempdb.

Check for Pages Allocated to Objects

Using the script from :

FIX: SQL Server 2005 does not reclaim the disk space that is allocated to the temporary table if the stored procedure is stopped
http://support.microsoft.com/kb/931843

we are able to re-verify that the temp object is active and has storage allocated to it.



begin tran

	use tempdb;

	SELECT 
			  Name=so.name
			, TotalPages=total_pages

	FROM tempdb.sys.objects so (NOLOCK)

		JOIN tempdb.sys.partitions pa (nolock) 

			ON so.object_id = pa.object_id

		LEFT JOIN tempdb.sys.allocation_units al (NOLOCK)

			ON (al.type in (1, 3) AND pa.hobt_id = al.container_id)
			OR (al.type = 2 AND pa.partition_id = al.container_id)

	WHERE name LIKE '#%'

	ORDER BY Name

rollback tran

Allocated Storage

CheckTempdbObjectsAndStorageAllocation

Remediation Choices

Identify / Kill Off Sessions

Identify & Kill Off Sessions belonging to you or person running steps



select 
		  SUSER_SID()  as [suserSID]
		, 'kill ' + cast(tblSP.spid as sysname) as SQLStatement
		, *

from   master.dbo.sysprocesses tblSP

	   inner join master.dbo.syslogins tblSL

		on tblSP.sid = tblSL.sid				

where  tblSP.spid > 50
and    tblSP.spid != @@spid
and    
	(
   	  (tblSP.program_name like 'Microsoft SQL Server Management Studio%' )                                                                                         
	)			
and    tblSL.name = SUSER_NAME()

Exit Management Studio

Exit Management Studio altogether.  Please keep in mind that it is not nearly enough to close the Wizard, not disconnect from the SQL Instance ( via Management Studio).  You really have to close all SQL Server Management Studios and exit from the App.

Code Changes

Code Changes – Microsoft

I will suggest that Microsoft makes code changes such as:

  • Check for object existence before attempting to create/drop
    if object_id('#tempdep') is not null
    begin
      drop table #tempdep
    end

 

Other Errors

Other Errors – Index was out of range

Error Message:

Index was out of range.
Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

Error Image:
IndexWasoutofRange

Other Errors – Creating a user without an associated login is not supported in SQL Server 2000

Error Message:

Microsoft.SqlServer.Management.Smo.SmoException: Creating a user without an 
associated login is not supported in SQL Server 2000.; at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Error Image:

AssociatedLoginIsNotSupported

Summary

The problem is not reproducible in Microsoft SQL Server Management Studio v2012; even when connecting to an MS SQL Server v2008/R2 instance.

And, so the problem seems to be completely isolated to the client code that is in use in MS SQL Server v2008/R2.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s