May 18, 2017 at 12:22 pm
This is a weird one. We were sure we had it working before we changed the sizes of the data types, but now it seems the EXEC statement for the dynamic SQL isn't creating our temp table. Can someone double-check our code?
IF (SELECT OBJECT_ID('Tempdb..#tmp3')) IS NOT NULL
DROP TABLE #tmp3;
DECLARE @StartingID INT = 92017;
--This variable actually selects from a different table & instance
--I hardcoded the number for convenience's sake
Declare @mysql varchar(200)=
'CREATE Table #tmp3 (PID INT,
MyTableID INT IDENTITY(' + CONVERT(varchar(10), @StartingID + ', 1),
VAcct varchar(100),
AID INT,
CreatedOn DATETIME,
SNumber varchar(100)) '
SELECT @mysql;
EXEC (@mysql);
GO
SELECT * FROM #tmp3;
The error we're getting is:
Msg 208, Level 16, State 0, Line 2
Invalid object name '#tmp3'
The weird thing is, the dynamic SQL works if we remove the # sign and make the temp table a regular table. But not if we leave it as a temp table. As I said, we're almost positive that we had it working before we fiddling with the varchar sizes to avoid some truncation issues. We even selected off the table when it was empty and after we inserted to make sure. But now it won't create the table either on my SSMS or my co-worker's SSMS. We're really confused with this.
Any thoughts?
May 18, 2017 at 12:29 pm
The temp table is created within the scope of the execution of the dynamic SQL and is not available to the outer process, because it's out of scope.
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.
May 18, 2017 at 12:29 pm
This wouldn't work.
Temporary tables created in dynamic SQL can't be referenced by the parent batch.
The reverse can be done. You can create a temporary table in the parent batch and reference it in the dynamic SQL.
Cheers!
May 18, 2017 at 12:44 pm
it should work if you make it a global temp table using ##tmp3, instead of #tmp3
May 18, 2017 at 12:54 pm
If the seed is the only that would vary, you can avoid the dynamic sql.
IF (SELECT OBJECT_ID('Tempdb..#tmp3')) IS NOT NULL
DROP TABLE #tmp3;
DECLARE @StartingID INT = 92017;
--This variable actually selects from a different table & instance
--I hardcoded the number for convenience's sake
CREATE Table #tmp3 (PID INT,
MyTableID INT IDENTITY(1, 1),
VAcct varchar(100),
AID INT,
CreatedOn DATETIME,
SNumber varchar(100))
DBCC CHECKIDENT ('tempdb..#tmp3', reseed, @StartingID)
GO
INSERT INTO #tmp3 (PID, VAcct, AID, CreatedOn, SNumber)
VALUES( 1,'A',1,GETDATE(), '31');
SELECT * FROM #tmp3;
May 18, 2017 at 4:09 pm
If you use a "sequence" object instead of an IDENTITY, there will be no need for a dynamic Temp Table nor even a need to determine what the next sequence number should be.
--Jeff Moden
May 18, 2017 at 9:07 pm
Brandie, you say you had this working before, even with the difference in scope?
May 18, 2017 at 9:44 pm
If it was working before, I have to assume that all references to the temp table were also within the dynamic sql.
DECLARE @sql VARCHAR(8000) = '
IF OBJECT_ID(''tempdb..#tmp3'', ''U'') IS NOT NULL
DROP TABLE #tmp3;
CREATE TABLE #tmp3 (
Column1 INT NOT NULL
);
INSERT #tmp3 (Column1) VALUES (1),(2),(3)
SELECT t3.Column1 FROM #tmp3 t3;'
EXEC(@sql);
May 19, 2017 at 5:10 am
Thank you everyone for your answers. They are very informative. I'll get with my co-worker and we'll discuss this (it's his code but I was advising him on the dynamic sql). You all are aces!
May 19, 2017 at 5:12 am
Jeff Moden - Thursday, May 18, 2017 4:09 PMIf you use a "sequence" object instead of an IDENTITY, there will be no need for a dynamic Temp Table nor even a need to determine what the next sequence number should be.
I do have to admit I'm not sure what you're referring to here. If I've heard of a sequence object, I've forgotten about it. What BOL keyword or Google reference should I use to find out more information about this?
May 19, 2017 at 5:17 am
Brandie Tarvin - Friday, May 19, 2017 5:12 AMJeff Moden - Thursday, May 18, 2017 4:09 PMIf you use a "sequence" object instead of an IDENTITY, there will be no need for a dynamic Temp Table nor even a need to determine what the next sequence number should be.I do have to admit I'm not sure what you're referring to here. If I've heard of a sequence object, I've forgotten about it. What BOL keyword or Google reference should I use to find out more information about this?
one article here
https://www.simple-talk.com/sql/learn-sql-server/sql-server-sequence-basics/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 19, 2017 at 5:49 am
And another here...
http://sqlhints.com/2013/04/14/sequence-in-sql-server-2012/
--Jeff Moden
May 20, 2017 at 7:14 am
And here's the BOL for creating one: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql
They've been around in Oracle ever since I started learning it and in SQL Server since 2012.
May 20, 2017 at 11:13 am
If you're not too concerned about potential performance hits, you could create a new schema for this staging data within your user database, and create your staging tables there. Then go back with a cleanup procedure to drop or truncate/reseed target tables that match the schema or some naming convention.
I didn't know about sequences either. Though I think this might be tricky to work into existing code.
May 22, 2017 at 6:01 am
caffeinated - Saturday, May 20, 2017 11:13 AMIf you're not too concerned about potential performance hits, you could create a new schema for this staging data within your user database, and create your staging tables there. Then go back with a cleanup procedure to drop or truncate/reseed target tables that match the schema or some naming convention.I didn't know about sequences either. Though I think this might be tricky to work into existing code.
I actually already have a schema for this specific project. And I am currently doing a truncate/reseed. But I want to know more about the sequence thing since I don't remember hearing about it.
Thanks everyone for the links. Reading up on it now.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply