Can I have Exec(...) statements within a transcation ?

Is the following T-SQL correct, in which I am trying to make sure that th effect of 3 stored procedures gets reversed in case of an error ? I know if in place of stored procedures I had action queries like 'ActionQry1', 'ActionQry2' and 'ActionQry3' then the transaction logic would work. But will it work even if exec(...) statements are there in the transaction ? Each stored procedure is made up of an action query.

begin tran
exec("storeprocedure1('2')")
if @.@.error=0
begin
exec("storeprocedure2")
if @.@.error=0
begin
exec("storeprocedure3(122)")
if @.error=0
commit tran
else
rollback tran
end
else
rollback tran
end
else
rollback transure, exec statements will work,
but your syntax is not correct, it should be like this:

 begin tran
 statement1
 if (@.@.error <> 0)
 begin
 rollback tran
 return
 end
 statement2
 if (@.@.error <> 0)
 begin
 rollback tran
 return
 end
 commit tran

Can I Alter A Report that has already been deployed to my Report Manager

I have some OLAP reports in which I have to alter the MDX statements each month, in the VS2005 report designer. They are deployed to the web and they contain Descriptions and Subscriptions which I create once they get to the web. The way I am currently updating them each month is by editing the reports in VS2005 and re-deploying them to the Reports Server. I then have to add the descriptions again and create new Subscriptions because the orig reports are deleted when the new ones, with the same name are deployed. There has to be a way that I can just alter the existing reports in the web environment and not have to recreate the Subscriptions each month. Any help would be appreciated.

If you go to the actual report that you want to update in report manager and go to the general tab there is a button that says update that you can select and then upload the new version. Then all of your descriptions and your history will remain and you just upload the new version of the report.

Properties-General-Update-Do not forget to hit apply when finished choosing rdl file.

Can Add/update/drop column sql statements in one store procedure?

I have a batch sql statments to add column NewCol into TableA, then update
NewCol at this TableA, then drop column NewCol from TableA. I have no proble
m
to run them as individual sql statment with GO after each statment. But when
I tried to put these sql statements into one store procedure, each time when
compile or run the store procedure, I got error 'Invalid column name'. I
guess column NewCol is not there at beginning. So Can I put Add/update/drop
column in one store procedure?
alter procedure xash
AS
-- other sql regarding to this TableA --
--alter add column--
alter table TableA add NewCol varchar(1) null
-- update new column--
Update TableA
set NewCol= 'Y'
--other operation related this table and new column--
--drop column --
alter table TableA drop COLUMN NewCol
GOYou have to commit the work at each step before the next step can see
it. There is lots of overhead for this.
I have to ask why you are doing this. Instead of having physical
storage hold your computed values, wouldn't it be better to simply have
a VIEW with the computed column in it? Also why do you want to use
VARCHAR(1); think about it.
CREATE VIEW FilteredFoobar (..)
AS
SELECT ..., CASE WHEN <<test here>> THEN 'Y' ELSE 'N' END AS new-col
FROM Foobar, ..
WHERE ..;|||On 13 Apr 2005 16:18:15 -0700, --CELKO-- wrote:

> Also why do you want to use VARCHAR(1); think about it.
You know, it would be nice if SQL implementations could just throw an error
message if you try to create a VARCHAR(1) column. :)