January 12, 2021 at 10:03 am
-- EXEC SP_DROP ##XXX -- For older versions of SQLserver
DROP TABLE IF EXISTS ##XXX
select top 11 * INTO ##XXX from information_schema.columns
-- EXEC SP_DROP ##XXX -- For older versions of SQLserver
DROP TABLE IF EXISTS ##XXX
select top 11 * INTO ##XXX from information_schema.columns
The code above is an example of what I am trying to do in a stored procedure. With 'normal' tables this works ok. With a hashhash table this gives :
Msg 2714, Level 16, State 1, Line 21
There is already an object named '##XXX' in the database.
What would be a good work around?
Thanks for your time and attention,
Ben
(I can come up with some work arounds, like use different table names, or not use hashhash tables, but I am looking for a more elegant solution. In coding I very often create tables (##,# and 'normal') and drop and create them at 'will'. The SP_drop does effectively the same is the DROP IF EXISTS and was and is used a lot because it works in older versions as well).
January 12, 2021 at 11:17 am
I do not know the answer, but out of interest, under what circumstances do you choose to use global temp tables in preference to 'normal' session-scoped temp tables?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 12, 2021 at 2:15 pm
The problem is the parser here. You are attempting to create the same object twice in the same batch, and thus the parser sees the second attempt to create the same object (##XXX) and generates an error. It doesn't matter that the object will most certainly be dropped before hand, the fact is that you try to create it again. Also note that NONE of the batch is run; the parser sees the "error" and doesn't even attempt to execute the batch.
The "simple" solution would be to divide into 2 batches. In SSMS/ADS/sqlcmd that would be by using GO:
DROP TABLE IF EXISTS ##XXX
select top 11 * INTO ##XXX from information_schema.columns
GO
DROP TABLE IF EXISTS ##XXX
select top 11 * INTO ##XXX from information_schema.columns
This problem can actually be reproduced other ways, take for example the following:
DECLARE @I int = 1;
IF @I = 1
CREATE TABLE #X (I int);
ELSE
CREATE TABLE #X (C char(1));
This batch will produce the error below, despite that both statements can never be run in the same batch:
There is already an object named '#X' in the database.
Another method of getting around this, when not using temporary objects, is to use statements where their compilation will be deferred:
DECLARE @I int = 1;
IF @I = 1
EXEC sys.sp_executesql N'CREATE TABLE dbo.X (I int);';
ELSE
EXEC sys.sp_executesql N'CREATE TABLE dbo.X (C char(1));';
GO
DROP TABLE dbo.X;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 12, 2021 at 2:35 pm
Perhaps a synonym would work for you here.
Why do the created tables have to have the same name? Presumably it's because some common code needs to run against the tables? If so, perhaps the code could work against a synonym and the actual table name wouldn't matter.
DROP TABLE IF EXISTS #XXX1
select top (11) * INTO #XXX1 from sys.tables
create synonym xxx$ for #xxx1;
select * from xxx$;
DROP TABLE IF EXISTS #XXX2
select top (11) * INTO #XXX2 from sys.views
drop synonym xxx$;
create synonym xxx$ for #xxx2;
select * from xxx$
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 12, 2021 at 6:08 pm
I do not know the answer, but out of interest, under what circumstances do you choose to use global temp tables in preference to 'normal' session-scoped temp tables?
I like to use global temp tables. During developing/testing/debugging of stored procedures, whenever I run into a 'not completed' (or erroneous) piece of code, I can inspect or continue from another window.
Yes the disadvantage is that you can not make re-entrant code, but certainly during development I prefer that that is not possible, with local temp tables I sometimes test on a 'wrong' table.
For production code I have to do a clean-up (removing lots of comments and some test/debug code), depending on the function and use of the script I sometimes substitute the global temporary tables.
At the moment I am building two compare routines.
Main features are:
Content compare, results into OLD and NEW rows.
Code can be rerun, so tables can be 'reused'.
Reason:
We are (slowly) moving from SQLAnywhere to SQLserver.
Customers want to see prove that migration to SQLserver had gone correct and want to have prove that actions do give the same result. But also build for future functions (compare between versions etc., debugging.).
Ben
January 12, 2021 at 7:43 pm
-- EXEC SP_DROP XXX -- For older versions of SQLserver
DROP TABLE IF EXISTS XXX
select top 11 * INTO XXX from information_schema.columns
-- EXEC SP_DROP XXX -- For older versions of SQLserver
DROP TABLE IF EXISTS XXX
select top 11 * INTO XXX from information_schema.columns
The above code does not give the mentioned error. Although following the same reasoning (the same object twice in the same [stored procedre]) the parser should (???) give a similar error. I am curious about the difference (XXX or ##XXX) to the parser.
The problem is the parser here. You are attempting to create the same object twice in the same batch
The "simple" solution would be to divide into 2 batches. In SSMS/ADS/sqlcmd that would be by using
GO:DROP TABLE IF EXISTS ##XXX
select top 11 * INTO ##XXX from information_schema.columns
GO
DROP TABLE IF EXISTS ##XXX
select top 11 * INTO ##XXX from information_schema.columns
Problem: Stored procedure and in a script the @parameters do not 'survive'
This problem can actually be reproduced other ways, take for example the following:
DECLARE @I int = 1;
IF @I = 1
CREATE TABLE #X (I int);
ELSE
CREATE TABLE #X (C char(1));
Yes similar to my 'original' problem conditional creation of a table.
Another method of getting around this, when not using temporary objects, is to use statements where their compilation will be deferred:
To circumvent the problem I now use indeed a dynamic structure, but for 'fixed' table names I do not consider this elegant.
Thanks for your time and attention,
Ben
January 12, 2021 at 7:54 pm
Perhaps a synonym would work for you here.
Why do the created tables have to have the same name? Presumably it's because some common code needs to run against the tables? If so, perhaps the code could work against a synonym and the actual table name wouldn't matter.
The code picks up data from two databases and works for SQLAnywhere (A) and for SQLserver databases (B). The code for SQLAnywhere is different then from SQLserver.
I can compare A<-->A, A<-->B, B<-->A and B<--B
the data for the first part is stored in a table, the data for the second part is stored in a table. But two sets of code are used for part A (one for SQLanywhere, one for SQLserver), same for second part. After the data had been gatherered, both result tables are combined. If I use different tablesnames for each 'collection', I would need 8 tablenames and 4 differenct script for combining the tables. Now I only have two tables with source information which gets combined in a single script. This is sufficient reason for me to have tables with the same name.
Thanks for your time and attention,
Response to your anwsers has been spread over time, first because of my work second, because there was (again) a pressconference from our (cycling) prime minister. (An extention of our lockdown).
Ben
January 13, 2021 at 9:05 am
Problem: Stored procedure and in a script the @parameters do not 'survive'
Well no, but there were no parameters in your script, nor was it provided as a Procedure; I wouldn't have recommended it as a "fix" if that were the case. 🙂
Yes similar to my 'original' problem conditional creation of a table.
That was my point.
To circumvent the problem I now use indeed a dynamic structure, but for 'fixed' table names I do not consider this elegant.
It honestly looks like you have a bit of an XY Problem, if I am honest. Why are you trying to create the same object in different places in the same batch anyway? I assume it'll have the same definition, so why not create the table first, and the later use a INSERT INTO rather than SELECT ... INTO? Then you don't have 2 attempts to create the same table in the same batch, and the error doesn't occur. If it isn't going to have the same definition, then giving it the same name, in my opinion, is a bad idea, as it can easily cause other problems if you have references to the object later, and it's not the right "version" of it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 15, 2021 at 4:38 pm
Well no, but there were no parameters in your script, nor was it provided as a Procedure; I wouldn't have recommended it as a "fix" if that were the case. 🙂
I apologise that I wasn't clear, I did mention that it was code from a stored procedure and the example was not very 'functional', it was just an illustration where the code was kept to a minimum
Why are you trying to create the same object in different places in the same batch anyway?
In a previous post I tried to explain this. And because my code is often used as a tool where the future use of the tool is not defined I often take the liberty to not assuming the definition of the tables the code has to work on.
Thanks for your time and attention,
For the moment I have choosen to use dynamic code,
Ben
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply