question

ZhengShi-1782 avatar image
0 Votes"
ZhengShi-1782 asked Yufeishao-msft commented

MSSQL 2019 standard get error when create index on temp table

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BjoernPeters avatar image
0 Votes"
BjoernPeters answered OlafHelper-2800 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

have the permission to create a table in that given database?

It's a local #temp table, automatically created in system database TempDB and every user/login has permissions to create them

See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15
=> Temporary Tables => Permissions: "Any user can create global temporary objects. Users can only access their own objects, unless they receive additional permissions."
0 Votes 0 ·
ZhengShi-1782 avatar image
0 Votes"
ZhengShi-1782 answered OlafHelper-2800 commented

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?


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Check your database collation in each instance:

 SELECT   COLLATION_NAME,*
 FROM sys.Databases


0 Votes 0 ·

Can anyone explain when MSSQL sensitive the table name?

Has still nothing with the Edition to do.

The SQL Server is installed with a case sensitive Server collation; you have to use the right cases for your query.
See https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15
0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered

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://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered OlafHelper-2800 commented

Hi @ZhengShi-1782,

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://docs.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.








· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

It won't help to change the default collation of a user database.
#temp table are created in system database TempDB and so the server collation takes effect.
1 Vote 1 ·