August 21, 2009 at 3:03 pm
On SQL 2000, I have several SPs which contain, among other stuff, lines like this:
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##CHEMPAX_INVENTORY') DROP TABLE [##CHEMPAX_INVENTORY]
SET @CMD = 'SELECT * INTO [##CHEMPAX_INVENTORY] FROM OPENQUERY(CHEMPAX, ''' + @sql + ''' )'
EXEC (@CMD)
These SPs are used to copy data from a Proggress database to a SQL DB.
Works fine, but I am trying to move this database from SQL 2000 to SQL 2005 on another server. It's giving me this error when I try to execute the SP:
Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30
Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions.
I don't think it's a permissions error, but I could be wrong. I suspect that the ## is something that doesn't work on 2005, but am googling it and finding nothing. Anyone familiar with changes in temp table syntax that has changed between the two versions?
August 21, 2009 at 3:59 pm
The ## at the beginning of a temp table name designates that the table is a global temporary table.
Quoting from BOL: Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. (emphasis mine)
I suspect the stored procedure which will not execute expects this table to already be in existence, even though it is a temporary table. Also quoting from BOL: If no other user works with this table after you create it, the table is deleted when you disconnect.
There may be some other procedure which creates it in another session. That session cannot be ended before your procedure starts or the ##table will disappear.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2009 at 12:50 pm
But that wouldn't explain why this thing works just fine on the old database. It runs successfully every morning.
August 24, 2009 at 12:56 pm
I don't think the problem is your temporary table, look at your error message again:
Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30
Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions
It can't find CHEMPAX_COSTS. Your temporary table is ##CHEMPAX_INVENTORY.
August 24, 2009 at 1:00 pm
I'm sorry for causing confusion. I have several of these jobs. My question was about the # signs, so I copied and pasted the particular one I had highlighted at the time.
Here's the code for the Costs one:
USE [RICCA]
GO
/****** Object: StoredProcedure [dbo].[SP_REFRESH_CHEMPAX_COSTS] Script Date: 08/24/2009 13:53:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_REFRESH_CHEMPAX_COSTS]
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(1000)
DECLARE @CMD VARCHAR(1000)
SET @sql =
'
SELECT
"FACILITY",
"AVERAGE-COST",
"STANDARD-COST",
"PROD-PKG-CODE"
FROM "SQLVIEW"."WHS-PROD-PKG"
WHERE "System-ID" = ''''DATACOR''''
AND ACTIVE = 1
'
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##CHEMPAX_COSTS') DROP TABLE [##CHEMPAX_COSTS]
SET @CMD = 'SELECT * INTO [##CHEMPAX_COSTS] FROM OPENQUERY(CHEMPAX, ''' + @sql + ''' )'
EXEC (@CMD)
IF EXISTS(SELECT TOP 1 * FROM [##CHEMPAX_COSTS])
BEGIN
TRUNCATE TABLE CHEMPAX_COSTS
INSERT INTO CHEMPAX_COSTS SELECT * FROM ##CHEMPAX_COSTS
END
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##CHEMPAX_COSTS') DROP TABLE [##CHEMPAX_COSTS]
SET NOCOUNT OFF
GRANT EXECUTE
ON [DBO].[SP_REFRESH_CHEMPAX_COSTS]
TO [Public]
and here's the message when I try to manually execute that SP on the new DB server:
Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30
Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions.
August 24, 2009 at 1:06 pm
Again, look at the error message. It is not complaining about the temporary table:
Msg 4701, Level 16, State 1, Procedure SP_REFRESH_CHEMPAX_COSTS, Line 30
Cannot find the object "CHEMPAX_COSTS" because it does not exist or you do not have permissions.
August 24, 2009 at 1:15 pm
AAAAAUUUUUUGHHHHHHHHH!
It's OK if I yell here, right?
Sorry, I am inheriting other people's code and totally didn't catch that there is a temp table and real table with almost the same name.
So thanks to you, I just imported that one table over from old to new. I re-ran the SP, and it didn't disply an error. It simply shows in the message or results pane a Return Value of 0.
So I opened up the new table, and it appears to be full of valid data. So I guess that was my problem.
thanks!
August 24, 2009 at 1:27 pm
Yes, you are permitted to yell. I'm glad we were able to help you find the problem. Sometimes it just takes another set of eyes. Typical Forest and Trees Syndrome.
August 24, 2009 at 1:35 pm
Lynn Pettis (8/24/2009)
Yes, you are permitted to yell. I'm glad we were able to help you find the problem. Sometimes it just takes another set of eyes. Typical Forest and Trees Syndrome.
Or as I prefer to state it: hard to read the letters with your head banging on the keyboard.......:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2009 at 4:02 pm
In a word: Doh! :w00t:
Man, I am really off my game.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 24, 2009 at 4:09 pm
Bob Hovious (8/24/2009)
In a word: Doh! :w00t:Man, I am really off my game.
You probably got a concussion from banging your head on your keyboard too many times. 😛
August 25, 2009 at 7:40 am
Quite likely.... and in the end, she solves her own problem. 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply