MSSQL 2019 standard get error when create index on temp table

Zheng Shi 21 Reputation points
2022-05-11T08:44:32.91+00:00

We have some code try to create a temp table on MSSQL with following:

CREATE TABLE #POM_read_expr_scratch2 ( auid VARCHAR(15) collate Latin1_General_BIN, aint_val INT ) ;
SELECT MAX(partition_number) FROM sys.partitions WHERE object_id = object_id( '#POM_READ_EXPR_SCRATCH2' ) HAVING MAX(partition_number) > 1;
CREATE UNIQUE INDEX re_scratch_index ON #POM_READ_EXPR_SCRATCH2 ( auid )   ;
COMMIT;

This code work with MSSQL 2016 Developer version. however when run with 2019 Standard version (DBMS VERSION:: 15.0.2000.5 RTM Standard Edition (64-bit)) we get following error:

ODBC error. SQLSTATE: 42000 Native error: 1088
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "#POM_READ_EXPR_SCRATCH2" because it does not exist or you do not have permissions.
Approx SQL was "CREATE UNIQUE INDEX re_scratch_index ON #POM_READ_EXPR_SCRATCH2 ( auid ) "

Error creating index re_scratch_index on table #POM_READ_EXPR_SCRATCH2.
*** EIM_check_error: code 1088, dbmsg='[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "#POM_READ_EXPR_SCRATCH2" >because it does not exist or you do not have permissions.'

We check the document but didnt see any restriction of creating index on temp table. Can anyone explain why could this happen?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,335 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Tom Phillips 17,721 Reputation points
    2022-05-11T14:18:46.547+00:00

    As Olaf said, you installed SQL 2019 as "case-sensitive". This is extremely unusual and not recommended. It has nothing to do with the edition or version of SQL Server.
    https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-ver15

    Also, although it has nothing to do with your problem, you should update to the current patch level of SQL 2019.
    https://support.microsoft.com/en-us/topic/kb4518398-sql-server-2019-build-versions-782ed548-1cd8-b5c3-a566-8b4f9e20293a

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 43,166 Reputation points
    2022-05-11T09:12:52.777+00:00

    Has nothing to do with the SQL Server Edition, that statement works in the free Express Edition as well.
    Are you execution the statement in one batch from your code.

    Or do you open always a new connection, execute one statement and close the connection?
    On connection close the temp table gets automatically dropped and that would explain the error "does not exists" on the next command.

    0 comments No comments

  3. Bjoern Peters 8,856 Reputation points
    2022-05-11T12:55:56.547+00:00

    Maybe the answer is here:
    because it does not exist or you do not have permissions.

    Please check if you/that user who is connecting have the permission to create a table in that given database?

    If you don't have the right to create a table, all other statements will also fail...


  4. Zheng Shi 21 Reputation points
    2022-05-11T13:05:34.473+00:00

    I think we notice an issue:
    when create temp table, we use table name #POM_read_expr_scratch2 (lower case)
    but when create index, the table name become #POM_READ_EXPR_SCRATCH2
    However, we dont understand, why in MSSQL 2016 Developer version, it report Index created successfully.
    but in MSSQL 2019 standard version, it report error, it looks the table name is sensitive in some case.
    Can anyone explain when MSSQL sensitive the table name?


  5. YufeiShao-msft 7,081 Reputation points
    2022-05-12T07:32:44.92+00:00

    Hi @Zheng Shi ,

    You can run the following query to check if a server is case-sensitive:

    SELECT SERVERPROPERTY('COLLATION')  
    

    CI= Case Insensitive
    CS=Case Sensitive

    If there is indeed a issue about case-sensitive, you can change the collation of the database to case insensitive, for example:

    If your collation is Latin1_General_CS_AS, and you want to change;

    ALTER DATABASE test2  COLLATE Latin1_General_CI_AS   
    

    If you want to change the entire server's collation, you basically need to run the installation again to rebuild the master database
    https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?redirectedfrom=MSDN&view=sql-server-ver15

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

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.