I have migrate SQL Server 2000 database into SQL Server 2005 and in all the
stored procedures I am using SET BEGIN TRANSACTION but SQL Server 2005
support BEGIN TRANSACTION, is there any way i can resolve this issues, I
have 150 stored procedures where I am using SET BEGIN TRANSACTION

Thanks..

Rogers,

Very interesting. I didn't remember that SET BEGIN TRANSACTION was once a
legal syntax. Here are a couple of options:

1 - Have your database run in 8.0 (SQL 2000) mode and the old syntax will be
accepted.

2 - Script out all of your stored procedures to a file. Globally change all
SET BEGIN TRAN, SET COMMIT TRAN, SET ROLLBACK TRAN to BEGIN TRAN, COMMIT
TRAN, ROLLBACK TRAN. Then run the script to reapply the changes in your
database.

I personally would choose option 2 over undermining the upgrade to 2005. To
get a script in 2005, right click on the database, ignore Script Database
and choose Tasks / Generate Scripts ..., then follow the prompts.

RLF


"Rogers" <naissani (AT) hotmail (DOT) com> wrote

I agree with Russell on going with the #2 option. That is how I would
approach it.

--
Andrew J. Kelly SQL MVP

"Russell Fields" <russellfields (AT) nomail (DOT) com> wrote

Is there any way I can update all the stored procedure through specific
query like to find SET BEGIN TRANSACTION and replace with BEGI TRANSACTION

Thanks


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

I agree with Russell’s option 2.

Script out the entire stored procedure to a file and perform a search and
replace of the strings.

After you have done with the replacements, you can apply that your DB [I
would recommend a FDB backup prior to this step for any rollback purposes].
Later you can script out each stored procedure to a file [File per object]
and use it for your source code control system’s updates.


--
Manoj Kumar


"Rogers" wrote: