Technical: Microsoft – SQL Server (v2008/R2) – Management Studio – Generate and Publish Script – Error Message – “There is already an object named ‘#tempdep’ in the database”
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: 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)
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
Post granting of permissions, when I tried again now getting the error pasted below: 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)
SQL Server Profiler
Retried while running SQL Server Profiler, and got the following error.
SQL Server Database Schema
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
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
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
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 – 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 – Index was out of range
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index (mscorlib)
Other Errors – Creating a user without an associated login is not supported in SQL Server 2000
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)
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.