Local Temp tables versus Global

I have a SP that dynamicly constructs a sql phrase that is executed with the
execute keyword correctly.
One of the variables is the name of a Global temporary table created on the
fly as follows
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.##CC' + Rtrim(@.sCustomerNo) + @.strUniqueTable
Then I execute the following
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
I had some concurrency issues and wanted to switch to Local Temporary Table,
so as soon as I take off one of the '#' signs, execute ther SP I get the
following error :
---
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#CCOI154315_61466018841ML160'.
----
When re-putting the second # sign, the SP works like a charm
Al I missing something regarding local temporary tables
Thanks in advanceHi
The creation of the table is not in the same scope as the executed SELECT
statement. You may want to execute them together.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>I have a SP that dynamicly constructs a sql phrase that is executed with
>the
> execute keyword correctly.
> One of the variables is the name of a Global temporary table created on
> the
> fly as follows
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.##CC' + Rtrim(@.sCustomerNo) + @.strUniqueTable
> Then I execute the following
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
>
> I had some concurrency issues and wanted to switch to Local Temporary
> Table,
> so as soon as I take off one of the '#' signs, execute ther SP I get the
> following error :
> ---
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#CCOI154315_61466018841ML160'.
> ----
> When re-putting the second # sign, the SP works like a charm
> Al I missing something regarding local temporary tables
> Thanks in advance|||Thansk for your response. As I said they are in the same SP.
Can you please explain what do you mean by ' not in the same scope '.
As I understand from what is indicated in BOL that nested SP in SP can call
local temporary tables :
--
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. The table can be
referenced by any nested stored procedures executed by the stored procedure
that created the table. The table cannot be referenced by the process which
called the stored procedure that created the table.
--
My understanding is that second level sp can execute sql phrases against LTT
in hosting SP, No?
"John Bell" wrote:

> Hi
> The creation of the table is not in the same scope as the executed SELECT
> statement. You may want to execute them together.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>
>|||One last thing I forgot to add, how do you execute both in the same scope?
can youn please show me?
Thansk in advance
"John Bell" wrote:

> Hi
> The creation of the table is not in the same scope as the executed SELECT
> statement. You may want to execute them together.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:2A3F3044-D1E7-4FCB-9896-C18EEC95A207@.microsoft.com...
>
>|||Hi
I used scope instead of session. You can use the temporary tables in called
procedures but you can't create the temporary table in one sub-procedure and
then use it in the next it has to be created at the higher level.
If you posted and example that works when you use a global temporary table
and fails when local it may help.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:B4CAD9DF-83B2-4111-AFDB-F96C27DDF7A2@.microsoft.com...
> Thansk for your response. As I said they are in the same SP.
> Can you please explain what do you mean by ' not in the same scope '.
> As I understand from what is indicated in BOL that nested SP in SP can
> call
> local temporary tables :
> --
> A local temporary table created in a stored procedure is dropped
> automatically when the stored procedure completes. The table can be
> referenced by any nested stored procedures executed by the stored
> procedure
> that created the table. The table cannot be referenced by the process
> which
> called the stored procedure that created the table.
> --
> My understanding is that second level sp can execute sql phrases against
> LTT
> in hosting SP, No?
> "John Bell" wrote:
>|||here we go
Here is 2 SPs, thje first one woth 2 '#' signe the second with one
I provided a create table which you can insert the following item
"3C17203-ME" in for test
purposes
For testing the SPs call them as follows (I have taken a lot of info and
joins from the original one)
bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs 'C ', '018841ML', 'EUR',
'3C17203-ME', 'OI154315_61466'
bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign 'C ', '018841ML', 'EUR',
'3C17203-ME', 'OI154315_61466'
The table used by the simplified PROC
CREATE TABLE [dbo].[PRODUCT_NG] (
[PROD_MANF_SKU] [varchar] (20)
) ON [PRIMARY]
GO
SP with 2Signs
---
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.##CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = " FROM PRODUCT_NG "
Set @.sWhereStart = " "
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
Rtrim(@.sSQL)
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
EXECUTE ("DROP TABLE " + @.lstr_sTableName)
GO
---
The single sign proc
---
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = 'dbo.#CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = " FROM PRODUCT_NG "
Set @.sWhereStart = " "
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
Rtrim(@.sSQL)
EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
@.lstr_sTableName + " " + @.sFromWhereStart )
Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
EXECUTE ("DROP TABLE " + @.lstr_sTableName)
GO
---
"John Bell" wrote:

> Hi
> I used scope instead of session. You can use the temporary tables in calle
d
> procedures but you can't create the temporary table in one sub-procedure a
nd
> then use it in the next it has to be created at the higher level.
> If you posted and example that works when you use a global temporary table
> and fails when local it may help.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:B4CAD9DF-83B2-4111-AFDB-F96C27DDF7A2@.microsoft.com...
>
>|||Hi
This may wrap but try:
CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
@.sCompanyNo varchar(2),
@.sCustomerNo varchar(8),
@.sCurrency char(3),
@.sSQL varchar(20),
@.sTableName varchar(255)
--@.WareHouse char(3)="11"
AS
SET NOCOUNT ON
Declare @.sFrom varchar(700)
Declare @.sWhereStart varchar(700)
Declare @.sFromWhereStart varchar(1900)
Declare @.lCount varchar(5)
Declare @.intCount int
Declare @.WEBGroup char(3)
Set @.WEBGroup = 'WEB'
Declare @.lstr_sTableName varchar(255)
declare @.strUniqueTable varchar(15)
Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as varchar(2) )
+ Cast(DATEPART (millisecond , getdate()) as varchar(3) )
Set @.lstr_sTableName = '#CC' + Ltrim(Rtrim(@.sTableName)) +
Rtrim(@.sCustomerNo) + @.strUniqueTable
--Print @.lstr_sTableName
--Return
SET @.sSQL = REPLACE(@.sSQL,'"','''')
Set @.sFrom = ' FROM dbo.PRODUCT_NG '
Set @.sWhereStart = ' '
Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
--Rtrim(@.sSQL)
EXEC ('BEGIN CREATE TABLE ' + @.lstr_sTableName + ' ( [fC_ManfSKU] [varchar]
(20), [oC_PricePub] int ) INSERT INTO ' + @.lstr_sTableName + ' (
[fC_ManfSKU], [oC_PricePub] ) SELECT PROD_MANF_SKU, 0 ' + @.sFromWhereStart
+ ' DROP TABLE ' + @.lstr_sTableName + ' END')
GO
The BEGIN and END show the scope for the temporary table.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:BDC195B7-536E-4F47-A41E-BAE856520A3F@.microsoft.com...
> here we go
> Here is 2 SPs, thje first one woth 2 '#' signe the second with one
> I provided a create table which you can insert the following item
> "3C17203-ME" in for test
> purposes
> For testing the SPs call them as follows (I have taken a lot of info and
> joins from the original one)
> bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs 'C ', '018841ML', 'EUR',
> '3C17203-ME', 'OI154315_61466'
> bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign 'C ', '018841ML', 'EUR',
> '3C17203-ME', 'OI154315_61466'
>
> The table used by the simplified PROC
> CREATE TABLE [dbo].[PRODUCT_NG] (
> [PROD_MANF_SKU] [varchar] (20)
> ) ON [PRIMARY]
> GO
>
> SP with 2Signs
> ---
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_2Signs
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.##CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = " FROM PRODUCT_NG "
> Set @.sWhereStart = " "
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> Rtrim(@.sSQL)
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
> Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
> EXECUTE ("DROP TABLE " + @.lstr_sTableName)
> GO
> ---
> The single sign proc
> ---
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = 'dbo.#CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = " FROM PRODUCT_NG "
> Set @.sWhereStart = " "
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> Rtrim(@.sSQL)
> EXECUTE ("SELECT PROD_MANF_SKU AS fC_ManfSKU, 0 as oC_PricePub into " +
> @.lstr_sTableName + " " + @.sFromWhereStart )
> Exec ("Select oC_PricePub, fC_ManfSKU from " + @.lstr_sTableName)
> EXECUTE ("DROP TABLE " + @.lstr_sTableName)
> GO
> ---
> "John Bell" wrote:
>|||Hi
To add...
There should be no need to worry about creating a unique name as this will
be done for you. This would then allow you to create the temporary table as
normal e.g.
CREATE TABLE #tmp1 ( id int not null identity, col1 varchar(10))
INSERT INTO #tmp1 ( col1 ) EXEC ('SELECT ''abc'' AS col1 UNION ALL SELECT
''def''')
SELECT * FROM #tmp1
DROP TABLE #tmp1
Your contention may be from using SELECT ... INTO as this may lock the
system tables in tempdb for a prolonged period. This was certainly an issue
on older versions of SQL Server and is a good practice to avoid.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:O5mwLfwsFHA.1328@.TK2MSFTNGP10.phx.gbl...
> Hi
> This may wrap but try:
> CREATE PROCEDURE bask_GetPriceForOneItem_VerSigNewsGroupe
s_1Sign
> @.sCompanyNo varchar(2),
> @.sCustomerNo varchar(8),
> @.sCurrency char(3),
> @.sSQL varchar(20),
> @.sTableName varchar(255)
> --@.WareHouse char(3)="11"
> AS
> SET NOCOUNT ON
> Declare @.sFrom varchar(700)
> Declare @.sWhereStart varchar(700)
> Declare @.sFromWhereStart varchar(1900)
> Declare @.lCount varchar(5)
> Declare @.intCount int
> Declare @.WEBGroup char(3)
> Set @.WEBGroup = 'WEB'
> Declare @.lstr_sTableName varchar(255)
> declare @.strUniqueTable varchar(15)
> Select @.strUniqueTable = Cast(DATEPART (second , getdate()) as
> varchar(2) )
> + Cast(DATEPART (millisecond , getdate()) as varchar(3) )
> Set @.lstr_sTableName = '#CC' + Ltrim(Rtrim(@.sTableName)) +
> Rtrim(@.sCustomerNo) + @.strUniqueTable
> --Print @.lstr_sTableName
> --Return
> SET @.sSQL = REPLACE(@.sSQL,'"','''')
> Set @.sFrom = ' FROM dbo.PRODUCT_NG '
> Set @.sWhereStart = ' '
> Set @.sFromWhereStart = Rtrim(Rtrim(@.sFrom) + Rtrim(@.sWhereStart) ) --+
> --Rtrim(@.sSQL)
> EXEC ('BEGIN CREATE TABLE ' + @.lstr_sTableName + ' ( [fC_ManfSKU]
> [varchar] (20), [oC_PricePub] int ) INSERT INTO ' + @.lstr_sTableName +
> ' ( [fC_ManfSKU], [oC_PricePub] ) SELECT PROD_MANF_SKU, 0 ' +
> @.sFromWhereStart + ' DROP TABLE ' + @.lstr_sTableName + ' END')
> GO
> The BEGIN and END show the scope for the temporary table.
> John
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:BDC195B7-536E-4F47-A41E-BAE856520A3F@.microsoft.com...
>|||So many thanks for your suggestion. As I said I have taken a lot of the T-SQ
L
off to make it simple.
The 'Select Into.....' phrase is followed by several dynamic sql as follows
EXEC ("Update ......bla bla " + mytemporaryTable + "blah bla ......"
So If I follow your first suggestion it means I shoyuld concatenate a ton of
sql phrases in one hus EXEC 'Begin ...END' which would be a pain for
debugging.
Of course the seond suggestion can not be used because it is straight T-SQL
and not dynamic
I would appreciate any other ideas or fixes if possible of course.
Salam
"John Bell" wrote:

> Hi
> To add...
> There should be no need to worry about creating a unique name as this will
> be done for you. This would then allow you to create the temporary table a
s
> normal e.g.
> CREATE TABLE #tmp1 ( id int not null identity, col1 varchar(10))
> INSERT INTO #tmp1 ( col1 ) EXEC ('SELECT ''abc'' AS col1 UNION ALL SELECT
> ''def''')
> SELECT * FROM #tmp1
> DROP TABLE #tmp1
> Your contention may be from using SELECT ... INTO as this may lock the
> system tables in tempdb for a prolonged period. This was certainly an issu
e
> on older versions of SQL Server and is a good practice to avoid.
> John
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:O5mwLfwsFHA.1328@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi
The second suggestion is that the table is static but the SQL is dynamic, I
have never found a application that really required dynamic table
definitions. You may want to re-consider the design of the process and the
reasons you think it has to be dynamic.
You may also want to read http://www.sommarskog.se/dyn-search.html to see if
there is anything that can be used.
John
"SalamElias" <eliassal@.online.nospam> wrote in message
news:2D75B7F3-930F-4E6D-8255-56C05E898E97@.microsoft.com...
> So many thanks for your suggestion. As I said I have taken a lot of the
> T-SQL
> off to make it simple.
> The 'Select Into.....' phrase is followed by several dynamic sql as
> follows
> EXEC ("Update ......bla bla " + mytemporaryTable + "blah bla ......"
> So If I follow your first suggestion it means I shoyuld concatenate a ton
> of
> sql phrases in one hus EXEC 'Begin ...END' which would be a pain for
> debugging.
> Of course the seond suggestion can not be used because it is straight
> T-SQL
> and not dynamic
> I would appreciate any other ideas or fixes if possible of course.
> Salam
> "John Bell" wrote:
>