April 30, 2012 at 12:22 pm
i've started the search for different rows in two tables but I'm not sure how to complete the remaining - not sure how to loop through rows in temp table to send the emails per row
WHILE EXISTS (
SELECT *
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
--returns 2 rows that are different
)
BEGIN
--PRINT 'changes found'
BEGIN
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
INTO #mytemp FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
End
BEGIN
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
--declare @BCC nvarchar(255) ='bcc'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
--get data elements for email msg
select @flightId = flightId,
@flightTime = FlightTime,
@blockTime = BlockTime,
@ttEngine1 = TTEngine1,
@tcEngine1 = TCEngine1,
@ttEngine2 = TTEngine2,
@tcEngine2 = TCEngine2,
@ttAirframe = TTAirframe,
@tcAirframe = TCAirframe
FROM #mytemp
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
end
--@blind_copy_recipients = @BCC,
END
END
PRINT 'no changes found'
oh here is my error:
(2 row(s) affected)
Mail queued.
Msg 2714, Level 16, State 6, Line 37
There is already an object named '#mytemp' in the database.
so i'm guessing it finds both rows, then it sends one email based on the last row but doesn't do so for the 1st row.
April 30, 2012 at 12:43 pm
Well thankfully you ran into an issue with the temp table already existing. You coded an infinite loop. This is one of those rare time where a cursor is actually not a bad thing. I will parse through this and see if I can put this together for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 12:48 pm
lol - thanks
I change to an inner join and did the following after the declares.
DECLARE @rowcount INT
SET @rowcount = (select @flightId = flightId,
@flightTime = FlightTime,
@blockTime = BlockTime,
@ttEngine1 = TTEngine1,
@tcEngine1 = TCEngine1,
@ttEngine2 = TTEngine2,
@tcEngine2 = TCEngine2,
@ttAirframe = TTAirframe,
@tcAirframe = TCAirframe
FROM #mytemp)
DECLARE @I INT
SET @I = 1
WHILE (@I <= @rowcount)
Begin
--get data elements for email msg
--select @flightId = flightId,
--@flightTime = FlightTime,
--@blockTime = BlockTime,
--@ttEngine1 = TTEngine1,
--@tcEngine1 = TCEngine1,
--@ttEngine2 = TTEngine2,
--@tcEngine2 = TCEngine2,
--@ttAirframe = TTAirframe,
--@tcAirframe = TCAirframe
--FROM #mytemp
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
end
--@blind_copy_recipients = @BCC,
SET @I = @I + 1
END
April 30, 2012 at 12:54 pm
I think something like this is close.
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
declare @MessageCount int = 0 --added this so you can track the number of messages sent.
declare FlightClosedList cursor for
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
open FlightClosedList
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
while @@FETCH_STATUS = 0
begin
set @MessageCount = @MessageCount + 1
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
--need to fetch the next record INSIDE the cursor
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
end
PRINT cast(@MessageCount as varchar(5)) + ' Message(s) sent'
It might be even better if you can insert this data into another table instead of sending directly. Then you could create an SSIS job to send emails for all the rows in the table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:05 pm
just spins with nothing happening
WHILE EXISTS (
SELECT *
FROM flightsClosed as a
inner JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44 --returns 2 rows that are different
)
BEGIN
--PRINT 'changes found'
BEGIN
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
declare @MessageCount int = 0
declare FlightClosedList cursor for
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
open FlightClosedList
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
while @@FETCH_STATUS = 0
begin
set @MessageCount = @MessageCount + 1
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
--need to fetch the next record INSIDE the cursor
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
end
END
END
April 30, 2012 at 1:08 pm
You added back in the while exists. That is your endless loop.
WHILE EXISTS (
SELECT *
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
--returns 2 rows that are different
)
There is nothing to modify this table inside your loop. If there is at least 1 row in this it will run forever, if there are none, your code won't do anything. The example I posted is the ENTIRE piece of code you need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:18 pm
okay so i dropped that and am running the query but seems to take a while longer 59 secs so far
April 30, 2012 at 1:22 pm
Longer than infinite? 😀
How many rows are you sending emails for? Did you populate the other variables with real info? @To, @From etc. It will probably take longer for error handling each trip through the cursor if it always fails sending the email.
For testing maybe you should just select the parameters you would be sending to "sp_send_dbmail". That way you can see the data without actually trying to send it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:23 pm
here is what I ran:
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
declare @From nvarchar(255) =from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
declare @MessageCount int = 0 --added this so you can track the number of messages sent.
declare FlightClosedList cursor for
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
open FlightClosedList
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
while @@FETCH_STATUS = 0
begin
set @MessageCount = @MessageCount + 1
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
--need to fetch the next record INSIDE the cursor
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
end
PRINT cast(@MessageCount as varchar(5)) + ' Message(s) sent'
April 30, 2012 at 1:25 pm
yup populated with real values and right now there are only two rows that are different
April 30, 2012 at 1:28 pm
try this, this may help shed some light on where the problems are.
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
declare @MessageCount int = 0 --added this so you can track the number of messages sent.
--added select to see what the result set should look like.
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
declare FlightClosedList cursor for
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
print 'Is the time slow for the above query?'
open FlightClosedList
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
while @@FETCH_STATUS = 0
begin
set @MessageCount = @MessageCount + 1
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
--exec msdb.dbo.sp_send_dbmail
--@profile_name = @profilename,
--@recipients = @To,
--@from_address = @From,
--@subject = @Subject,
--@body = @Message
select @Message
--need to fetch the next record INSIDE the cursor
fetch next from FlightClosedList into @flightId, @flightTime, @blockTime, @ttEngine1, @tcEngine1, @ttEngine2, @tcEngine2, @ttAirframe, @tcAirframe
end
PRINT cast(@MessageCount as varchar(5)) + ' Message(s) sent'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:34 pm
yah - this just spins away for minutes with nothing happening
April 30, 2012 at 1:36 pm
OK how about the actual query then all by itself?
SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2012 at 1:37 pm
why not do if the rows exists then copy to temp table then send email for all rows in temp table and exit when done? that was my thought but it doesn't like the variable declarations in the set rowcount variable.
--draft 2
--step3
--now get the diffs if any
--IF EXISTS(
--select StatusTypeID from dbo.flightsClosed2
--except
--select StatusTypeID from dbo.flightsClosed
--)
--SELECT 'found1'
--ELSE
--SELECT 'not found1'
---------
--IF EXISTS
--(
--SELECT *
--FROM tempdb.dbo.sysobjects
--WHERE ID = OBJECT_ID(N'tempdb..#mytemp')
--)
--BEGIN
--DROP TABLE #mytemp
--END
WHILE EXISTS (
SELECT *
FROM flightsClosed as a
inner JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44 --returns 2 rows that are different
)
BEGIN
--PRINT 'changes found'
BEGIN
--SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
SELECT a.*
INTO #mytemp FROM flightsClosed as a
inner JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
End
BEGIN
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
--declare @BCC nvarchar(255) ='j@.com'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)
DECLARE @rowcount INT
SET @rowcount = (select @flightId = flightId, --!!!!!! errors here
@flightTime = FlightTime,
@blockTime = BlockTime,
@ttEngine1 = TTEngine1,
@tcEngine1 = TCEngine1,
@ttEngine2 = TTEngine2,
@tcEngine2 = TCEngine2,
@ttAirframe = TTAirframe,
@tcAirframe = TCAirframe
FROM #mytemp)
DECLARE @I INT
SET @I = 1
WHILE (@I <= @rowcount)
Begin
set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
end
--@blind_copy_recipients = @BCC,
SET @I = @I + 1
END
END
END
PRINT 'no changes found'
April 30, 2012 at 1:38 pm
yup returns the 2 rows of data
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply