SQL Server Performance

Global Temp Tables

Discussion in 'General Developer Questions' started by danny123, Sep 24, 2008.

  1. danny123 New Member

    Hi there,
    In one of my stored procedure i am creating and populating the global temp table and then using that table in join with other tables to get the results. I am gettin the desired results but query is ending with error. Below is my query and Error i am getting
    SELECT DISTINCT A.Prefix,A.FirstName,A.MiddleInitial,A.LastName,A.Suffix,A.Email,A.Address,A.City,A.State,A.Zip
    ,ER.EventDate,AI.*
    FROM tblAccount A WITH(NOLOCK) JOIN tblEvent ER WITH(NOLOCK) ON (A.AccountID = ER.AccountFK) join ##tblAccountInfo AI ON (cast(AI.AccountID as int) = ER.AccountFK)
    WHERE ER.EventID = @EventID
    And this is the error i am getting:
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'WITH'.
    Msg 105, Level 15, State 1, Line 1
    Unclosed quotation mark before the character string '[Code 495]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)'.
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'WITH'.
    Msg 105, Level 15, State 1, Line 1
    Unclosed quotation mark before the character string '[Code Code 496]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)'.
    And so on for all the columns in Temp table.
    This query works perfectly fine when i run outside the stored procedure by running each step manually.
    Please advice.
    Thanks!!
  2. Adriaan New Member

    What happens if you drop the WITH (NOLOCK) hints?
    Not quite sure here, but the character strings from the error messages do not appear in the query statement that you posted. Do they appear in the full script of the sproc?
  3. danny123 New Member

    Thanks for quick response!
    1. I tried to remove NOLOCK hint and that did not help. I am still getting the same error.
    2.Yes ,character string are getting used in procedure. I am renaming the columns of ##tblAccountInfo just before the query. For that i am using the dynamic sql. Below is the code.
    declare @Key int
    declare @Name varchar(100)
    declare @SqlRename varchar(8000)
    declare C_Rename cursor for
    select FieldCustomFK ,FieldLabel
    from #tblTempFieldKey
    open C_Rename
    Fetch next from C_Rename into @Key,@Name
    while @@fetch_status = 0
    begin
    Set @SqlRename = 'exec tempdb..sp_rename ''##tblAccountInfo.[' + cast(@Key as varchar(100)) + ']'',''['
    If Len(cast(@Name as varchar(100))) > 15
    Set @SqlRename = @SqlRename + Substring(cast(@Name as varchar(100)),1,15) + ' ' + cast(@Key as varchar(100)) + ']'',''Column'''
    else
    Set @SqlRename = @SqlRename + Substring(cast(@Name as varchar(100)),1,15) + ' ' + cast(@Key as varchar(100)) + ']'',''Column'''
    --print @SqlRename
    exec (@SqlRename)
    Fetch next from C_Rename into @Key,@Name
    end
    close C_Rename
    deallocate C_Rename
    This is the Message in regard to this script when this part of procedure runs.
    Caution: Changing any part of an object name could break scripts and stored procedures.
    The Column was renamed to '[Country Code 708]'.
    And so on for all the columns.
    Please advice!
    Thanks!!
  4. Adriaan New Member

    Ah - the joys of dynamic SQL ...
    I notice this small debug line -
    --print @SqlRename
    Use it, and see where the quotes in the statement are not coming out right.
    ****
    Do ask yourself why you would ever need to rename a column - reasons against that in this specific example:
    (1) This is a temp table, so you have control over the column names anyway.
    (2) This is a global temp table, so you should not change column names. The same table is accessible from any other connection to this server instance, which might run into error as it would expect different column names.
    (3) You can simply add aliases to your queries against this table, to get different column names on the results.
  5. danny123 New Member

    Thats why i put this print command in there so that i can test the dynamic sql before using it.
    This is what i get
    exec tempdb..sp_rename '##tblAccountInfo.[495]','[Country 495]','Column'
    exec tempdb..sp_rename '##tblAccountInfo.[496]','[Country Code 496]','Column'
    and so on for all the columns. I dont see any issues with this. Can you please mention if i am missing something.
    1. While creating the temp table i had to use the FieldID rather than FieldName as column names because we have fields with same label but different IDs.
    2. This query will be running only on request and very rarely. So chances of conflict are very less.
    3.Aliases in this case are not feasible as this temp table columns are different each time its running.
    Thanks for the response.
    Please advice where i am missing something in the renaming of the columns.
  6. Adriaan New Member

    Tried to edit this message, but a new one was created instead. And now I cannot delete this message ...
  7. Adriaan New Member

    Your final query statement would be something like this:
    SELECT [495] AS [Country 495], [496] AS [Country Code 496]
    FROM ##tblAccountInfo
    You know how to generate dynamic SQL (you're already calling sp_rename through dynamic SQL) so you have the complete framework at hand to generate a query statement with aliases.
    ***
    By the way, the errors are probably popping up due to the square brackets around the column names on the parameters for sp_rename.
    In plain SQL, you need the square brackets around numeric column names, and around column names with blanks, but not when feeding the name as a parameter.
    To be honest, this is rather messy in a few respects. Preferably, column names should not consist of only numbers, and should not contain blanks. You're running into this problem because of poor choices earlier in the design of this procedure.
  8. danny123 New Member

    Thanks for all your help. You almost saved my life :)
    I know that this is not one of the best designs, but given the situations it was necessity to use that way. Situation was something like :
    To register for a event user had to fill a form. In that form user can create their own custom fields and uset them.
    Now there can be custom fields with same label but different ID
    One user can signup for multiple events and so can create multiple fields.
    client wanted to get one row per user for specific event where (if any ) custom fields become the column heading and custom fields become the value of those columns.
    This might give some idea but there are some complexities with in database design too which needed to be addressed.
    But you really saved my life, i was going crazy about whats wrong with the rename procedure.
    Thank alot again.
  9. Adriaan New Member

    I almost saved your life - so you died? [H]
    There are proper ways of organizing that sort of data, trust me.

Share This Page