|
|
Posted by Erland Sommarskog on 11/05/07 22:31
D. (d@d.com) writes:
>> o Remove all "go" in the script, and wrap most statements in EXEC.
>> The way script lookas as generated, if there is a batch-aborting
>> error, the transaction is rolled back, and the rest of the statements
>> will be committed. An alternative is to wrap all batches in
>> IF @@transcount > 0 BEGIN END.
>
> I must remove all "go" statements because I'll execute the code via jdbc
> driver.
> I've not understood what do you mean with the phrase "wrap most statements
> in EXEC" (sorry but I'm fairly new to SQLServer)
If you from a client, it's actually easier. Don't remove the "go" in
the sense that you send the entire script at once, but execute each
batch separately, and abort the whole affair if there is an error.
Don't forget to add a IF @@trancount > 0 ROLLBACK TRANSACTION in this case.
The full story is this: when there is an error in an SQL command, several
things can happen, depending on the error, and many of there errors
when you work with DDL abort the batch and rollback the transaction.
Batches is separated by the "go". This means that if one batch fails
and aborts the transaction, and you run the script from a query window,
the remaining statements will still be executed - but without a transaction.
The stuff about EXEC is that instead of "ALTER TABLE ...", you need
EXEC('ALTER TABLE ...')
This is because if you put all in one batch, the script may not compile,
because it may refer to columns that has not been created yet etc.
But since you run from JDBC, you should not need this.
> I' also noticed that SSMS, before the ALTER TABLE statement put always
> some instructions (in the automated generated SQL) that I don't
> unserstand well...
>
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> GO
> ALTER TABLE xxxxxxx
>
> Do you think that I have to execute them from my java program or can I
> start directly with ALTER TABLE instruction?
Include them. Some of these settings are save with the table and per
columns. Many of these settings are already in effect, but they do
not make any harm.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|