locked
If the ##Table exists, drop it... How to write this command? RRS feed

  • Question

  • :
    <header>

     Hi,

    I use SAP B1 SQL   (T-SQL)

    </header>
    <section id="j-main">
    -----------------------
    <section>

    I have the following code (in a Stored Procedure) which works well.

    One thing which works, but not very well is the line highlighted in green.

    I want SQL to check if the table exists before dropping it.

    Can anyone help me write this line correctly?

    I have included some background information (OPTIONAL READING) at the bottom in case you want to know my thought processes.

    Thanks

    Leon Lai

     

    -- TO CREATE A UNIQUE ##TABLE FOR EACH WORKSTATION

    -- THIS ##TABLE WILL BE DROPPED AT THE BEGINNING OF EACH SESSION

    -- IN EFFECT IT WILL BEHAVE LIKE A #TABLE

      -- DECLARE A VARIABLE TO CONTAIN THE NAME OF THE TABLE

      declare @mytable varchar(30)

      

    -- CREATE THE NAME OF THE ##TABLE

      set @mytable ='##'+'Table'+ HOST_ID()

    --  print @mytable

    -- CHECK IF THE ##TABLE ALREADY EXISTS - IF YES, DROP IT

     

    -- Drop table ##Table10316                                                             /* this works */

    -- if exists (select * from  ##Table10316) drop table ##Table10316    /* this works */

    --  if exists (select * from  @mytable) drop table @mytable                /* this does not work*/

       

    execute ('drop table ' + @mytable)

    -- CREATE THE TEMPORARY TABLE

          execute ('create table '+ @mytable +   '(column1 integer)')

       -- INSERT 2 ROWS IN THE TABLE

      

      execute ('insert into ' + @mytable +   ' values(1234)')

      execute ('insert into ' + @mytable +   ' values(6789)')

        -- SELECT FROM THE TEMPORARY TABLE

         execute ('select column1 from '+ @mytable )

    --------------------------------

    Background Info (in brief)

    I am developing a comprehensive Crystal Report for tracking inventory.

    It consists of a Main Report + a dozen subreports.

    All the tables needed by the main and all subreports come from a single procedure (with 1 input parameter)

    The SP will generate numerous Tables - one for each subreport.

    I use this approach so that all reports are synchronised.

    My problem:

    If I use LOCAL temporary tables (#Table1), these tables are automatically dropped once the SP has finished executing and there are no tables for the subreports.

    If I use GLOBAL temporary tables (##Table1), there will beconflict among the numerous users who use my report at the same time.

    So, I create kind of hybrid table which is a ##Table but behaves like a #Table in that it is not visible to other users, because it has the Host ID in it.

    This hybrid ##Table should be dropped at the beginning, and a new one created.

    --------end --------

    </section>
    </section>
    Thursday, November 1, 2012 2:18 PM

Answers

  • IF OBJECT_ID('##tmp') IS NOT NULL
       DROP TABLE ##tmp

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Proposed as answer by Naomi N Thursday, November 1, 2012 3:29 PM
    Thursday, November 1, 2012 2:22 PM
    Answerer
  • Try
    IF EXISTS (
    SELECT *
    FROM sys.tables
    WHERE name='#temp')
    DROP TABLE #temp


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:03 PM
    Thursday, November 1, 2012 2:26 PM
  • Leon try this

    declare @mytable varchar(30)
     set @mytable ='##'+'Table'+ HOST_ID()
     exec ('if object_id('''+@mytable+''') is null  drop table '+@mytable+'')
    


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:52 PM
    Thursday, November 1, 2012 3:19 PM
  • Worked for me, e.g.

    DECLARE @CMD VARCHAR(200)
    	,@myTable VARCHAR(30)
    
    SET @mytable = '##' + 'Table' + RTRIM(HOST_ID())
    SET @CMD = 'IF OBJECT_ID(''tempdb..' + @mytable + ''',N''U'') IS NOT NULL
    			DROP TABLE ' + @myTable
    
    PRINT @cmd


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:33 PM
  • You need to issue 


    use tempdb

    declare @mytable varchar(30)
    set @mytable ='##'+'Table'+ HOST_ID()


    exec (' create table '+@mytable+' (c int);select * from '+@mytable+'; if object_id('''+@mytable+''') is not null
          drop table '+@mytable+'; select * from '+@mytable+'')


    (0 row(s) affected)
    Msg 208, Level 16, State 0, Line 2
    Invalid object name '##Table3712'.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:33 PM
    Answerer
  •   DECLARE @mytabletrim VARCHAR(MAX)
      SET @mytabletrim= RTRIM(@mytable)
      DECLARE @str NVARCHAR(MAX);
      SET @str='IF OBJECT_ID(''tempdb..'+@mytabletrim +''')'+'IS NOT NULL '+
      'BEGIN
      DROP TABLE '+@mytabletrim+
      ' END' 
    --PRINT @str;
      EXEC sp_ExecuteSQL @str;

    Try this and should work for you


    Narsimha

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:35 PM
  • Don't put space between single quotes before tempdb. N'U' is a type, just to make sure we're checking for the user defined table. N' indicates the nvarchar. This is extra, the original code will work without it, but I wanted to add it to make sure we're checking for the table and not for any object.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:59 PM
  • Why do you keep adding spaces which we didn't have? Now you added an extra space after N' Again, '' is the same as a single quote, you need to double it in a string. So, no spaces in these double single quotes. Also, keep the space after word table, e.g.

    DROP TABLE  '  + @myTable


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, November 1, 2012 4:15 PM
    • Marked as answer by Leon Lai Friday, November 2, 2012 6:05 AM
    Thursday, November 1, 2012 4:14 PM
  • Can you post the whole code snippet? At least now we know that your cmd was being executed, but it doesn't produce the desired result. I need to see the whole code now to see why it's not working.

    Also, what is the reason of creating a global temp table? 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, November 1, 2012 4:41 PM
  • Before you actually execute the statements, I'd suggest that you print them to see if they look correct. If you PRINT both the "IF OBJECT_ID...." command and your CREATE TABLE command, then you can run them one by one to and then check where it goes wrong.

    Also, as Naomi suggest, please give us the full query you are running so we don't have to guess what you are doing. Clearly, since it's working fine for us you must be doing something else when you run the full script.

     

    Steen Schlüter Persson (DK)

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:05 PM
    Thursday, November 1, 2012 8:43 PM
  • IF OBJECT_ID('tempdb..##Table3332') IS NOT NULL 
    BEGIN 
                DROP TABLE ##Table3332    
    END 
    


    Narsimha

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:50 PM
    Thursday, November 1, 2012 2:27 PM
  • Hi Uri,

    Thanks for your reply.

    When I run my SP with your line added, it cannot delete the table ##Table10316.

    This table is not hard coded, but generated on the fly, and is created by the variable @mytable.

    The Table name is a concatenation of a hard coded name + the Host ID of the user running the report.

    Hope you can suggest something else

    Best Regards

    Leon Lai

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:56 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:06 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 2:38 PM
  • Hi Manish,

    I do not want my command to delete ALL ##Temp tables.

    I want it to delete ONLY the table created for the user each time he runs my report.

    My table name is not hardcoded, but is a concatenation of a hard-coded name + the user's Terminal Id.

    e.g say ##Table10316.

    When the user reruns the report, I want only ##Table10316 to be dropped.

    It is then immediately recreated.

    I use this trick so that each user has a unique ##table which will not conflict with ##tables of other users running the same report at the same time

    Thanks

    Leon Lai

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:56 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 2:45 PM
  • Hi Naarasimha,

    Thanks for your reply.

    My table name, say ##Table10316 is NOT hard-coded, but generated on the fly when the user runs the report.

    It is a concatenation of a hardcoded value + the User's Terminal ID

    So, your code would need to be parametrised also

    Best Regards

    Leon Lai

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 2:50 PM
  • declare @mytable varchar(30)
    set @mytable ='##'+'Table'+ HOST_ID()


    exec ('if object_id('''+@mytable+''') is not null
          drop table '+@mytable+'')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, November 1, 2012 2:51 PM
    Answerer
  • You'll have to create the DROP statement dynamically.

    Something like this might work -

    DECLARE @CMD VARCHAR(200), @myTable VARCHAR(30)
    
    set @mytable ='##'+'Table'+ RTRIM(HOST_ID())
    
    
    SET @CMD = 'IF OBJECT_ID(''tempdb..'+@mytable+''') IS NOT NULL
    			DROP TABLE '+@myTable
    			


    Steen Schlüter Persson (DK)

    • Proposed as answer by Naomi N Thursday, November 1, 2012 3:32 PM
    • Unproposed as answer by Leon Lai Friday, November 2, 2012 4:50 PM
    • Marked as answer by Leon Lai Friday, November 2, 2012 4:50 PM
    Thursday, November 1, 2012 2:57 PM
  • Hi Uri,

    exec ('if object_ID(' ' '+@mytable+ ' ' ') is not null drop table '+ @mytable +' ')

    When I run the SP, the ##Table10316 is not dropped.

    Error Msg:

    Msg 2714, Level 16, State 6, Line 1

    There is already an object named '##Table10316' in the database.

    Maybe a small error in the line. It seems logical.

    Could you please recheck the code?

    Thanks

    Leon

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:06 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 3:06 PM
  • You need to specify that you want to look for the OBJECT_ID in Tempdb. The above won't fint the table hence it will not delete it. Also, the HOST_ID() function adds a number of empty spaces at the end, so it might be worth adding RTRIM before using the ID. Otherwise you'll have to be consistent in using the name with the emtpy spaces at the end.

    You can look at my earlier reply to see the code. 

     

    Steen Schlüter Persson (DK)

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:03 PM
    Thursday, November 1, 2012 3:15 PM
  • Hi Steen,

    Thanks for your reply.

    There is a small syntax error here:

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near ' TEMPDB..'.

    SET @CMD ='IF OBJECT_ID (' ' TEMPDB..' + @mytable + ''  ') IS NOT NULL DROP TABLE '+ @mytable

    Please recheck

    Thanks

    Leon

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:04 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 3:22 PM
  • Hi Naomi,

    SET @CMD ='IF OBJECT_ID ('  'TEMPDB..'

    + @mytable + ' ' ', N' 'U'')

    IS NOT NULL DROP TABLE ' + @mytable

    If it worked for you, maybe you made a typing error, or maybe me. Please check.

    I get this error message:

    Msg 102, Level 15, State 1, Procedure usp_LK_temptable, Line 13

    Incorrect syntax near 'TEMPDB..'.

    COULD you tell me what are N and U ?

    ' ' ', N' 'U'')

    Thanks

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 3:55 PM
  • SET @CMD ='IF OBJECT_ID (''TEMPDB..' + @mytable + ''',N' 'U'')IS NOT NULL DROP TABLE' + @mytable

    I have removed all spaces.

    Now, I get this error message:

    Incorrect syntax near 'U')IS NOT NULL DROP TABLE '.

    What mistake did I make this time?

    Why do you put 2 .. after TEMPDB?

    Why error message says near 'U')  and not  near 'U'') ?

    SET @CMD ='IF OBJECT_ID (''TEMPDB..'

    Thanks

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 4:12 PM
  • Hi Naomi,

    SET @CMD ='IF OBJECT_ID(''TEMPDB..' + @mytable + ''',N''U'')IS NOT NULL DROP TABLE ' + @mytable

    Now, there is this error msg:

    Msg 2714, Level 16, State 6, Line 1

    There is already an object named '##Table10316' in the database.

    It seems that the code does not drop my table.

    Please do not give up. Maybe I made another mistake. I double and multiple checked.

    Thanks

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 4:31 PM
  • Good Morning Naomi,

    I finally managed to make your code work! Wow!

    I had omitted the execute (@CMD)

    ALTER PROCEDURE [dbo].[usp_temptable]

    AS

       declare @CMD varchar(200) ,@mytable varchar(30)

       set @mytable ='##'+'Table'+ RTRIM(HOST_ID())

       SET @CMD ='IF OBJECT_ID(''TEMPDB.dbo.' + @mytable +  ''' )IS NOT NULL     DROP TABLE ' + @mytable

        execute (@CMD)

       execute ('create table '+ @mytable +   '(column1 integer)' )

       execute ('insert into ' + @mytable +   ' values(1234)')

       execute ('insert into ' + @mytable +   ' values(6789)')

       execute ('select column1 from ' + @mytable )

    It now works like a charm!

    Why I want to create a GLOBAL temp table?

    Part of the reason is explained in my original question (in the footnote).

    I am using this trick to create a table that will have the benefits of both Global and Local temp tables, without the disadvantages of either.

    Another reason is that I am developing a Crystal report from the SQL result set, and I want ALL my subreports to be synchronised (generated at the same instant of time).

    So, all my tables are generated by a single Stored Procedure.

    If I use Local Temp Tables, unfortunately all tables will drop as soon as the SP has executed, and I won't have any data for my reports.

    If I use Global Temp Tables, unfortunately, there will be conflict when many network users work on my report at the same time.

    My workaround will permit me to make Global tables behave like local tables.

    SO, Thanks a lot for your patience and generous help.

    And sorry for the spaces between the ' '.

    I deliberately put the spaces to make the code easier to read on the forum. Ha ha!

    Thanks to all the other persons who replied

    I greatly appreciate your help.

    But numerous replies were coming at such a fast pace that I had no time to test them all... and it was about bed-time.

    Best Regards

    Leon

    CLOSING


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 7:13 AM
    • Unmarked as answer by Leon Lai Friday, November 2, 2012 7:13 AM
    • Marked as answer by Leon Lai Friday, November 2, 2012 4:55 PM
    Friday, November 2, 2012 5:47 AM
  • Glad to hear that you got it working.

    Just as a side note, then you could end up in conflicts when using HOST_ID() to generate the table name. There could be two different users that gets the same HOST_ID and then use the same table. That will lead to some strange issues, because if user One has already generated the table when user Two runs the report, then the table from user One is dropped.

    You could enhance the uniqueness of the tablename, by e.g. using the NEWID() function to generat a unique number for the table. 


    Steen Schlüter Persson (DK)

    • Marked as answer by Leon Lai Friday, November 2, 2012 8:55 AM
    Friday, November 2, 2012 7:30 AM
  • Hi Steen,

    Thanks a lot for your info and for your concern.

    I have found that HOST_ID() is changing all the time. I don't know why?

    So, it will not be the perfect candidate for my query.

    Now, you are telling me that 2 different users may get the same HOST_ID().

    I think I'll rather use HOST_NAME() to do my job. Hope there will be no problems.

    Best Regards

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 8:52 AM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:08 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Friday, November 2, 2012 8:52 AM

All replies

  • IF OBJECT_ID('##tmp') IS NOT NULL
       DROP TABLE ##tmp

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Proposed as answer by Naomi N Thursday, November 1, 2012 3:29 PM
    Thursday, November 1, 2012 2:22 PM
    Answerer
  • Try
    IF EXISTS (
    SELECT *
    FROM sys.tables
    WHERE name='#temp')
    DROP TABLE #temp


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:03 PM
    Thursday, November 1, 2012 2:26 PM
  • IF OBJECT_ID('tempdb..##Table3332') IS NOT NULL 
    BEGIN 
                DROP TABLE ##Table3332    
    END 
    


    Narsimha

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:50 PM
    Thursday, November 1, 2012 2:27 PM
  • Hi Uri,

    Thanks for your reply.

    When I run my SP with your line added, it cannot delete the table ##Table10316.

    This table is not hard coded, but generated on the fly, and is created by the variable @mytable.

    The Table name is a concatenation of a hard coded name + the Host ID of the user running the report.

    Hope you can suggest something else

    Best Regards

    Leon Lai

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:56 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:06 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 2:38 PM
  • Hi Manish,

    I do not want my command to delete ALL ##Temp tables.

    I want it to delete ONLY the table created for the user each time he runs my report.

    My table name is not hardcoded, but is a concatenation of a hard-coded name + the user's Terminal Id.

    e.g say ##Table10316.

    When the user reruns the report, I want only ##Table10316 to be dropped.

    It is then immediately recreated.

    I use this trick so that each user has a unique ##table which will not conflict with ##tables of other users running the same report at the same time

    Thanks

    Leon Lai

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:56 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 2:45 PM
  • Hi Naarasimha,

    Thanks for your reply.

    My table name, say ##Table10316 is NOT hard-coded, but generated on the fly when the user runs the report.

    It is a concatenation of a hardcoded value + the User's Terminal ID

    So, your code would need to be parametrised also

    Best Regards

    Leon Lai

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 2:50 PM
  • declare @mytable varchar(30)
    set @mytable ='##'+'Table'+ HOST_ID()


    exec ('if object_id('''+@mytable+''') is not null
          drop table '+@mytable+'')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, November 1, 2012 2:51 PM
    Answerer
  • You'll have to create the DROP statement dynamically.

    Something like this might work -

    DECLARE @CMD VARCHAR(200), @myTable VARCHAR(30)
    
    set @mytable ='##'+'Table'+ RTRIM(HOST_ID())
    
    
    SET @CMD = 'IF OBJECT_ID(''tempdb..'+@mytable+''') IS NOT NULL
    			DROP TABLE '+@myTable
    			


    Steen Schlüter Persson (DK)

    • Proposed as answer by Naomi N Thursday, November 1, 2012 3:32 PM
    • Unproposed as answer by Leon Lai Friday, November 2, 2012 4:50 PM
    • Marked as answer by Leon Lai Friday, November 2, 2012 4:50 PM
    Thursday, November 1, 2012 2:57 PM
  • Hi Uri,

    exec ('if object_ID(' ' '+@mytable+ ' ' ') is not null drop table '+ @mytable +' ')

    When I run the SP, the ##Table10316 is not dropped.

    Error Msg:

    Msg 2714, Level 16, State 6, Line 1

    There is already an object named '##Table10316' in the database.

    Maybe a small error in the line. It seems logical.

    Could you please recheck the code?

    Thanks

    Leon

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:06 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:59 AM
    Thursday, November 1, 2012 3:06 PM
  • You need to specify that you want to look for the OBJECT_ID in Tempdb. The above won't fint the table hence it will not delete it. Also, the HOST_ID() function adds a number of empty spaces at the end, so it might be worth adding RTRIM before using the ID. Otherwise you'll have to be consistent in using the name with the emtpy spaces at the end.

    You can look at my earlier reply to see the code. 

     

    Steen Schlüter Persson (DK)

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:03 PM
    Thursday, November 1, 2012 3:15 PM
  • Leon try this

    declare @mytable varchar(30)
     set @mytable ='##'+'Table'+ HOST_ID()
     exec ('if object_id('''+@mytable+''') is null  drop table '+@mytable+'')
    


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:52 PM
    Thursday, November 1, 2012 3:19 PM
  • Hi Steen,

    Thanks for your reply.

    There is a small syntax error here:

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near ' TEMPDB..'.

    SET @CMD ='IF OBJECT_ID (' ' TEMPDB..' + @mytable + ''  ') IS NOT NULL DROP TABLE '+ @mytable

    Please recheck

    Thanks

    Leon

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:04 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 3:22 PM
  • Worked for me, e.g.

    DECLARE @CMD VARCHAR(200)
    	,@myTable VARCHAR(30)
    
    SET @mytable = '##' + 'Table' + RTRIM(HOST_ID())
    SET @CMD = 'IF OBJECT_ID(''tempdb..' + @mytable + ''',N''U'') IS NOT NULL
    			DROP TABLE ' + @myTable
    
    PRINT @cmd


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:33 PM
  • You need to issue 


    use tempdb

    declare @mytable varchar(30)
    set @mytable ='##'+'Table'+ HOST_ID()


    exec (' create table '+@mytable+' (c int);select * from '+@mytable+'; if object_id('''+@mytable+''') is not null
          drop table '+@mytable+'; select * from '+@mytable+'')


    (0 row(s) affected)
    Msg 208, Level 16, State 0, Line 2
    Invalid object name '##Table3712'.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:33 PM
    Answerer
  •   DECLARE @mytabletrim VARCHAR(MAX)
      SET @mytabletrim= RTRIM(@mytable)
      DECLARE @str NVARCHAR(MAX);
      SET @str='IF OBJECT_ID(''tempdb..'+@mytabletrim +''')'+'IS NOT NULL '+
      'BEGIN
      DROP TABLE '+@mytabletrim+
      ' END' 
    --PRINT @str;
      EXEC sp_ExecuteSQL @str;

    Try this and should work for you


    Narsimha

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:35 PM
  • Hi Naomi,

    SET @CMD ='IF OBJECT_ID ('  'TEMPDB..'

    + @mytable + ' ' ', N' 'U'')

    IS NOT NULL DROP TABLE ' + @mytable

    If it worked for you, maybe you made a typing error, or maybe me. Please check.

    I get this error message:

    Msg 102, Level 15, State 1, Procedure usp_LK_temptable, Line 13

    Incorrect syntax near 'TEMPDB..'.

    COULD you tell me what are N and U ?

    ' ' ', N' 'U'')

    Thanks

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 3:55 PM
  • Don't put space between single quotes before tempdb. N'U' is a type, just to make sure we're checking for the user defined table. N' indicates the nvarchar. This is extra, the original code will work without it, but I wanted to add it to make sure we're checking for the table and not for any object.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:53 PM
    Thursday, November 1, 2012 3:59 PM
  • SET @CMD ='IF OBJECT_ID (''TEMPDB..' + @mytable + ''',N' 'U'')IS NOT NULL DROP TABLE' + @mytable

    I have removed all spaces.

    Now, I get this error message:

    Incorrect syntax near 'U')IS NOT NULL DROP TABLE '.

    What mistake did I make this time?

    Why do you put 2 .. after TEMPDB?

    Why error message says near 'U')  and not  near 'U'') ?

    SET @CMD ='IF OBJECT_ID (''TEMPDB..'

    Thanks

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 4:12 PM
  • Why do you keep adding spaces which we didn't have? Now you added an extra space after N' Again, '' is the same as a single quote, you need to double it in a string. So, no spaces in these double single quotes. Also, keep the space after word table, e.g.

    DROP TABLE  '  + @myTable


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, November 1, 2012 4:15 PM
    • Marked as answer by Leon Lai Friday, November 2, 2012 6:05 AM
    Thursday, November 1, 2012 4:14 PM
  • Hi Naomi,

    SET @CMD ='IF OBJECT_ID(''TEMPDB..' + @mytable + ''',N''U'')IS NOT NULL DROP TABLE ' + @mytable

    Now, there is this error msg:

    Msg 2714, Level 16, State 6, Line 1

    There is already an object named '##Table10316' in the database.

    It seems that the code does not drop my table.

    Please do not give up. Maybe I made another mistake. I double and multiple checked.

    Thanks

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:57 PM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:07 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Thursday, November 1, 2012 4:31 PM
  • Can you post the whole code snippet? At least now we know that your cmd was being executed, but it doesn't produce the desired result. I need to see the whole code now to see why it's not working.

    Also, what is the reason of creating a global temp table? 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, November 1, 2012 4:41 PM
  • Before you actually execute the statements, I'd suggest that you print them to see if they look correct. If you PRINT both the "IF OBJECT_ID...." command and your CREATE TABLE command, then you can run them one by one to and then check where it goes wrong.

    Also, as Naomi suggest, please give us the full query you are running so we don't have to guess what you are doing. Clearly, since it's working fine for us you must be doing something else when you run the full script.

     

    Steen Schlüter Persson (DK)

    • Marked as answer by Leon Lai Friday, November 2, 2012 4:05 PM
    Thursday, November 1, 2012 8:43 PM
  • Good Morning Naomi,

    I finally managed to make your code work! Wow!

    I had omitted the execute (@CMD)

    ALTER PROCEDURE [dbo].[usp_temptable]

    AS

       declare @CMD varchar(200) ,@mytable varchar(30)

       set @mytable ='##'+'Table'+ RTRIM(HOST_ID())

       SET @CMD ='IF OBJECT_ID(''TEMPDB.dbo.' + @mytable +  ''' )IS NOT NULL     DROP TABLE ' + @mytable

        execute (@CMD)

       execute ('create table '+ @mytable +   '(column1 integer)' )

       execute ('insert into ' + @mytable +   ' values(1234)')

       execute ('insert into ' + @mytable +   ' values(6789)')

       execute ('select column1 from ' + @mytable )

    It now works like a charm!

    Why I want to create a GLOBAL temp table?

    Part of the reason is explained in my original question (in the footnote).

    I am using this trick to create a table that will have the benefits of both Global and Local temp tables, without the disadvantages of either.

    Another reason is that I am developing a Crystal report from the SQL result set, and I want ALL my subreports to be synchronised (generated at the same instant of time).

    So, all my tables are generated by a single Stored Procedure.

    If I use Local Temp Tables, unfortunately all tables will drop as soon as the SP has executed, and I won't have any data for my reports.

    If I use Global Temp Tables, unfortunately, there will be conflict when many network users work on my report at the same time.

    My workaround will permit me to make Global tables behave like local tables.

    SO, Thanks a lot for your patience and generous help.

    And sorry for the spaces between the ' '.

    I deliberately put the spaces to make the code easier to read on the forum. Ha ha!

    Thanks to all the other persons who replied

    I greatly appreciate your help.

    But numerous replies were coming at such a fast pace that I had no time to test them all... and it was about bed-time.

    Best Regards

    Leon

    CLOSING


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 7:13 AM
    • Unmarked as answer by Leon Lai Friday, November 2, 2012 7:13 AM
    • Marked as answer by Leon Lai Friday, November 2, 2012 4:55 PM
    Friday, November 2, 2012 5:47 AM
  • Glad to hear that you got it working.

    Just as a side note, then you could end up in conflicts when using HOST_ID() to generate the table name. There could be two different users that gets the same HOST_ID and then use the same table. That will lead to some strange issues, because if user One has already generated the table when user Two runs the report, then the table from user One is dropped.

    You could enhance the uniqueness of the tablename, by e.g. using the NEWID() function to generat a unique number for the table. 


    Steen Schlüter Persson (DK)

    • Marked as answer by Leon Lai Friday, November 2, 2012 8:55 AM
    Friday, November 2, 2012 7:30 AM
  • Hi Steen,

    Thanks a lot for your info and for your concern.

    I have found that HOST_ID() is changing all the time. I don't know why?

    So, it will not be the perfect candidate for my query.

    Now, you are telling me that 2 different users may get the same HOST_ID().

    I think I'll rather use HOST_NAME() to do my job. Hope there will be no problems.

    Best Regards

    Leon


    xx

    • Marked as answer by Leon Lai Friday, November 2, 2012 8:52 AM
    • Unmarked as answer by Naomi N Friday, November 2, 2012 5:08 PM
    • Marked as answer by Leon Lai Saturday, November 3, 2012 2:58 AM
    Friday, November 2, 2012 8:52 AM