Temporary tables

[Shrek]

I have a stored sript that works across a number of different databases at the clients sites. In order for this to work the script creates a global temporary table that is calibrated according to the servers/databases regional settings

SET @DatabaseCollation = convert(sysname,DatabasePropertyEx(db_name(),'Collation'))
SET @SQL = 'CREATE TABLE ##TableStructure (TableName varchar(100) COLLATE ' + @DatabaseCollation + ....... )
EXECUTE (@SQL)
 

The client now wants to run this script across different databases at the same time, which causes errors with the global temporary tables. Solution change my global temp table to local temp tables...

SET @DatabaseCollation = convert(sysname,DatabasePropertyEx(db_name(),'Collation'))
SET @SQL = 'CREATE TABLE #TableStructure (TableName varchar(100) COLLATE ' + @DatabaseCollation + ....... )
EXECUTE (@SQL)
 

Problem is that the script now fails with a message Invalid object name '#TableStructure'.

[ck9663]

It's because a local temporary table is created per instance/connection unless you drop it.

If you have three connections each creating a temporary table, they can only see the table that they created. You might need to do some re-design on your app.

-- CK