Asynchronous data flow tasks how to run more than 4 at a time

Hi guys,

i have a for each loop and it has about 20 data flow tasks (simple data extractions). i notice when i run the package it only runs up to 4 data flow tasks at a time. others have to wait till one of the first 4 flows finishes.

i was wondering if there's a way to change the limit of how many data flow tasks can run at a time. is there a property some where ?

i know this will be stressfull to the server, but the server is well equiped with CPU power and memory, so performance will not be an issue.

any thoughts?

Package.MaxConcurrentExecutables Property

Valid values are one and higher, or -1. Other values are invalid. A value of -1 allows the maximum number of concurrently running executables to equal the number of processors plus two. Setting this property to zero or any other negative value fails with an error code that indicates an invalid argument.

This property is used when parallelism exists in the workflow. If the workflow is a series of sequential precedence constraints, then this property has no effect.

I don't know if you can get more than CPU Count + 2 by forcing the value. If this is a 32-bit server then I would be concerned about memory, as despite having 10 GB in there, a process (read SSIS Package) can only use 2GB or 3GB with the/3GB boot.ini switch, so you may want to break out into multiple packages, or just call the same package multiple times. The Execute Package Task can be used to get multiple processes with the out of processes property, but this has a higher overhead for loading and starting the packages.

Assistance with Trigger

Hi,

Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.

I have table_X which I have a trigger on INSERT setup.
This trigger updates Field_2 = '1' and inserts some rows in another table.

Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH"
So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)

Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.

Thanks in advance for your help!!!

Cheersnow, you have to make sure you understand that no matter how many rows you insert, the trigger will fire only once per batch, ok?

if exists (select * from inserted where field_a = 'blah')
-- do your stuff|||Now that is a prompt response - thanks!

Yeah I understand that the trigger will only fire once per batch

So here is another stupid question - where you say select * from inserted. Is inserted a key word here or where you using that as my table name?
Because basically what I am after is (if it is possible) to have (for example) a record inserted with field_3 = 1234, and field_1 = "BLAH", then the trigger to go "okay" field_1 = BLAH and then carry on and use the field_3 value (ie 1234) in the remaining query to build other records in other tables.

If what I think is correct - the "inserted" word is actually just my table name - then the trigger will still be firing a lot of times because there is already a lot of records in the table with Field_1 = BLAH....

I fear I might be going round in circles and not making a hell of a lot of sense....*argh*|||no, inserted is an internal table visible only for the trigger itself. that's where new values resulting from your insert statement are stored.|||Sorry to keep asking a million and one questions, but so this table "inserted" which can be queried from the trigger (eg select * from updated), does this have the same structure (ie fields and field names) as the actual table which the data is going to be inserted into?

And is there any way where I can pull values from that table and use them as variables in the trigger?|||the answer is YES, except there are only 2 internal table, inserted and deleted. when the number of rows in both is the same it's an update operation, if deleted table is empty then it's an insert. and again, the structure of both is identical to the structure of the table for which the trigger was created.

edited:

and yes, you can store all values into variables from inserted, or reference that table throughout the execution of the trigger.|||Brilliant!!

Thanks heaps for your help!

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_id
Hi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
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/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id
|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> 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/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> for
> value
> to
> the
> where
>
>
|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...[vbcol=seagreen]
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:

> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>
>
|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.

> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...[vbcol=seagreen]
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_idHi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
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/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> 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/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> > I hope you guys can help. I am looking to create a trigger that watches
> for
> > updates to the sch_status column and fires when the update changes the
> value
> > to 4. The code that will be executed is below with addition of some code
> to
> > e-mail the results. It is the actual code to create the update trigger on
> the
> > schedule table I need some help with.
> >
> > select s.sch_id,
> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> > s.e_subject,
> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> where
> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> > dl.sch_id = s.sch_id),
> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> > convert(varchar(12),getdate(),112) +'.log'
> > from schedule s
> > where s.sch_status = 4
> > order by s.sch_id
>
>|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> All what you have to do it join the virtual table INSERTED in your query
>> on
>> the primary key.
>> It is not recommended that you send e-mails from within a trigger, as it
>> hurts performance, has horrible consequences with locking and blocking,
>> and
>> if the e-mail sending fails, your update will get rolled back (and you
>> might
>> have sent e-mails to 1/2 your customer base). Rather write a record into
>> a
>> queue type table where another process comes along a sends the e-mails.
>> Books On Line has some good examples on CREATE TRIGGER and how to use the
>> virtual tables INSERTED and DELETED.
>> 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/
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> > I hope you guys can help. I am looking to create a trigger that watches
>> for
>> > updates to the sch_status column and fires when the update changes the
>> value
>> > to 4. The code that will be executed is below with addition of some
>> > code
>> to
>> > e-mail the results. It is the actual code to create the update trigger
>> > on
>> the
>> > schedule table I need some help with.
>> >
>> > select s.sch_id,
>> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> > s.e_subject,
>> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
>> where
>> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
>> > dl.sch_id = s.sch_id),
>> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> > convert(varchar(12),getdate(),112) +'.log'
>> > from schedule s
>> > where s.sch_status = 4
>> > order by s.sch_id
>>|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:
> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> > In case it leads to a different outcome I plan to have the select
> > statement
> > and the e-mail (not sql mail) in a stored procedure and have it called by
> > the
> > trigger.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> All what you have to do it join the virtual table INSERTED in your query
> >> on
> >> the primary key.
> >>
> >> It is not recommended that you send e-mails from within a trigger, as it
> >> hurts performance, has horrible consequences with locking and blocking,
> >> and
> >> if the e-mail sending fails, your update will get rolled back (and you
> >> might
> >> have sent e-mails to 1/2 your customer base). Rather write a record into
> >> a
> >> queue type table where another process comes along a sends the e-mails.
> >>
> >> Books On Line has some good examples on CREATE TRIGGER and how to use the
> >> virtual tables INSERTED and DELETED.
> >>
> >> 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/
> >>
> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> >> > I hope you guys can help. I am looking to create a trigger that watches
> >> for
> >> > updates to the sch_status column and fires when the update changes the
> >> value
> >> > to 4. The code that will be executed is below with addition of some
> >> > code
> >> to
> >> > e-mail the results. It is the actual code to create the update trigger
> >> > on
> >> the
> >> > schedule table I need some help with.
> >> >
> >> > select s.sch_id,
> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> >> > s.e_subject,
> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> >> where
> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> >> > dl.sch_id = s.sch_id),
> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> >> > convert(varchar(12),getdate(),112) +'.log'
> >> > from schedule s
> >> > where s.sch_status = 4
> >> > order by s.sch_id
> >>
> >>
> >>
>
>|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:
>> You still have the same issues (concurrency/performance), even if you
>> execute a proc in order to send email from the trigger. I suggest you
>> heed
>> Mike's suggestion and employ a queue-type table so that you can send the
>> actual email asynchronously. It's fairly simple to implement.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>> > In case it leads to a different outcome I plan to have the select
>> > statement
>> > and the e-mail (not sql mail) in a stored procedure and have it called
>> > by
>> > the
>> > trigger.
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> All what you have to do it join the virtual table INSERTED in your
>> >> query
>> >> on
>> >> the primary key.
>> >>
>> >> It is not recommended that you send e-mails from within a trigger, as
>> >> it
>> >> hurts performance, has horrible consequences with locking and
>> >> blocking,
>> >> and
>> >> if the e-mail sending fails, your update will get rolled back (and you
>> >> might
>> >> have sent e-mails to 1/2 your customer base). Rather write a record
>> >> into
>> >> a
>> >> queue type table where another process comes along a sends the
>> >> e-mails.
>> >>
>> >> Books On Line has some good examples on CREATE TRIGGER and how to use
>> >> the
>> >> virtual tables INSERTED and DELETED.
>> >>
>> >> 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/
>> >>
>> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> >> > I hope you guys can help. I am looking to create a trigger that
>> >> > watches
>> >> for
>> >> > updates to the sch_status column and fires when the update changes
>> >> > the
>> >> value
>> >> > to 4. The code that will be executed is below with addition of some
>> >> > code
>> >> to
>> >> > e-mail the results. It is the actual code to create the update
>> >> > trigger
>> >> > on
>> >> the
>> >> > schedule table I need some help with.
>> >> >
>> >> > select s.sch_id,
>> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> >> > s.e_subject,
>> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list
>> >> > dl
>> >> where
>> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl
>> >> > where
>> >> > dl.sch_id = s.sch_id),
>> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> >> > convert(varchar(12),getdate(),112) +'.log'
>> >> > from schedule s
>> >> > where s.sch_status = 4
>> >> > order by s.sch_id
>> >>
>> >>
>> >>
>>

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on th
e
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_idHi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
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/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by th
e
trigger.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> All what you have to do it join the virtual table INSERTED in your query o
n
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, an
d
> if the e-mail sending fails, your update will get rolled back (and you mig
ht
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> 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/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> for
> value
> to
> the
> where
>
>|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...[vbcol=seagreen]
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
>|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:

> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you hee
d
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>
>|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.

> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...[vbcol=seagreen]
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:
>

Assigning XML data to variables

Hi Guys...

Need some assistance here again.
What data type should I use for the variable that I receive from a resultset?

I tried setting it to object, but I encountered some problem later in the stage during the data flow because the XML source adapter cannot get the variable.

I tried setting it to String but get some error during runtime because of invalid datatype.

The main purpose here is actually to get the data from an XML column in a table and populate it into the XML Source Adapter so that I can do some transformations.

Any ideas?

Thanks.Hi,
Yes, this is definitely an issue. In the next release (post- CTP15) you will be able to put the XML result set into a string variable that can then be consumed by the XML Source Adapter. For now, you might be able to work around it with a script component that creates a new string variable and populates it with the data from the object variable. I'm sorry I don't have a better answer for you.

Thanks,
Mark

Assigning to multipule categories

Ok guys,
I'm realitvely new to the whole database development stuff, but I have a very important project to finish using SQL and ASP. I am to design a new links manager for a website.
Right now I have the following:
The ability to add a link, and edit it
The ability to add a category and edit it

When you go to add a link, a list of categories is provided for you, with checkboxes. What I need to do is figure out how to assign multipule categories to one link.
I have a Cross-Referencing table with three fields:
CrossRefID
LinkID
and CatID.

If you need more clarification, post here and let me know.

Thanks in Advance,
Aaron Hawn (aaron@.ionzion.net)The table structure you provided is sufficient to answer that question. Can you clarify your issue?|||Operative word being think, I think you are asking how to represent multiple relationships using the table schema you've described. One row in the table represents one cross reference from a link to a category. To represent multiple category relationships for a single link, you add multiple rows to the Cross-reference table for that LinkID.

-PatP

Assigning FOREIGN KEY, which method is correct?

Hi guys,

I am real new to SQL and just like to clear up some questions regarding to assigning FOREIGN KEY.

Are there any differences in assigning foreign keys this way:

CREATE TABLE NURSE
(EMP_ID VARCHAR2(8) CONSTRAINT fk_nurse_emp REFERENCES EMPLOYEE(EMP_ID),
WARD_ID VARCHAR2(8) CONSTRAINT fk_nurse_ward REFERENCES WARD(WARD_ID),
CONSTRAINT pk_nurse PRIMARY KEY(EMP_ID, WARD_ID));

and this way:

CREATE TABLE NURSE
(EMP_ID VARCHAR2(8),
WARD_ID VARCHAR2(8),
CONSTRAINT fk_nurse_emp FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(EMP_ID),
CONSTRAINT fk_nurse_ward FOREIGN KEY(WARD_ID) REFERENCES WARD(WARD_ID),
CONSTRAINT pk_nurse PRIMARY KEY(EMP_ID, WARD_ID));

What are the differences between them?

Many thanks.No difference - except that you couldn't create a composite foreign key with the first method. They are just alternatives.|||Thank you for clearing this up for me andrewst.

Another question, might not be all that important, but which is the preferred method?

I guess the second method is more preferred, as it clearly identifies the FOREIGN KEYS, but it takes more lines. I just like to learn the basic conventions first and stick to it.|||Well it's really a matter of personal taste and/or company policy. But since the second syntax works for all foreign keys and the first works only for single-column foreign keys, the second syntax could be preferred.

asp/net sql update against ntext datatype

Guys Ihave a table that among the other columns has a column of ntext.

I also have another table that has another ntext column...
due to normalizations I need to merge the tables and set the column on the first table plus the value from the second table...(based on some where clause...

How can this be done?
if data type is varchar it is no problem..but due to text datatype I am unable to perform this update...

please help.

FrankUnfortunately you cannot concatenate an ntext field. I think the best you are going to be able to do is something like this:

SELECT
CAST(CAST(myText AS nVarchar(4000)) + CAST(myText AS nVarchar(4000)) AS ntext)
FROM
Test

However this will obviously truncate both columns at 4000 before putting them together.

Terri|||but the length exceeds 8000.

that is actually the problem.|||My only suggestion then is to run a VB.NET (or C#) program that selects in the data from both sources, concatenates the nText data, then updates the nText field in the desired database table. I don't think you are going to be able to do this within just Transact-SQL itself.

Terri|||well that requires application code..it must be a way of doing it too...in sql..
thanks anyway.|||There is no way to concatenate nText fields to each other. If you find a way around it, let us know.

Quoting directly from the Books Online:

+ (String Concatenation)
An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

Syntax
expression + expression

Arguments
expression

Is any valid Microsoft® SQL Server? expression of any of the data types in the character and binary data type category,except the image, ntext, or text data types. Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.

Terri

asp.net connect to sql server

i need code for this guys... no one has given any straight answers... paypal $200/hr for consulting fees. Otherwise, you're going to have to read a book.|||Take a look @.this article
Does this help?

ASP.net app TO SQL SERVER REPORTING DATABASE.

Hi guys; hope you can help me
I have built a web app that sits on our web server (asp.net 2.0).
this connects to our SQL server (2005) reporting services database on
dataserver.
Anonymous access must be switched of on IIS (integrated windows only
on).
Once the report has been selected by the user, I query the database to
get the parameters for selected report (loop through parameters
collection). I then check the required parameter controls for the
parameter values. I was using Anonymous access and this was working
fine. But since changing to not allowing Anonymous access I get an
"The request failed with HTTP status 401: Access Denied" error when
using the GetReportParameters method. My code is below
Dim rs As New washington.ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim report As String = strReportPath & strReportName
Dim forRendering As Boolean = False
Dim historyID As String = Nothing
Dim values As washington.ParameterValue() = Nothing
Dim credentials As washington.DataSourceCredentials() = Nothing
Dim parameters As washington.ReportParameter() = Nothing
parameters = rs.GetReportParameters(report, historyID, forRendering,
values, credentials)
Dim intParamCount As Integer = parameters.Length
Dim intLoopCounter As Integer
Dim parmArray(intParamCount - 1) As ReportParameter
If Not (parameters Is Nothing) Then
Dim rp As washington.ReportParameter
For Each rp In parameters
' loop collection
Next rp
End if
as i understand it i should be passing the credentials to the report
but when call
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
the credentials are empty
am i on the correct path
cheers
pHi, Phil
so, you're using the SOAP API to query parameters for a specific
report, and when you switch the ReportServer web app from anonymous to
Integrated Auth only, you get Access denied. Have you tried hitting
the report server URL from Internet Explorer after you switch to Integ.
Auth? Can you see the Report Server web app? Does it display any
catalog items like folders and reports? Does it display the report
you're trying to query for parameters, and if so, does it allow you to
run it from the browser?
If you answer yes, then your current user context has access to the
report server (by default the installation grants the local
administrators group full access to the report catalog from the root).
Also, when you try to "debug" or output the credentials from the
DefaultCredentials it will always have an empty value. Try deploying
your code to another server, instead of the localhost, and the
CredentialCache property should work fine. If you would still like to
test from your localhost, instead of using
CredentialCache.DefaultCredentials, try creating a new
NetworkCredential(string user, string pwd, string domain). You can
hardcode your credentials there, and test your code that way just to
test if you are actually authenticating at the server side with the web
service. Note that this option is for a test scenario, not for
production, as you would not want to bake in credentials in code.
If you answer no, then simply login to the box as a local admin account
(or open IE using the "Run As" option and enter the credentials of an
admin account on the box). Once you're logged in as admin and open IE
to the Report Manager URL (http://<machinename or localhost>/Reports),
you can view the properties of the folder or item (report) and add a
user account and permissions set for access to that catalog item ( you
can give it Content Manager, Browser, etc).
Regards,
Thiago Silva
On Nov 23, 10:59 am, "Phils" <phil.sm...@.iresponse.co.uk> wrote:
> Hi guys; hope you can help me
> I have built a web app that sits on our web server (asp.net 2.0).
> this connects to our SQL server (2005) reporting services database on
> dataserver.
> Anonymous access must be switched of on IIS (integrated windows only
> on).
> Once the report has been selected by the user, I query the database to
> get the parameters for selected report (loop through parameters
> collection). I then check the required parameter controls for the
> parameter values. I was using Anonymous access and this was working
> fine. But since changing to not allowing Anonymous access I get an
> "The request failed with HTTP status 401: Access Denied" error when
> using the GetReportParameters method. My code is below
> Dim rs As New washington.ReportingService()
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim report As String = strReportPath & strReportName
> Dim forRendering As Boolean = False
> Dim historyID As String = Nothing
> Dim values As washington.ParameterValue() = Nothing
> Dim credentials As washington.DataSourceCredentials() = Nothing
> Dim parameters As washington.ReportParameter() = Nothing
> parameters = rs.GetReportParameters(report, historyID, forRendering,
> values, credentials)
> Dim intParamCount As Integer = parameters.Length
> Dim intLoopCounter As Integer
> Dim parmArray(intParamCount - 1) As ReportParameter
> If Not (parameters Is Nothing) Then
> Dim rp As washington.ReportParameter
> For Each rp In parameters
> ' loop collection
> Next rp
> End if
> as i understand it i should be passing the credentials to the report
> but when call
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> the credentials are empty
> am i on the correct path
> cheers
> p|||Once you make the site non-anonymous, the credentials are no longer
anonymous. You have to actually query the user. I did this a few months ago
at another job, but do not have access to the code. I remember experimenting
with impersonation and believe that was the first part of the solution.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
********************************************
Think outside the box!
********************************************
"Phils" <phil.smith@.iresponse.co.uk> wrote in message
news:1164301180.467523.79980@.k70g2000cwa.googlegroups.com...
> Hi guys; hope you can help me
>
> I have built a web app that sits on our web server (asp.net 2.0).
> this connects to our SQL server (2005) reporting services database on
> dataserver.
> Anonymous access must be switched of on IIS (integrated windows only
> on).
>
> Once the report has been selected by the user, I query the database to
> get the parameters for selected report (loop through parameters
> collection). I then check the required parameter controls for the
> parameter values. I was using Anonymous access and this was working
> fine. But since changing to not allowing Anonymous access I get an
> "The request failed with HTTP status 401: Access Denied" error when
> using the GetReportParameters method. My code is below
>
> Dim rs As New washington.ReportingService()
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>
> Dim report As String = strReportPath & strReportName
> Dim forRendering As Boolean = False
> Dim historyID As String = Nothing
> Dim values As washington.ParameterValue() = Nothing
> Dim credentials As washington.DataSourceCredentials() = Nothing
> Dim parameters As washington.ReportParameter() = Nothing
>
> parameters = rs.GetReportParameters(report, historyID, forRendering,
> values, credentials)
>
> Dim intParamCount As Integer = parameters.Length
> Dim intLoopCounter As Integer
> Dim parmArray(intParamCount - 1) As ReportParameter
> If Not (parameters Is Nothing) Then
> Dim rp As washington.ReportParameter
> For Each rp In parameters
> ' loop collection
>
> Next rp
> End if
>
> as i understand it i should be passing the credentials to the report
> but when call
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
>
> the credentials are empty
> am i on the correct path
> cheers
> p
>

ASP.NET + SQL 2005

Guys, I have a problem with setting up connection from asp file to MS Sql db.
I'm using Web.Config as the file to setup the connection:
<connectionStrings
<add name="AMConn" connectionString="Data Source=TWS_WS04;Initial Catalog=AccessMed;Integrated Security=True"

providerName="System.Data.SqlClient" /
</connectionStrings
My connection to the db from aspx file:

string strSQL = "SELECT * FROM Client";

SqlConnection myConnection = new SqlConnection(ConfigurationManager.AppSettings["AMConn"]);

SqlDataAdapter myCommand = new SqlDataAdapter(strSQL, myConnection);

DataSet objDataSet = new DataSet();

myCommand.Fill(objDataSet, "Client");

DataTable objDataTable = objDataSet.Tables["Client"];

And the error message: "The ConnectionString property has not been initialized."

I know that there is a problem with my connection because no matter what I put as AppSettings["ANYTHING I WANT"]); - the error is always the same.

I'm new to asp.net and I would appreciate any help.

Thanks,

JimUse the specific newsgroups for 2005 questions:
http://communities.microsoft.com/ne...welcomePage.htm

--
David Portas
SQL Server MVP
--|||aspx.cs file. I'm using C# as code behind.

"draku" <jkafara@.hotmail.com> wrote in message

My connection to the db from aspx file:|||GOT IT!
If anyone ever have the same problem this is the solution:
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrin gs["AMConn"].ConnectionString);

"draku" <jkafara@.hotmail.com> wrote in message news:dhhakm$dln$1@.inews.gazeta.pl...
Guys, I have a problem with setting up connection from asp file to MS Sql db.
I'm using Web.Config as the file to setup the connection:
<connectionStrings
<add name="AMConn" connectionString="Data Source=TWS_WS04;Initial Catalog=AccessMed;Integrated Security=True"

providerName="System.Data.SqlClient" /
</connectionStrings
My connection to the db from aspx file:

string strSQL = "SELECT * FROM Client";

SqlConnection myConnection = new SqlConnection(ConfigurationManager.AppSettings["AMConn"]);

SqlDataAdapter myCommand = new SqlDataAdapter(strSQL, myConnection);

DataSet objDataSet = new DataSet();

myCommand.Fill(objDataSet, "Client");

DataTable objDataTable = objDataSet.Tables["Client"];

And the error message: "The ConnectionString property has not been initialized."

I know that there is a problem with my connection because no matter what I put as AppSettings["ANYTHING I WANT"]); - the error is always the same.

I'm new to asp.net and I would appreciate any help.

Thanks,

Jim