Multi-Step Transaction, fails but reports success

I have for example the following SQL:
BEGIN TRAN
Delete from users where userid = 102
Update mainSettings set userCnt = UserCnt -1
COMMIT TRAN
If for example the "update" statement fails, but the "delete" statement
is successful - the overall transaction reports no error, and thus, my
appliction thinks everything was a success.
Any ideas?
VB 6.0
MDAC ADO 2.7
JasonAnswered in microsoft.public.sqlserver.programming
Please do not multi-post.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||You have to check for @.@.error after every statement that insert / delete or
update
the db. Also, you have to check the return value of the sp.
create procedure proc1
@.userid
as
set nocount on
declare @.error int
BEGIN TRAN
Delete from users where userid = @.userid
set @.error = @.@.error
if @.error != 0
begin
rollback transaction
raiserror('Error deleting from table users.', 16, 1)
return 1
end
Update mainSettings set userCnt = UserCnt -1
if @.error != 0
begin
rollback transaction
raiserror('Error updating from table [mainSettings].', 16, 1)
return 1
end
COMMIT TRAN
return @.@.error
go
declare @.rv int
declare @.error int
declare @.tc int
set @.tc = @.@.trancount
exec @.rv = proc1 102
set @.error = coalesce(nullif(@.rv, 0), @.@.error)
if @.error != 0
begin
print 'there was an error.'
end
if @.@.trancount != @.tc
rollback transaction
go
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"jroozee@.gmail.com" wrote:
> I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>|||Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
server return a error?
Jason|||Read the articles. There you will find the answer.
AMB
"jroozee@.gmail.com" wrote:
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Same reason, last statement in the batch was successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<jroozee@.gmail.com> wrote in message
news:1112043813.998362.180420@.g14g2000cwa.googlegroups.com...
> Ok, forget the BEGIN/COMMIT...Even with out that, why wouldnt SQL
> server return a error?
> Jason
>|||Additionally, the reason you must check for the error, is that the SQL
programmers can not make the decision for you as to whether or not the error
is sufficient to roll back the transaction. That is for you to decide, so
you much catch the errors and rollback the transaction when necessary.. (
There are types of errors which will automatically roll back the transaction
without your intervention. These are generally more severe errors which
cause batch abort, or you have been chosen as a deadlock victim.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jroozee@.gmail.com> wrote in message
news:1112042240.645733.295030@.f14g2000cwb.googlegroups.com...
>I have for example the following SQL:
> BEGIN TRAN
> Delete from users where userid = 102
> Update mainSettings set userCnt = UserCnt -1
> COMMIT TRAN
>
> If for example the "update" statement fails, but the "delete" statement
> is successful - the overall transaction reports no error, and thus, my
> appliction thinks everything was a success.
> Any ideas?
> VB 6.0
> MDAC ADO 2.7
> Jason
>

multiplue UI Report Tables using exact same dataset ?

Is there an easier way to manage the table UI element on the report. For
example in my report I have a table for the header info and the data, the
data uses maybe 10 columns and we could be perhapps adding more columns
(fields) to it in the future. The header uses 4 columns. It's a pain
because everytime I add a column to the main report details I have to go up
and fix the header and merge the appropriate cells to get it back to 4 cells
in the header. Is there not a way to use multiple table elements but they
both use the same dataset,. When I say same dataset, I dont mean a copy of
the same dataset, I want it to use the same dataset without the query
running twice for each table.
You guys know what I mean?
Possible ?Sorry, I'm not sure what you mean!
Is the table header a seperate table from the 'data' table?
Are they using the same dataset or not?
If the header and data are in fact seperate tables, then why are you
doing it like that, you seem to be making extra work for yourself!
If two tables use the same dataset the query would only be run once.
Chris
D Witherspoon wrote:
> Is there an easier way to manage the table UI element on the report.
> For example in my report I have a table for the header info and the
> data, the data uses maybe 10 columns and we could be perhapps adding
> more columns (fields) to it in the future. The header uses 4
> columns. It's a pain because everytime I add a column to the main
> report details I have to go up and fix the header and merge the
> appropriate cells to get it back to 4 cells in the header. Is there
> not a way to use multiple table elements but they both use the same
> dataset,. When I say same dataset, I dont mean a copy of the same
> dataset, I want it to use the same dataset without the query running
> twice for each table.
> You guys know what I mean?
> Possible ?

Multiple values into single cell

Taking the Northwind database as an example,
I have an order table: 'Orders'
a order details table: 'Order Details'
and a products table: 'Products'

For reasons best ignored, I want to produce a query which has columns:

Orders.OrderID, Products

which has results like:

10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella di
Giovanni'
10249, 'Tofu, Manjimup Dried Apples'

________
so for those who don't really know what I'm on about and haven't got
access to northwind, I want the second cell to contain the returned
column of a second query (but in text format)

could anyone suggest a way this could be done? am I entering the land
of cursors?

Many thanks,
NeilNAJH (neilonusenet@.yahoo.co.uk) writes:

Quote:

Originally Posted by

For reasons best ignored, I want to produce a query which has columns:
>
Orders.OrderID, Products
>
>
which has results like:
>
10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella di
Giovanni'
10249, 'Tofu, Manjimup Dried Apples'
>
________
so for those who don't really know what I'm on about and haven't got
access to northwind, I want the second cell to contain the returned
column of a second query (but in text format)
>
could anyone suggest a way this could be done? am I entering the land
of cursors?

If you are on SQL 2000, yes. SQL Server MVP Anith Sen covers the various
methods on http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
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|||

Quote:

Originally Posted by

Quote:

Originally Posted by

>For reasons best ignored, ... <<

Do not ignore the reasons; so, why do you want to write stinking bad
code?|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>For reasons best ignored, ... <<

>
Do not ignore the reasons; so, why do you want to write stinking bad
code?

At least he isn't talking about storing the data that way, merely
outputting it that way. That said, I would do it in a separate
reporting layer (e.g. Crystal Reports) if at all possible.|||On May 21, 3:27 am, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>For reasons best ignored, ... <<

>

Quote:

Originally Posted by

Do not ignore the reasons; so, why do you want to write stinking bad
code?

>
At least he isn't talking about storing the data that way, merely
outputting it that way. That said, I would do it in a separate
reporting layer (e.g. Crystal Reports) if at all possible.

I know it's bad practice to do it that way and a seperate reporting
layer like crystal reports etc would be lovely, but I'm in a situation
where I don't have those kind of facilities and budgets at my
disposal. It's just a means to an end. I'm certainly not holding data
like that. It's bad enough dealing with internal politics without
having to deal with it again on here! Thanks to you all for your kind
(or not so kind) input.

Neil

Multiple updates on multiple conditions?

Hi,

I'm looking for a way, if possible, to update a field to different values based on different criteria. For example, Field X should be A if condition 1 is true, it should be B if condition 2 is true, etc. I'm not sure if there's a way to use a CASE in an UPDATE statement?

Thanks

Maybe something like:

update urTable
set X = case when condition_1 = target1 then A
when condition_2 = target2 then B
else C
end
where filterColumn = someCondition

Multiple Search Criteria using FREETEXTTABLE function

Hi,

How would one allow for possible multiple search criteria using FREETEXTTABLE. For example, my table "listings" has a full-text search catalog, and I may want to:

SELECT * FROM listings WHERE name LIKE @.name AND city LIKE @.city

I've got it working to select only by name:

SELECT listing_id, RANK, name, address, city, zip, heading, phone
FROM listings a,
FREETEXTTABLE(listings, name, 'FormsOf(INFLECTIONAL, taxes accounting') b
WHERE [KEY] = a.listing_id
ORDER BY RANK DESC, name

How would I modify this query to also search for @.city from the city column?

Thanks!

Caseyhttp://msdn2.microsoft.com/en-us/library/ms177652.aspx
hope this link helps you.

Multiple Search

Does anyone know how to search for more than one key word
at a time in MS Access 2000?
Example: Cars or Trucks (in a Memo field)Please post this to an MS Access newsgroup.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Mike" <mike@.countrymedic.com> wrote in message
news:037e01c33f3a$6e3695f0$a101280a@.phx.gbl...
Does anyone know how to search for more than one key word
at a time in MS Access 2000?
Example: Cars or Trucks (in a Memo field)

Multiple Same indexes same table

I was looking at Northwind and noticed that some of the tables have the same
index, but different names on the same table.
For example, on the Orders table you have the following script:
****************************************
************************************
************
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL ,
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO
CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON
[PRIMARY]
GO
CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO
CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON
[PRIMARY]
GO
CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO
CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO
CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON
[PRIMARY]
GO
****************************************
************************************
**
CustomerID and CustomerOrders are the same as are EmployeeID and
EmployeesOrders.
Why would you have this?
This would seem to create more overhead for the system.
Just curious.
Thanks,
Tom>I was looking at Northwind and noticed that some of the tables have the same index, but di
fferent
>names on the same table.
<snip>
> Why would you have this?
Because you don't know what you are doing. ;-)
Honestly, there was some type of confusion in the build scripts for the Nort
hwind database. You
don't create two same type of indexes on the same column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eSmBW3BpFHA.3304@.tk2msftngp13.phx.gbl...
>I was looking at Northwind and noticed that some of the tables have the sam
e index, but different
>names on the same table.
> For example, on the Orders table you have the following script:
> ****************************************
**********************************
**************
> CREATE TABLE [dbo].[Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmployeeID] [int] NULL ,
> [OrderDate] [datetime] NULL ,
> [RequiredDate] [datetime] NULL ,
> [ShippedDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [Freight] [money] NULL ,
> [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
> GO
> CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
> GO
> CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
> GO
> CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
> GO
> CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
> GO
> CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
> GO
> CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
> GO
> CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
> GO
> ****************************************
**********************************
****
> CustomerID and CustomerOrders are the same as are EmployeeID and Employees
Orders.
> Why would you have this?
> This would seem to create more overhead for the system.
> Just curious.
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23q$RciCpFHA.496@.TK2MSFTNGP10.phx.gbl...
same index, but different
> <snip>
> Because you don't know what you are doing. ;-)
> Honestly, there was some type of confusion in the build scripts for the
Northwind database. You
> don't create two same type of indexes on the same column.
I guess I do know what I am doing, since I asked the question :).
I figured there was something wrong, but I wanted to make sure I wasn't
missing something.
Thanks,
Tom
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:eSmBW3BpFHA.3304@.tk2msftngp13.phx.gbl...
same index, but different
****************************************
************************************
************
,
NULL ,
[PRIMARY]
[PRIMARY]
[PRIMARY]
[PRIMARY]
****************************************
************************************
**
EmployeesOrders.
>