MSSQL 2019 standard get error when create index on temp table

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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...

Answer 4

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?

Answer 5

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.