DBCC CHECKIDENT within Transaction

Hi,
I am getting problem when I use DBCC CHECKIDENT with in a Transaction.
Please advice on this.
--
Regards
SivakumarHi
Most DBCC commands must be in their own batches and can not be in User
Transactions.
Please post error messages as "I am getting problem" does not give us much
to help you with.
Regards
Mike
"Subramaniam Sivakumar" wrote:

> Hi,
> I am getting problem when I use DBCC CHECKIDENT with in a Transaction.
> Please advice on this.
> --
> Regards
> Sivakumar|||Thanks for your reply.
I am calling some SPs from one master SP. That master SP begins a
transaction. After successful execution of other SP calls then the master SP
commits the transaction. If any of the SPs fails then the master SP rollback
s
the transaction. The other SPs have some EXEC (string) commands and call som
e
SPs. Actually these SPs do some migration from one database to another
database. Some times the source database will be in linked server.
In the master SP, after the migration finished then I reset the IDENTITY
values for all tables which having the IDENTITY column.
When I executing the master SP the control is stopped when the control comes
to the command DBCC CHECKIDENT.
The code sample as like this…
CREATE MASTER_SP
( @.SRC_DB, @.TGT_DB)
AS
BEGIN
DECLARE @.a INT
SET @.a = 0
BEGIN TRANSACTION
EXEC @.a = SP1 @.SRC_DB, @.TGT_DB
-- Reset IDENTITY values using DBCC CHECKIDENT
-- ……..
IF @.a <> 0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN 1 -- ERROR
END
RETURN 0 -- SUCCESS
END
Regards
Sivakumar
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Most DBCC commands must be in their own batches and can not be in User
> Transactions.
> Please post error messages as "I am getting problem" does not give us much
> to help you with.
> Regards
> Mike
> "Subramaniam Sivakumar" wrote:
>