By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,414 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,414 IT Pros & Developers. It's quick & easy.

Temporary tables

P: 13
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
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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.