Transcation does not execute

Question

Post reply

Transcation does not execute

tt-615680

SSCertifiable

Points: 7835
More actions
September 23, 2010 at 9:53 am

#228726

I'm trying to run the following transcation but everytime I run it, it keeps executing but nothing was happening, is there anything that I'm doing wrong?
BEGIN TRANSACTION
GO
SELECT sd.OrderID,sd.ProductID,sd.BookedDate,sd.LBooked_Date,sd.SLeadTime,sd.CustDel
INTO
#UpdatedValue
FROM supplydates sc JOIN supplychaindates sd ON sc.OrderID = sd.OrderID
WHERE
(sc.ProductID <> sd.ProductID
OR
sc.BookedDate <> sd.BookedDate
OR
sc.LBooked_Date <> sd.LBooked_Date
OR
sc.SLead_Time <> sd.SLead_Time
OR
sc.CustDel <> sd.CustDel
OR
sc.Release_Date <> sd.Release_Date
OR
sc.Print_Date <> sd.Print_Date
OR
sc.RePrint_Date <> sd.RePrint_Date
OR
sc.Cancel_Date <> sd.Cancel_Date
OR
sc.Required_Date <> sd.Required_Date
OR
sc.Earliest_Date <> sd.Earliest_Date
OR
sc.SupAckNo <> sd.SupAckNo
OR
sc.Ackduedate <> sd.Ackduedate
OR
sc.PreBookDelDate <> sd.PreBookDelDate
OR
sc.ActDelDate <> sd.ActDelDate
OR
sc.PreBookDelDate <> sd.PreBookDelDate
OR
sc.CustActDelDate <> sd.CustActDelDate)
AND
sd.Active_To IS NULL
SELECT 1, GETDATE()
COMMIT TRANSACTION
Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

What are you expecting to see?

Why are you wrapping these two statements inside a transaction?

What do you see if you remove INTO #UpdatedValue from your first query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tt-615680 SSCertifiable Points: 7835 More actions · Answer 2

Chris Morris-439714 (9/23/2010)
What are you expecting to see?
Why are you wrapping these two statements inside a transaction?
What do you see if you remove INTO #UpdatedValue from your first query?

The reason why I have the query as a transaction is because there are other transactions which run in a stored procedure, this one is part of it.

I get the same problem when I just run the query by itself.

The whole point of the query is to get all the updated data from the two tables and insert them into a temp table, I then run another transactions after that.

Thank you

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

tt-615680 (9/23/2010)
...
I get the same problem when I just run the query by itself.
The whole point of the query is to get all the updated data from the two tables and insert them into a temp table ...

It looks like the query isn't doing what you expect then. Can you provide more information, like table scripts etc? See the link in my sig for more info.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

winash SSChampion Points: 11446 More actions · Answer 4

To elaborate on what Chris mentioned - you can try to see if you're not getting data by executing your query in parts...

For e.g.: first execute this and see if it returns any data

SELECT sd.OrderID ,sd.ProductID,sd.BookedDate,sd.LBooked_Date,sd.SLeadTime,sd.CustDel

--INTO

--#UpdatedValue

FROM supplydates sc JOIN supplychaindates sd ON sc.OrderID = sd.OrderID

Then execute this (note that a bunch of stuff is commented out):

SELECT sd.OrderID ,sd.ProductID,sd.BookedDate,sd.LBooked_Date,sd.SLeadTime,sd.CustDel

--INTO

--#UpdatedValue

FROM supplydates sc JOIN supplychaindates sd ON sc.OrderID = sd.OrderID

WHERE

/*

(sc.ProductID <> sd.ProductID

OR

sc.BookedDate <> sd.BookedDate

OR

sc.LBooked_Date <> sd.LBooked_Date

OR

sc.SLead_Time <> sd.SLead_Time

OR

sc.CustDel <> sd.CustDel

OR

sc.Release_Date <> sd.Release_Date

OR

sc.Print_Date <> sd.Print_Date

OR

sc.RePrint_Date <> sd.RePrint_Date

OR

sc.Cancel_Date <> sd.Cancel_Date

OR

sc.Required_Date <> sd.Required_Date

OR

sc.Earliest_Date <> sd.Earliest_Date

OR

sc.SupAckNo <> sd.SupAckNo

OR

sc.Ackduedate <> sd.Ackduedate

OR

sc.PreBookDelDate <> sd.PreBookDelDate

OR

sc.ActDelDate <> sd.ActDelDate

OR

sc.PreBookDelDate <> sd.PreBookDelDate

OR

sc.CustActDelDate <> sd.CustActDelDate)

AND

*/

sd.Active_To IS NULL

If you get data till this point - then start executing each of the OR clauses while commenting out the rest and see at what point you stop getting data...there might an issue with the query/data which is resulting in no data being returned

tt-615680 SSCertifiable Points: 7835 More actions · Answer 5

Chris Morris-439714 (9/23/2010)
tt-615680 (9/23/2010)
...
I get the same problem when I just run the query by itself.
The whole point of the query is to get all the updated data from the two tables and insert them into a temp table ...
It looks like the query isn't doing what you expect then. Can you provide more information, like table scripts etc? See the link in my sig for more info.

Here is the table structure for both tables:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[supplydates](

[OrderID] [varchar](50) NULL,

[ProductID] [varchar](12) NULL,

[BookedDate ] [datetime] NULL,

[LBooked_Date] [datetime] NULL,

[SLead_Time] [int] NULL,

[CustDel] [datetime] NULL,

[Release_Date] [datetime] NULL,

[Print_Date] [datetime] NULL,

[RePrint_Date] [datetime] NULL,

[Cancel_Date] [datetime] NULL,

[Required_Date] [datetime] NULL,

[Earliest_Date] [datetime] NULL,

[SupAckNo] [char](15) NULL,

[Ackduedate] [datetime] NULL,

[PreBookDelDate] [datetime] NULL,

[ActDelDate] [datetime] NULL,

[PreBookDelDate] [datetime] NULL,

[CustActDelDate] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[supplychaindates](

[OrderID] [varchar](50) NULL,

[ProductID] [varchar](12) NULL,

[BookedDate ] [datetime] NULL,

[LBooked_Date] [datetime] NULL,

[SLead_Time] [int] NULL,

[CustDel] [datetime] NULL,

[Release_Date] [datetime] NULL,

[Print_Date] [datetime] NULL,

[RePrint_Date] [datetime] NULL,

[Cancel_Date] [datetime] NULL,

[Required_Date] [datetime] NULL,

[Earliest_Date] [datetime] NULL,

[SupAckNo] [char](15) NULL,

[Ackduedate] [datetime] NULL,

[PreBookDelDate] [datetime] NULL,

[ActDelDate] [datetime] NULL,

[PreBookDelDate] [datetime] NULL,

[CustActDelDate] [datetime] NULL,

[Active_From] [datetime] NULL,

[Active_To] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Thank you

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

What happens when there's a row in one table but not the other (matching on OrderID)?

Run these two queries and describe the output:

SELECT sc.OrderID, sd.OrderID

FROM supplydates sc

LEFT JOIN supplychaindates sd ON sd.OrderID = sc.OrderID

AND sd.Active_To IS NULL

SELECT sd.OrderID, sc.OrderID

FROM supplychaindates sd

LEFT JOIN supplydates sc ON sc.OrderID = sd.OrderID

WHERE sd.Active_To IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tt-615680 SSCertifiable Points: 7835 More actions · Answer 7

Chris Morris-439714 (9/24/2010)
What happens when there's a row in one table but not the other (matching on OrderID)?
Run these two queries and describe the output:
SELECT sc.OrderID, sd.OrderID
FROM supplydates sc
LEFT JOIN supplychaindates sd ON sd.OrderID = sc.OrderID
AND sd.Active_To IS NULL
SELECT sd.OrderID, sc.OrderID
FROM supplychaindates sd
LEFT JOIN supplydates sc ON sc.OrderID = sd.OrderID
WHERE sd.Active_To IS NULL

I run the following queries and they both work fine with different results, but I only need results back only that match both tables and also if any data has been updated.

SELECT sc.OrderID, sd.OrderID

FROM supplydates sc

LEFT JOIN supplychaindates sd ON sd.OrderID = sc.OrderID

AND sd.Active_To IS NULL

SELECT sd.OrderID, sc.OrderID

FROM supplychaindates sd

LEFT JOIN supplydates sc ON sc.OrderID = sd.OrderID

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Have you tried following the steps recommended by Winash?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tt-615680 SSCertifiable Points: 7835 More actions · Answer 9

Chris Morris-439714 (9/24/2010)
Have you tried following the steps recommended by Winash?

I have been trying to query the OR statements one at a time but it is still the same issue.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

tt-615680 (9/24/2010)
Chris Morris-439714 (9/24/2010)
Have you tried following the steps recommended by Winash?
I have been trying to query the OR statements one at a time but it is still the same issue.

So you get results if you run this:

SELECT sd.OrderID, sd.ProductID, sd.BookedDate, sd.LBooked_Date, sd.SLeadTime, sd.CustDel

FROM supplydates sc

INNER JOIN supplychaindates sd ON sc.OrderID = sd.OrderID

WHERE sd.Active_To IS NULL

But you get no results if you include any of the other comparisons in your where clause?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Of course, you could turn the query around and look at rows matching on orderid where the column values are the same:

SELECT sd.OrderID, sd.ProductID, sd.BookedDate, sd.LBooked_Date, sd.SLeadTime, sd.CustDel

FROM supplydates sc

INNER JOIN supplychaindates sd ON sc.OrderID = sd.OrderID

WHERE sd.Active_To IS NULL

AND (

sc.ProductID = sd.ProductID

sc.BookedDate = sd.BookedDate

sc.LBooked_Date = sd.LBooked_Date

sc.SLead_Time = sd.SLead_Time

sc.CustDel = sd.CustDel

sc.Release_Date = sd.Release_Date

sc.Print_Date = sd.Print_Date

sc.RePrint_Date = sd.RePrint_Date

sc.Cancel_Date = sd.Cancel_Date

sc.Required_Date = sd.Required_Date

sc.Earliest_Date = sd.Earliest_Date

sc.SupAckNo = sd.SupAckNo

sc.Ackduedate = sd.Ackduedate

sc.PreBookDelDate = sd.PreBookDelDate

sc.ActDelDate = sd.ActDelDate

sc.PreBookDelDate = sd.PreBookDelDate

sc.CustActDelDate = sd.CustActDelDate

)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden