May 16, 2002 at 3:07 pm
Hello all,
I am new to SQLServer and trying to create global variables. I would want some values to remain persistent since the user logged in and that could be accessed from a stored procedure or a trigger. Is there a way to do this?
Thanks.
May 16, 2002 at 3:51 pm
USe @@@ before var name in 2000
quote:
Hello all,I am new to SQLServer and trying to create global variables. I would want some values to remain persistent since the user logged in and that could be accessed from a stored procedure or a trigger. Is there a way to do this?
Thanks.
May 16, 2002 at 4:55 pm
No such critter (sorry in a mood). You can however create a global temp table (##tblname) to stored data and pass between. It will remain in affect for the duration of their connection however other users can reference it too and until either the user disappears or all open qeuries against it finish.
You other option would be to create a static table with a spid column so that you can id the connection using it. Someone posted this concept elsewhere in the forums at one point. You have to make sure when a user connects that it checks it's spid and clears the existing data for that spid as was the previous connection. Then it is a matter of building a method to communicate this data around logically. Such as using a varchar field for all data and a varchar field for type of info. SUch as you could stored the identity value from inserted in the table to tell the procedure that ran it what it was. Or persist a value for mylasttableaccessed. This would be available to all users and would allow you a global scope to move this information around.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 21, 2002 at 9:52 am
Thanks Antares. This is good idea but I can't use a global temp table because other users will be waiting to access the same table and if I use a static table with spid then the rows will be lost if i do a rollback. I want the information to be available after a rollback. Even I load a cursor before a rollback, the cursor data is lost after the rollback. Is there any other way to get the data back after a rollback?
Thanks.
May 21, 2002 at 10:07 am
Can you walk me thru your process in full. I am concerned if you keep the data even after the rollback you end up with phantom data. You can I believe use BEGIN TRANS and COMMIT TRANS to commit full transactions to the table without commiting all the transactions. And if a rollback occurrs then the commited items should remain. This would require so real planning to ensure data safety. This may offer you your solution but you may end up like I said with phantom rows of items you commited to you work table but rolledback against the main tables.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 21, 2002 at 11:55 am
I log certain information to table frequently through the whole process that should persist a rollback so that the end user can get a report on why the process failed and what stage it failed and rolled back. Based on your suggestion I think I should use begin/commit tran whenever I write to the log table so that even if the original process failed I get the logs. What do you mean by phantom data?
May 21, 2002 at 12:29 pm
Phantom data or ghost data are records that were once there but have dissapeared during execution due to rollbacks, updates or deletes, thus the output does not reflect actual data from the tables.
Ex.
I am running a query that is allowed to read uncommited data (sorta what you are doing here with log) that can rollback.
My output is all the records returned from the query but due to allowing to pick up uncommitted values when compared to the real on hand data when some records rolledback due to transaction errors I have things that do not exist.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 21, 2002 at 12:29 pm
Antares, even the begin/commit trans does not seem to work. Test the below code:
create table test(blah varchar(100))
begin tran one
declare @x varchar(10)
select @x = 'tran one'
print 'x:' + @x
begin tran two
insert into test values('This value should persist')
commit tran two
select @x = 'bb'
select * from test
rollback tran one
select * from test
print 'x:' + @x
I need the rows in table test to be available after rollback tran one. Any ideas?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply