October 27, 2016 at 7:00 am
I have a stored procedure that drops temp tables using dynamic sql. Immediately following that stored procedure is a CREATE TABLE for the temp table and then a INSERT into that temp table but I am getting an error.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 208, Level 16, State 0, Line 34
Invalid object name '#Error'.
I assume it is a result of out-of-order execution in some fashion but not sure how to tell nor how to ensure it is fixed.
This is the setup
-- Step 1: The function called by my DROP TEMP TABLEs stored procedure
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.f_DelimitedSplit8K
-- Written by Jeff Modine (SQLServerCentral.com)-- freely distributable
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
-- Step 2: the Drop Temp TABLE stored proc
CREATE PROCEDURE dbo.s_DropTempDBTable
@TableList varchar(8000)
AS
BEGIN;
DECLARE@SQLvarchar(max) = SPACE(0);
SELECT @SQL = @SQL +
CASE
WHEN LEN(@SQL) > 0
THEN CHAR(13) + CHAR(10)
ELSE SPACE(0)
END + 'IF OBJECT_ID(''tempdb..' + LTRIM(RTRIM(Item)) + ''') IS NOT NULL DROP TABLE ' + LTRIM(RTRIM(Item)) + ';'
FROMdbo.f_DelimitedSplit8K (@TableList, ',')
EXEC (@SQL);
END;
GO
This is the actual code.
-- Step 3: This is the actual code that is being run
EXEC dbo.s_DropTempDBTable '#Error';
GO
CREATE TABLE #Error
(
RowSeq INT NOT NULL
IDENTITY(1, 1) ,
Item NVARCHAR(128) NOT NULL ,
Msg NVARCHAR(2048)
NOT NULL PRIMARY KEY CLUSTERED ( RowSeq ) WITH FILLFACTOR = 100
);
GO
IF SUSER_SNAME() NOT LIKE 'DOMAIN\%'
BEGIN
INSERT #Error
( Item ,
Msg
)
VALUES ( N'Login' ,
'Use Windows authentication only'
);
END;
I have been looking at execution plan on SQL Sentry Plan Explorer, but I am fresh to all this and from the knowledge I do not see any red flags.
October 27, 2016 at 7:13 am
Out of interest ... Temp tables get dropped automatically by the system ... why are you trying to do this manually?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 27, 2016 at 7:16 am
I suggest that you replace these rows
EXEC dbo.s_DropTempDBTable '#Error';
GO
with this
IF OBJECT_ID('tempdb..#error', 'U') IS NOT NULL
DROP TABLE #error;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 27, 2016 at 7:20 am
This is actually part of a larger query that is returning a result set for my users, being tested and rerun currently. There are actually 3 temp tables in the query and this #Error temp table is used in try catches to table multiple errors and then display later if there is a row in the #Error's table and then rollback the transaction if so.
October 27, 2016 at 7:24 am
Yeah that does work I know that. The s_DropTempDBTable was created because it would standardize the beginning of queries I have to run for LOTS of queries I am tasked to run. So I was looking for a solution to the why the execution of the is off. But you have a solution!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply