Re: Add a coulm in a table, after a specific column


o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

Ok, I have to execute the DDL statements via jdbc so Iìll put them inside a
unique transaction.


o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

Ok


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)


o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.

Ok, I'll do.

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?

Thanks,
D.


.

Relevant Pages

  • Re: fork() slowness.
    ... "transaction" in the middle. ... real script that's giving me trouble. ... To further confuse the issue, ... > Because you are using wall time to compute your intervals, ...
    (comp.lang.perl.misc)
  • Re: can not find the relationship in 2005
    ... The transaction scope is wacko. ... In the generated script, the tranxaction spans multiple batches. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • Re: advanced question - xlock hint on record not locking select * from table
    ... (You can add DBCC OPENTRAN before or after your EXECUTE sp_lock to ... Your first script has exclusive lock only on a part of the PK ... For your second script, ... So these two transaction do not interfere and you are getting ...
    (microsoft.public.sqlserver.server)
  • Re: Copy data from flat table into multiple tables
    ... > back the transaction and exit the loop. ... The problem that I'm having is that the script doesn't make ... >>open csrUsers ...
    (microsoft.public.sqlserver.programming)
  • Re: advanced question - xlock hint on record not locking select * from table
    ... > * from sametable (also using serializable transaction level) and this ... starts a connection and selects a record using the xlock hint ... > opened to run the second script. ... > insert into bla values ...
    (microsoft.public.sqlserver.programming)