Multiple Inserts in the Same Stored Procedure

Hey,
I have a project that I have been working on and I need to insert a
record into multiple tables and I if any one of the inserts fails I
need to rollback all of the previous inserts that were done. To
illustrate, I have ten tables that need to have a record inserted into
them and if it errors out on table six, then I want to rollback the
previous five inserts. I'd appreciate any advice I can get. Thanks.bradley.d.walker@.gmail.com wrote:
> Hey,
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>
BEGIN TRANSACTION
INSERT Table1 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table2 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table3 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
INSERT Table4 .....
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
...
...
...
...
...
...
COMMIT TRANSACTION|||With SQL 2000 -SQL 2005 is a bit 'simplier' in manifestation - but the idea
is the same.
BEGIN TRANSACTION
INSERT INTO into table1 (ColList) VALUES (ValList)
IF @.ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
INSERT INTO into table2 (ColList) VALUES (ValList)
IF @.ERROR <> 0
BEGIN
ROLLBACK
RETURN
END
{etc.}
COMMIT TRANSACTION
RETURN
There are variations to the idea, using GOTO to the end of the sproc and ROL
LBACK from there.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<bradley.d.walker@.gmail.com> wrote in message news:1151077294.100001.43410@.g10g2000cwb.goog
legroups.com...
> Hey,
>
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>|||To add to the other responses, consider adding SET XACT_ABORT ON to the
beginning of your proc. This will ensure a transaction rollback occurs in
the case of a client-initiated attention event (e.g. query timeout or
cancel).
Hope this helps.
Dan Guzman
SQL Server MVP
<bradley.d.walker@.gmail.com> wrote in message
news:1151077294.100001.43410@.g10g2000cwb.googlegroups.com...
> Hey,
> I have a project that I have been working on and I need to insert a
> record into multiple tables and I if any one of the inserts fails I
> need to rollback all of the previous inserts that were done. To
> illustrate, I have ten tables that need to have a record inserted into
> them and if it errors out on table six, then I want to rollback the
> previous five inserts. I'd appreciate any advice I can get. Thanks.
>|||>> I have a project that I have been working on and I need to insert a record [si
c] into multiple tables and I if any one of the inserts fails Ineed to rollb
ack all of the previous inserts that were done. <<
Easy enough; set up a series of INSERT INTO's in a single transaction,
trap each insertion's error and do a ROLLBACK and return if you have a
failure. Do not commit until the end of the whole thing.
The scope of transactions in T-SQL is independent of the block
structure of the language. Think of a "transaction guy" with a bucket
of data looking at a house. The house pumps data into his bucket. He
does not care what is happening inside; he is waiting to see a COMMIT
or ROLLBACK flag come out of the window of the house. At that point,
he either throws the data out or throws it in the database.
But a better question why do you want to store the same data in
multiple tables? The major reason we moved from files to RDBMS was to
get rid of redundancy -- the mantra is "one fact, one time, one way,
one place!" and not "Let's make ten copies and try to keep them all the
same!" Instead of making ten copies of a mag tape with the same data,
we use VIEWs, CTE, and derived tables in SQL.
You did know that a row is not a record in your posting or understand
transactions, makes me wonder if your schema is messed up because you
are mimicing files.|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> To add to the other responses, consider adding SET XACT_ABORT ON to the
> beginning of your proc. This will ensure a transaction rollback occurs in
> the case of a client-initiated attention event (e.g. query timeout or
> cancel).
Very interesting! I did not know about this. This can be a quick fix
for applications that suffers from unhandled query timeouts.
Why did you not tell me this before? :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Very interesting! I did not know about this. This can be a quick fix
> for applications that suffers from unhandled query timeouts.
We did exactly that recently for one of our ADO middle-tier database
application with persistent db connections. All transactions were done in
stored procedure code (no nested procs) and the application performed no
explicit transaction handling and no special database exception handling.
Database errors were logged but the app moved on, oblivious to the
ramifications.
Everything was fine until a nightly job was accidentally run during the day
and that caused blocking and subsequent command timeouts. Because the app
performed no connection cleanup after the exception, a transaction that was
in progress remained open after the timeout. All subsequent work done on a
problem connection was done in the context the open transaction and was
never committed!
In addition to adding XACT_ABORT ON quick fix, I asked the developers to
close and re-open persistent connections following any type of database
exception.

> Why did you not tell me this before? :-)
I got the idea to try XACT_ABORT ON after perusing your error handling
articles. Somehow, I got it in my mind that this technique was covered
there ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97ED135AF6FCYazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Very interesting! I did not know about this. This can be a quick fix
> for applications that suffers from unhandled query timeouts.
> Why did you not tell me this before? :-)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> Everything was fine until a nightly job was accidentally run during the
> day and that caused blocking and subsequent command timeouts. Because
> the app performed no connection cleanup after the exception, a
> transaction that was in progress remained open after the timeout. All
> subsequent work done on a problem connection was done in the context the
> open transaction and was never committed!
A really fine mess! Yes, we have experienced this in our application as
well, although that's many years behind us now. I wonder how much the
default timeout of 30 seconds have cost enterprises over the worldin
money and misery.

> In addition to adding XACT_ABORT ON quick fix, I asked the developers to
> close and re-open persistent connections following any type of database
> exception.
While better than nothing, it's not optimal, unless you already have
turned off connection pooling. Of course, if you close and reconnect
directly, and get back the same physical connection directly, everything
will be cleaned up on the spot. But if the pool gives you a different
connection, it could take 60 seconds before the rollback occurs, when
the API actually closes the connection.

> I got the idea to try XACT_ABORT ON after perusing your error handling
> articles. Somehow, I got it in my mind that this technique was covered
> there ;-)
In that case, I don't know that I write in my articles myself. But I
will have to add it!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?
Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?
|||> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files.
You can't do that without some free space. You can add a new file and shrink the original file.
Well, thinking about it, it is *possible* that the shrink can at the same time autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
The one with more free space.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.boroughkenaiaku s...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.com> said:
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE in this case. The
> database is mostly read intensive, with few writes/changes. There are two databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb used, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster database.
> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files. I can't split to new databases without breaking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?|||> What I'm trying to figure out is how to move from one database:one massive file to one[vbc
ol=seagreen]
> database:multiple small files.[/vbcol]
You can't do that without some free space. You can add a new file and shrink
the original file.
Well, thinking about it, it is *possible* that the shrink can at the same ti
me autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until
the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
The one with more free space.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.borou
ghkenaiakus...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.co
m> said:
>
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE
in this case. The
> database is mostly read intensive, with few writes/changes. There are two
databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb use
d, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster d
atabase.
> What I'm trying to figure out is how to move from one database:one massive
file to one
> database:multiple small files. I can't split to new databases without bre
aking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Multiple datasets embedded within each other..

I have a dilemma. I am creating an invoice report with a fairly
complicated hierarchy.
I have it working so far. All the invoice elements are doing fine
except the taxes. Because of business requirements, it is not possible
for me to get everything in a single dataset. My invoice may have 40
lines. Some of those lines are taxable and some are not. Taxes might
include 1 type of tax of 5 types of tax.
So i need to get the tax in a separate dataset. What I need to know is
if it is possible to embed one dataset inside another based on a
filter (the ID field used to link the taxes to a line for example).
I know I can reference certain fields in other datasets, but I have an
unknown number of tax lines I have to embed into my existing dataset
in the report. I couldn't find any information on this so if osmeone
can point me in the right direction or flat out tell me its not
possible I would appreciate it.
Thanks.What you want is to embed a subreport in a cell of the report table (or onto
your list control). This subreport can have just a single field if you want.
Have the subreport parameters (remember a subreport is just a regular
report) be whatever values you need to calculate the tax. Test the report
standalone and then drag and drop the report into the cell of the report
control (if that is what you are using). Then do a right mouse click on the
subreport and map the parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ" <resonantblue@.gmail.com> wrote in message
news:1172171435.536157.39690@.v45g2000cwv.googlegroups.com...
>I have a dilemma. I am creating an invoice report with a fairly
> complicated hierarchy.
> I have it working so far. All the invoice elements are doing fine
> except the taxes. Because of business requirements, it is not possible
> for me to get everything in a single dataset. My invoice may have 40
> lines. Some of those lines are taxable and some are not. Taxes might
> include 1 type of tax of 5 types of tax.
> So i need to get the tax in a separate dataset. What I need to know is
> if it is possible to embed one dataset inside another based on a
> filter (the ID field used to link the taxes to a line for example).
> I know I can reference certain fields in other datasets, but I have an
> unknown number of tax lines I have to embed into my existing dataset
> in the report. I couldn't find any information on this so if osmeone
> can point me in the right direction or flat out tell me its not
> possible I would appreciate it.
> Thanks.
>

Multiple Database hits vs Bulk Data parameters

I was curious to know if it the amount of data sent to the sql server mattered.I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.Thanks

For SQL Server, the cost of parsing parameters can always be ingonred compared with the cost of compiling a store procedure and producing a optimized execution plan for it.

From network I/O aspect, each execution of a stored procedure is considered as a 'batch' (batch in SQL means all data will be processed and returned at one time, which can greatly improve network performance). So if you use 3 stored procedures, you need 3 rounds; while 1 stored procedure need only 1 round.

So generally I suggest you use 1 stored procedure with 12 parameters.

Multiple Browser Connection Problem

I'm having problems working on a DNN Site. I'm doing all the dev work local
on my machine. I've got SQL 2005 Developer Edition. I'm running a DNN site on
the local IIS. I've enabled both tcp and named pipe connections and I've
started the browser service. The web config is pointed to Server=(local).
Simply put I get the message
The page cannot be displayed
There are too many people accessing the Web site at this time.
I realize they don't want people running big-ol' web sites on this but come
one. I've simply got 3 browser windows open. I've got 2 on the local machine
and then I use remote desktop to view the page from another machine and
logged into the site as a test user while on my local machine I'm logged in
as a superuser making changes. I don't know what the heck is going on. I
don't know if changing it to TCP only or even specifying localhost instead of
(local) in the connection string. If anyone has a fix for this please let me
know. Honestly this is pretty silly on Microsofts part. If this is just
something you can't get around, one of those By design failures, Does anyone
know if there is an upgrade I can get from dev edition to standard (or
whatever marketing is calling it this year).
hi,
NetFodder wrote:
> I'm having problems working on a DNN Site. I'm doing all the dev work
> local on my machine. I've got SQL 2005 Developer Edition. I'm running
> a DNN site on the local IIS. I've enabled both tcp and named pipe
> connections and I've started the browser service. The web config is
> pointed to Server=(local). Simply put I get the message
> The page cannot be displayed
> There are too many people accessing the Web site at this time.
as the instance you are referencing is a default instance, there's no need
of the the SQL Browser service..

> I realize they don't want people running big-ol' web sites on this
> but come one. I've simply got 3 browser windows open. I've got 2 on
> the local machine and then I use remote desktop to view the page from
> another machine and logged into the site as a test user while on my
> local machine I'm logged in as a superuser making changes. I don't
> know what the heck is going on. I don't know if changing it to TCP
> only or even specifying localhost instead of (local) in the
> connection string. If anyone has a fix for this please let me know.
> Honestly this is pretty silly on Microsofts part. If this is just
> something you can't get around, one of those By design failures, Does
> anyone know if there is an upgrade I can get from dev edition to
> standard (or whatever marketing is calling it this year).
actually I do not think the problem is SQLExpress related, as SQLExpress
does not limit this way the max number of connections to the SQL Server
instance..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||Actually I did a bunch of research and found a couple of solutions for it.
First the developer edition also does not have a limit (programatically) on
the number of connections. However, for some reason my system did. After
fixing that by uninstalling/reboot/re-installing, I ran into an IIS
limitation. Most likely because I'm working on some modules that actually use
specified user accounts for running. They work kind of the same as SQL, or
exchange where the services need a service account to run. These need a web
account to run so think of them as DNN Service Accounts. Anywho, 2 pieces
together fixed it. First I used MetaEdit to change the IIS limit from 10 to
40. Then I unchecked the box on the web site properties page for KeepAlives.
If anyone runs into this, I hope this helps.
"Andrea Montanari" wrote:

> hi,
> NetFodder wrote:
> as the instance you are referencing is a default instance, there's no need
> of the the SQL Browser service..
>
> actually I do not think the problem is SQLExpress related, as SQLExpress
> does not limit this way the max number of connections to the SQL Server
> instance..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>