[dba-SQLServer] What happens when

John

As far as I know with a transaction either it all suceeds or it all fails.
SO if Build Table 1 fails then the transaction fails totally.

The Begin Trans statement makes SQL Server treat everythig that follows as a
single statement hence the "all fail" if one fails.

RE statements (MSDN) all TSQL Statements with the exception of the following
can be useed inside a transaction

      ALTER DATABASE LOAD DATABASE
      BACKUP LOG LOAD TRANSACTION
      CREATE DATABASE RECONFIGURE
      DISK INIT RESTORE DATABASE
      DROP DATABASE RESTORE LOG
      DUMP TRANSACTION UPDATE STATISTICS


You should check for errors if your using an SP by testing @@Error if an
error occurs and branch out to the error handler. Put this directly
following any INSERT statement as its value is subject to change between
statement execution.

Use DTS for the data task.

I am sure you could execute the script from windows but at the command
prompt using ISQL. Never tried it via Win Explorer but bet soemone will show
a way. How about a little Bat file??

Martin










----- Original Message ----- 
From: "John W. Colby" <jwcolby at colbyconsulting.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Saturday, July 24, 2004 4:43 PM
Subject: [dba-SQLServer] What happens when


> What happens in SQL Server when a specific statement fails?
>
> I am looking at a process of building a table, then inserting data into
that
> table, building another table, placing related data into that table.  All
> using SQL.  The statements that place data in the first table require
thaqt
> the build table statement work of course, and the statements that place
data
> into the second table should not occur unless the entire first sequence
> occurs since the second table is related to the first.
>
> I understand the concept of transactions but what really happens?
>
> Psuedo code
>
> Start transaction
> Build table1
> Insert Row2
> Insert Row3
> Insert Row4
>
> Build Table2
> Insert row5
> Insert row6
> Insert Row7
> Commit
>
> 1) First, can statements that alter the database design be included inside
> of transaction statements?  Whould I need to rearrange that to:
>
> Build table1
> Build Table2
>
> Start transaction
> Insert Row2
> Insert Row3
> Insert Row4
>
> Insert row5
> Insert row6
> Insert Row7
> Commit
> 2) Suppose that Build Table1 fails?  Do the rest of the statements attempt
> to execute?  Does the process fail immediately and report an error,
refusing
> to execute any subsequent statements?
>
> 3) Suppose that Insert Row3 fails?  Do the rest of the statements attempt
to
> execute or does the process fail and rollback automatically?
>
> 4) Is my process that is executng the code responsible for checking for
> errors after each statement?
>
> 5) I have discovered how to create a script for creating the table
> structure, but how do I create a script for creating the data in the
table,
> given a table with data in it?
>
> 6) Can scripts be executed directly (from windows explorer)?  I can take a
> script and paste it into enterprise manager (query analyzer) and execute
all
> statements at one time.
>
> John W. Colby
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>