CONCAT in Execute SQL Task

I am curious to know if there is a way to suppress or temporarily store event errors until the final one, then take all those errors and write to db field?

I have a package that uses an event error to store msgs into a field. But, I end up getting several errors that will overwrite my db field each time. Only about two out of 10 are relevent to my problem...

I just need to know if there is a way to suppress the number of event errors that come over , or concat them into one event, per event that occurs? Right now when I hit an error, I end up getting 10 error messages that follow, so my OnError event gets triggered 10 times..

Jason

One way to handle this may be to count the errors using a script in your event handler. Update a variable until you reach the last error you wish to handle, then enable the behaviour you wish to execute.

Donald Farmer

Group Program Manager

SQL Server Integration Services

|||Thanks Donald, how do I determine when the final error occurs in a failure?

So, let's say I have an object that fails, I get 5 onerror event called msgs, how do I know that 5 (or whatever number) is my last error msg for the failure?|||

Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:

UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?

where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?

|||

scoobyjw wrote:

Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:

UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?

where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?

Jason,
Why not try building the SQL statement using a property expression on the SQLStatementSource property?

-Jamie|||The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:

if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?

? Sorry about the pseudo code, I am not an expert at SQL.|||

scoobyjw wrote:

The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:

if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?

? Sorry about the pseudo code, I am not an expert at SQL.

Yeah, try this:
[UPDATE ETL_Transactions SET LogDetails = COALESCE(LogDetails, '') + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

I really think you should look at using a property expression tho

-Jamie

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

name: initials

I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

name: initials

I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

name: initials

I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

name: initials

I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?