Is transaction name has to be unique?

Hi

I remember that once I had a problem when using a cursor in a sp and when several instances of the sp were running I had a problem when the first sp in the sequence deallocated the cursor and all the other who run in parallel had errors...
Well, this is not the problem now, but my question is, if I have a sp that has begin tran t1, and several instances of the sp are running in parallel, and each of course has begin tran t1, should I expect the same collision effect like with the cursor? Is every tran has to be with unique name? Or maybe the server knows how to manage this and when one tran has started and another sp tried to start another with the same name it makes it wait until the first one committed or rolled back?

Thanks,
Inon.I would think that this would be a particularly bad idea, though I have no specific experience in this area (transaction numbers). As I always understood it, the idea behind an explicitly identified transaction was to be able to roll back that specific transaction, particularly in an asynchronous environment. You have to ask yourself, what is the value in re-using the same transaction identifier? If you are just going to re-use the same identifier, then why bother with an identifier at all?

Regards,

hmscott

Is this possible?

I have a procedure that uses a cursor. I want to use this procedure to build a table based on the cursor. How can I dynamically generate a table the will contain the results. I plan on executing this procedure to populate this table every week.
Thanks...create procedure...

declare my_cursor cursor for
select ...

open my_cursor
fetch next ... into <variables list>

while @.@.fetch_status = 0 begin
insert <table_name> values (<variables list>)
fetch next ... into ...
end

close my_cursor
deallocate my_cursor

returnsql

Is this possible without a cursor?

I have something like

update table

set field = ...

where field = ...

and for each entry that was effected by this query I want to insert an entry into another table.

I have always done this with cursors is there a more effecient way? For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...

im not sure if this is what you want but maybe something like this... well first of all you cant do two updates on two tables in the same query so you would have to do it in a sproc (am i right?)

so it would be like

updated table2

set field = "my child"

where field in (select field1

from table1

where field = "something")

update table 1

set field ="my parent"

where field = "something"

is that right? sorry i dont know the answer off the top of my head just trying to help

|||

I believe you are looking for the output clause of the update statement:

USE AdventureWorks;GODECLARE @.MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDateINTO @.MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDateFROM @.MyTableVar;GO--Display the result set of the table.--Note that ModifiedDate reflects the value generated by an--AFTER UPDATE trigger.SELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO