>You might have to start a distributed transaction since you're stretching the transaction across databases.

I don't think so, but if needed it would be automatic. From the BOL:

BEGIN TRANSACTION starts a local transaction. The local transaction is escalated to a distributed transaction if the following actions are performed before it is committed or rolled back:

An INSERT, DELETE, or UPDATE statement is executed that references a remote table on a linked server. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.


A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.
The local copy of SQL Server becomes the transaction controller and uses MS DTC to manage the distributed transaction.


>
>-Mike
>
>>>>>Thanks a lot, this is a great starting point. I was my halfway of setting these tables up, but then switched to reading some forums, etc. :)
>>>>
>>>>Actually, I didn't even think. To change to the correct database you could do:
>>>>
>>>>

>>>>IF @tcState = 'CT'
>>>>   USE CTCredit
>>>>
>>>>IF @tcState = 'MA'
>>>>   USE MACredit
>>>>

>>>>
>>>>BOb
>>>
>>>Are you sure, I have to switch? Can I use full notation instead in my queries and use USE command.
>>
>>No, you don't have to switch, you could just use the full notation to...
>>
>>IF @tcState = 'CT'
>>BEGIN
>> UPDATE CTCredit.dbo.tablename SET .....
>>END
>>
>>That would work too. You would just have to write the same SQL twice. A little more maintenance for your code. You can use the variable in the notation like this:
>>
>>UPDATE (@tcState+'Credit.dbo.tablename') like you could in foxpro. But, you could go this route, build the code into a string and then exec() it.
>>
>>BOb