You might have to start a distributed transaction since you're stretching the transaction across databases.
-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