| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2005-12-15 : 06:25:37
|
| I am running an sp which puts exception records in a table on the server. These records then become the items in a listbox to notify the user of them. My problem is that if more than one person is running this process the table will be overwritten. So I thought about a temporary table (I've never used these before). I changed my sp as below, but I get an error. Am I going about this the wrong way ?Invalid object name '#tblRB_ExceptionsBlockBooking'. Invalid object name '#tblRB_ExceptionsBlockBooking'CREATE Procedure [spRB_BuildExemptBlockBookings]@WHEREClause varchar(1000)AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(1000) -- Enter the dynamic SQL statement into the -- variable @SQLStatementSELECT @SQLStatement = "Insert #tblRB_ExceptionsBlockBooking(EX_BookingDateRef, EX_RoomRef, EX_DateRequired) select BD_BookingDateRef, BD_RoomRef, BD_DateRequired from vweBlockBookings WHERE " + @WHEREClause -- Execute the SQL statement EXEC(@SQLStatement)select * from #tblRB_ExceptionsBlockBookingGO |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-12-15 : 06:30:47
|
A #TempTable is only available while the connection That created it is still open (or until it is dropped) - so you can't just insert into the #table from a web app unless it was created in the same statement batch. So one option is to create the #temp table at the begining of your sproc and another option is to have a permanent table which also carries a field with an identifier as to which session the record belongs to.I hope that makes sense.Duane.  "It's a thankless job, but I've got a lot of Karma to burn off." |
 |
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-12-15 : 06:38:43
|
| The second option sounds simpler - how do I create an identifier as to which session ? I assume this means I know which user is using the table. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-12-15 : 06:52:23
|
| Well the second option is probably slightly more difficult.Anyways - there are a number of ways.One is by having a table with an identity column in it for each new session and then having a column on this permanent table (the one replacing the temp table) that references the identity field in the session table.when you create a new session record you can obtain the new identity vale by using something like this:SELECT @MyIDVar = @@IDENTITYImmediately after creating the new record.You can then make reference to that @MYIDVar variable when you insert into the table and when you select from it againegselect * from tblRB_ExceptionsBlockBooking where sessionID = @MYIDVar Hope this helpsDuane. "It's a thankless job, but I've got a lot of Karma to burn off." |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-12-15 : 06:56:33
|
| If I've got this right, then my identity for one user could be Jones1 and for the next record Jones2. If another user was adding to the table their records would be Harries1 and Harries2. I then extract the ones I want for each user.If the other way is simplier, how do I do that ? |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-12-15 : 07:02:46
|
| The other way would be:Just by having the create statement for your #temp table at the beginning of your SPDuane. "It's a thankless job, but I've got a lot of Karma to burn off." |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-12-15 : 07:06:17
|
| Could you edit my sp for me please. TIA |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-12-15 : 07:11:51
|
| Yes - I can - If you pay your salary into my bank account.The very first statement in the sp should be Create Table #tblRB_ExceptionsBlockBooking(col1 int, ....)Duane. "It's a thankless job, but I've got a lot of Karma to burn off." |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2005-12-15 : 09:16:12
|
| Thanks - I tried, but didn't realise I had to define the columns. I'll go to some examples somewhere. That's the sort of example I meant - I didn't mean yu to completely rewrite it.You wouldn't want my salary - they can't even afford to send me on a course - it's learn by doing and searching.Thanks once again |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-12-15 : 09:18:35
|
| Great - how about you write the sp and post it here if it still isn't working and then we can look at it.Duane. "It's a thankless job, but I've got a lot of Karma to burn off." |
|
|
|
|
How can I do this ?