May 14, 2004 at 9:46 am
I have this procedure to update the counters in 7 companies. I do not want to run the script in each company. But with this logic, it only updates the first company. It goes through all of them, I know because I put a print and it shows that it does.
Here is the code, Please help
declare @lcdbname varchar(65),
@lcFirstDigit char(1)
select @lcFirstDigit = '4'
declare curtemp cursor for select cdbname from companies where cstatus = 'A' order by cdbname
open curtemp
FETCH NEXT FROM curtemp INTO @lcdbname
WHILE @@FETCH_STATUS =0
begin
exec ('use '+ @lcdbname)
update tables1 set ccounter1 = @lcFirstDigit+right('0000000000'+isnull(substring(ccounter1,2,9),'1'),09),
ccounter2 = @lcFirstDigit+right('0000000000'+isnull(substring(ccounter2,2,9),'1'),09)
update tables2 set ccounter1 = @lcFirstDigit+right('0000000000'+isnull(substring(ccounter1,2,9),'1'),09),
ccounter2 = @lcFirstDigit+right('0000000000'+isnull(substring(ccounter2,2,9),'1'),09)
FETCH NEXT FROM curtemp INTO @lcdbname
end
CLOSE curtemp
deallocate curtemp
GO
May 14, 2004 at 10:03 am
You must put the update inside the EXEC.
EXEC ('update what ever')
Or declare @strUp AS VARCHAR(300)
SELECT @strUp = 'Update whatever...'
EXEC (@strUp)
May 14, 2004 at 10:27 am
Thanks...
Just 2 things
1. Do you mean inside the first exec (exec ('use '+@dbname + update whatever....) or 2 separate Exec()
2. Why does it have to be, the update, in an exec()
May 14, 2004 at 12:04 pm
I meant in the first exec.
Don't know the exact explanation, but it is like it is executed in a diferent context or connection.
If you create a #temp table with EXEC (EXEC ('CREATE #TABLE...')) you won't see it in the actual connection, the same if you declare variables
EXEC ('DECLARE @test-2 AS INT SELECT @test-2 = 1')
PRINT @test-2
this will fail,
EXEC ('DECLARE @test-2 AS INT SELECT @test-2 = 1 PRINT @test-2')
This won't.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply