467,915 Members | 1,237 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

Temporary tables

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

Expand|Select|Wrap|Line Numbers
  1. SET @DatabaseCollation = convert(sysname,DatabasePropertyEx(db_name(),'Collation'))
  2. SET @SQL = 'CREATE TABLE ##TableStructure (TableName varchar(100) COLLATE ' + @DatabaseCollation + ....... )
  3. EXECUTE (@SQL)
  4.  
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...
Expand|Select|Wrap|Line Numbers
  1. SET @DatabaseCollation = convert(sysname,DatabasePropertyEx(db_name(),'Collation'))
  2. SET @SQL = 'CREATE TABLE #TableStructure (TableName varchar(100) COLLATE ' + @DatabaseCollation + ....... )
  3. EXECUTE (@SQL)
  4.  
Problem is that the script now fails with a message Invalid object name '#TableStructure'.
Sep 3 '08 #1
  • viewed: 961
Share:
1 Reply
ck9663
Expert 2GB
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
Sep 3 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Zlatko Matić | last post: by
1 post views Thread by Stefan van Roosmalen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.