"There is already an object named xxxxx" error

[mdav2]

I thought I would give some background first to my question. I am not an DBA but the ID I have sys has access to a database called "saw_live". We don't have experienced DBA's for SQL Server in my organisation but do have people with DBA access to our SQL cluster farm who can help me run setup scripts, administer users etc.

I am having problems with a database that has been recently set up which I have called "saw_live". I have set up the database and tables without a problem but when I come to create the stored procedures (see below) one of them returns the following error:

Msg 2714, Level 16, State 1, Procedure sp_get_courses_available, Line 55
There is already an object named '#coursesfull' in the database.

I have done some querying which mentions that anything prefixed by a hash (#) is a temporary table (double hash ## being global temporary). I have also found out that they should exist in the tempdb schema. I have looked for the existence of a table called “coursesfull” in the saw_live schema, the master schema and the tempdb schema and can’t find anything. However my access is limited so it could exist elsewhere. I’ve also checked the sys.objects for the same three schemas and its not there either

I have found a duplicate database which an earlier version my script had created. I had this deleted this and tried again with the same error.

The procedure will allow me to add it if I remove the SQL with the reference to the “coursesfull” table\object. It only seems to be this one object causing my problems.

Anyone help me out with some advice on what I could try next? If I can’t get to the bottom of this my alternative plan is to change the name of the temporary table. I would like to know why this error occurred if possible though.

Thanks,

Mark

-- Select the database
USE [saw_live]
GO

-- If the procedure exists in the database then drop it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_courses_available]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[sp_get_courses_available]
GO

-- Create the procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================================================
-- Author:	xxxx
-- Created:	July 2009
-- Version:	1
-- Description:	Procedure that returns the courses available for a client.
--              Used in the CLIENT screen in the VFP application
--
--              Notes & Version details added by xxxx, October 2009
-- =============================================================================
CREATE PROCEDURE sp_get_courses_available
	-- Add the parameters for the stored procedure here
	@tcclient varchar(20), 
	@tdfrom smalldatetime ,
	@tdto smalldatetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with 
	-- SELECT statements.
	SET NOCOUNT ON;

	-- Declare local variables used in the script
	DECLARE @lnmaxclients INT
	DECLARE @lnmaxinstructor INT
	DECLARE @lncrsoffdays INT

	-- Store the values for the maximum number of clients, instructors and 
	-- offer days into local variables from the system maintenance table
	SELECT @lnmaxclients = maxclients, @lnmaxinstructor =  maxinstruc, @lncrsoffdays = crsoffdays 
		FROM sysmaint

	-- Return all the course references which the client is already on into a 
	-- temporary table (#alreadyon)
	SELECT courseref
		INTO #alreadyon
		FROM crsclnt
		WHERE clientref = @tcclient

	-- Return all the course references where the courses are full in temporary 
	-- table (coursesfull) A course is considered full when it has the maximum 
	-- number of clients as defined by the SYSMAINT.MAXCLIENTS value
	SELECT courseref
		INTO #coursesfull
		FROM Course
		WHERE courseref IN (
			SELECT crsclnt.courseref
				FROM crsclnt
				GROUP BY crsclnt.courseref
				HAVING COUNT(crsclnt.courseref) >= @lnmaxclients
		)

	-- This returns all the courses that have a count over the venue capacity
	-- ? not sure how this is used yet ?
	SELECT c.courseref --, count(*), v.venuecap 
		INTO #coursesfull
		FROM course c, crsvenue cv, venue v, crsclnt cc
		WHERE c.courseref = cv.courseref
		AND cv.venueid = v.venueid
		AND c.courseref = cc.courseref
		GROUP BY c.courseref, v.venuecap
		HAVING count(*) >= v.venuecap
		ORDER BY 1






	-- Return all courses that have the maximum number of instructors assigned 
	-- and that have accepted the offer to tutor the course
	SELECT crsinst.courseref, COUNT(crsinst.courseref)  AS cnt
		INTO #maxinstructors
		FROM crsinst
		WHERE offerout ='Accepted'
		GROUP BY crsinst.courseref
		HAVING COUNT(crsinst.courseref) = @lnmaxinstructor

	-- Returns a list of courses that the user can choose to assign a client to
	-- This meets all the requirements as follows:
	--
	-- 1) Return only thoses courses that are not fully booked
	-- 2) and where the user is not already booked onto a course
	-- 3) and where the course has the maximum number of instructors assigned
	-- 4) Include only courses with a course date greater than x days after 
	--   current date
	-- 5) The course has not been cancelled
	--
	SELECT course.courseref, course.coursedate , course.coursetime, venue.venuename
		INTO #coursesavnocnt
		FROM  course
		INNER JOIN crsvenue ON  course.courseref = crsvenue.courseref
		INNER JOIN venue    ON crsvenue.venueid = venue.venueid
		WHERE course.courseref NOT IN (SELECT courseref FROM #coursesfull)
		AND course.courseref NOT IN (SELECT courseref FROM #alreadyon)
		AND course.courseref IN (SELECT courseref FROM #maxinstructors)
		AND Course.coursedate >= GETDATE() + @lncrsoffdays
		AND Course.coursecan <> 'Yes'
		ORDER BY course.coursedate

	-- Calculate the number of clients on each course
	SELECT crsclnt.courseref, COUNT(Crsclnt.courseref) AS cnt
		INTO #coursecnt
		FROM Crsclnt
		WHERE courseref IN (SELECT courseref FROM #coursesavnocnt)
		GROUP BY Crsclnt.courseref



	-- ????
	IF @tdto = '1901/01/01' begin
		SELECT #coursesavnocnt.courseref, convert(varchar(20), 
			#coursesavnocnt.coursedate, 103) as coursedate , 
			#coursesavnocnt.coursetime, #coursesavnocnt.venuename , 
			isnull(#coursecnt.cnt,0) 
			FROM #coursesavnocnt 
			FULL JOIN #coursecnt ON  #coursesavnocnt.courseref = #coursecnt.courseref
			WHERE coursedate >= @tdfrom
			ORDER BY #coursesavnocnt.coursedate 
	END ELSE BEGIN

		-- Combine the main details of available courses with the number of clients on each
		-- This is returned as the result of the stored procedure and populates the grid
		SELECT #coursesavnocnt.courseref, convert(varchar(20), #coursesavnocnt.coursedate, 103) as coursedate , 
			#coursesavnocnt.coursetime, #coursesavnocnt.venuename , isnull(#coursecnt.cnt,0) 
			FROM #coursesavnocnt 
			FULL JOIN #coursecnt ON  #coursesavnocnt.courseref = #coursecnt.courseref
			WHERE coursedate >= @tdfrom
			AND coursedate <= @tdto + 1
			ORDER BY #coursesavnocnt.coursedate 
	END 



--	-- Combine the main details of available courses with the number of clients on each
--	-- This is returned as the result of the stored procedure and populates the grid
--	SELECT #coursesavnocnt.courseref, CONVERT(VARCHAR(20), #coursesavnocnt.coursedate, 103) AS coursedate , 
--			#coursesavnocnt.coursetime, #coursesavnocnt.venuename ,	ISNULL(#coursecnt.cnt,0)
--		FROM #coursesavnocnt
--		FULL JOIN #coursecnt ON  #coursesavnocnt.courseref = #coursecnt.courseref
--		ORDER BY #coursesavnocnt.coursedate
	
	-- Drop all temporary tables
	DROP TABLE #alreadyon
	DROP TABLE #coursesfull
	DROP TABLE #maxinstructors
	DROP TABLE #coursesavnocnt
	DROP TABLE #coursecnt
END
GO

Mark Davies
Warwickshire County Council

 

[drlex]

Mark,
It doesn't like the repeated
SELECT xxxx
INTO #coursesfull
statements

Could you change the second one to
INSERT INTO #coursesfull
SELECT xxxx FROM
?



soi là, soi carré

 

[mdav2]

Thanks drlex, didn't cross my mind that was the problem.

The original script was written by a contractor who then created some updates. I tried to roll up the updates into a single procedure not realising it should have been two procedures. I am going to rename the temporary tables in the second procedure to avoid any conflicts. Should resolve my issue I hope!

Mark Davies
Warwickshire County Council

 

[DrSql]

Is similar named temp table being used by any other databases or procedures on that databases?

Tyr running this on your db


use saw_live
select * from sysobjects where name like '%#coursesfull%'

Good luck

Dr.Sql
Good Luck.