Transaction advice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transaction advice

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2005-03-16 : 06:17:53

Hi all

I have the following query that basically inserts into one table and then executes another SP that inserts into another table.

The code is as follows



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


ALTER   PROCEDURE spTradeInsert
	(
	@ISIN varchar(20),
	@DealDate datetime,
	@Quantity numeric(18, 0),
	@Price numeric(18, 4),
	@Consideration numeric(18, 4),
	@BuySell varchar(1),
	@Symbol varchar(4),
	@BargainReference varchar(20),
	@Conditions varchar(10),
	@SettlementDate datetime,
	@Participant varchar(10),
	@Counterparty varchar(10),
	@Ignore bit,
	@TradeType varchar(2)
	)

AS

DECLARE @TradesID int

INSERT
	TradeHistory
	(
	ISIN,
	MessageProcessDate,
	DealDate,
	Quantity,
	Price,
	Consideration,
	BuySell,
	Symbol,
	BargainReference,
	Conditions,
	SettlementDate,
	Participant,
	Counterparty,
	Cancelled,
	Ignore,
	TradeType
	)
VALUES
	(
	@ISIN,
	GETDATE(),
	@DealDate,
	@Quantity,
	@Price,
	@Consideration,
	@BuySell,
	@Symbol,
	@BargainReference,
	@Conditions,
	@SettlementDate,
	@Participant,
	@Counterparty,
	0,
	@Ignore,
	@TradeType
	)

SET @TradesID = SCOPE_IDENTITY()

IF @Ignore = 0

	
exec spXMLOutput_Trade @TradesID = @TradesID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

The problem is that when the
exec spXMLOutput_Trade @TradesID = @TradesID runs it looks at a view that that is based on the tradehistory table. Therefore when the exec statement runs there is no row for that TradesID in the view. Is there any way I can commit the first insert so that when it gets to the exec statement the inserted TradesID will be in the view.

All help always appreciated.

OMB

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-16 : 06:55:27

Use begin and Commit Transaction

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-16 : 07:53:03

I don't think that is your problem. Without explictly using Transaction control, the insert in your spTradeInsert Sproc IS an implicit transaction. Doing a Begin and Commit shouldn't change anything. In my little test to check this, my called sp is able to locate the new value in the view. Are you sure there are no other conditions in your where clause that is filtering out the new value?


create table test1 (col1 int identity(1,1), col2 int)
go
create view vtest1 as select col1, col2 from test1
go
create proc testSP1 @col1 int as Select * from vtest1 where col1 = @col1
GO
create proc testSP2 @col2 int as 
declare @col1 int
insert Test1 (col2) values (@col2)
Select @col1 = scope_identity()
exec testSP1 @Col1 = @col1
GO


exec testSP2 @col2 = 10

GO
drop proc testSP2
go
drop proc testsp1
go
drop view vTest1
GO
drop table Test1

Be One with the Optimizer
TG

OMB
Yak Posting Veteran

88 Posts

Posted - 2005-03-16 : 07:56:51

Thanxs Madhivanan
but i have already tried that

OMB
Yak Posting Veteran

88 Posts

Posted - 2005-03-16 : 08:45:06

thanxs TG

I think the queruy might have been a red herring. The query runs fine from query analyser, in the live envireonment the same query is executed fom a Vb app.

Does any one know the connection string that QueryAnalyser uses or how query analyser connects to the database. I think it may have something to do with some of the values in my connectionstring.

here it is. It's very basic



Provider=SQLOLEDB;Data Source=SQL02;User ID=x;Password=x

thanxs OMB

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-16 : 08:50:24

Check the value of your @Ignore parameter. If its null or 1, this won't fire: exec spXMLOutput_Trade @TradesID = @TradesID

Be One with the Optimizer
TG

OMB
Yak Posting Veteran

88 Posts

Posted - 2005-03-16 : 08:56:00

TG

the value for @ignore is always set correctly.

The SP does actually run it's just that it adds null to all the rows in the inserted table which illustrates that it cannot find the Tradesid in the view. it's baffling because this works fine in QA but not from the app.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-16 : 09:10:25

ODBC:
"Driver={SQL Server};Server=myServer;Database=pubs;Uid=sa;Pwd=sucka;"

OLEDB:
"Provider=sqloledb;Data Source=myServer;Initial Catalog=pubs;User Id=sa;Password=sucka;"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-16 : 09:13:41

That IS weird. Have you connected to sql (QA window) using the same login that your app runs under and tried that. I'm wondering if there is a permission issue and the error is getting buried somewhere. Also check the event log to see if any errors are being thrown that your app is not catching.

Be One with the Optimizer
TG

OMB
Yak Posting Veteran

88 Posts

Posted - 2005-03-21 : 03:17:13

Sorry for the delay in getting back. found out what was causing the problem but cannot explain the fix any one have any ideas?

The code from the app set the dateformat to dmy before executing the Main SP. when we removed this and put it within the SP it worked but we had one more problem we had to set the dateformat back to mdy. it works but i hav eno explanatioon why.

I have attcahed the part of the code that I'm refering to.

[code]

ALTER PROCEDURE spTradeInsert
(
@ISIN varchar(20),
@DealDate varchar(30),
@Quantity numeric(18, 0),
@Price numeric(18, 4),
@Consideration numeric(18, 4),
@BuySell varchar(1),
@Symbol varchar(4),
@BargainReference varchar(20),
@Conditions varchar(10),
@SettlementDate varchar(30),
@Participant varchar(10),
@Counterparty varchar(10),
@Ignore bit,
@TradeType varchar(2)
)

AS

DECLARE @TradesID int
DECLARE @DealDateDate datetime
DECLARE @SettlementDateDate datetime

SET DATEFORMAT dmy
SET @DealDateDate = CONVERT(datetime, @DealDate)
SET @SettlementDateDate = CONVERT(datetime, @SettlementDate)
SET DATEFORMAT mdy

Any Ideas ?

Thanxs
OMB

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-21 : 09:12:47

~~SET DATEFORMAT dmy~~
SET @DealDateDate = CONVERT(datetime, @DealDate, 104)
SET @SettlementDateDate = CONVERT(datetime, @SettlementDate, 104)
~~SET DATEFORMAT mdy~~

???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources