Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rwatson
Starting Member
4 Posts |
 Posted - 2011-03-22 : 11:06:01
|
In a Sql Server 2005 stored procedure, where a global (##) temporary table is created and dropped within a transaction block (begin/end transaction), queue access to the table by other users?The stored procedure pivots, multiple columns from an existing set of rows to columns which can be a completely different set of based on a key field in another table. It consists almost entirely of dynamic sql  . I can't use a local temporary table because there are out-of-context calls within the stored procedure which need access to the table. I want to be sure access to the global temporary table is limited to one user/process at a time.ThanksRay Watson |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
rwatson
Starting Member
4 Posts |
Posted - 2011-03-22 : 14:08:52
|
| Can it be accessed by another user (with permission) while it's in a transaction block? I read somewhere that using a transaction effectively locks it while the transaction is executing.Ray Watson |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
rwatson
Starting Member
4 Posts |
Posted - 2011-03-23 : 08:11:19
|
| I'll try...If you have data in a table with records like this:GroupBy Description Date1 Date2 TST Project Start 01/01/2011 01/02/2011 TST Permits 01/10/2011 01/11/2011 And you wanted to pivot the Description field into columns, like so:Project Start_Date01 Project Start_Date02 Permits_Date01 Permits_Date02 01/01/2011 01/02/2011 01/10/2011 01/11/2011I could pass in the groupby field (TST), the field to pivot on (Description) and the fields to include in the pivot (Date01,Date02). I would then create a temporary table of the description field values and use it to build dynamic sql to execute to pivot the rows into columns.If I wanted the stored procedure to work with any data structure I wouldn't be able to hard code the pivot table structure i.e. the description field value type. So I would use a temporary table, a global one because in creating the table I'd have to use an Exec statement, which is an out-of-context call.Once the sql was built using the pivot table data the table was dropped...all this within a transaction block.My concern was that concurrent users would cause it to blow up, one user dropping the table as another user was trying to create it.I have a work-around, using a physical table with the SPID as an additional field in the pivot table to locate the current users pivot data. But I had to hard code the description field type. I was hoping that I could use a global temporary table and dynamically build it based on args passed in. If there was a way to assure that only one user has access to the temporary table at a time.Ray Watson |
|
|
|
|
|
|
|
|
Global Temporary Tables & Transactions