how can a trigger act asynchronously to the event that fired it in sql server 2005?

if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.

Having the trigger call a job using sp_start_job would be one option if you don't want to use Service Broker, although Service broker would be a better approach.

-Sue

|||are jobs supported in all editions (including express)? I thought i read something once saying they (might have just been scheduling) are not supported in express.|||

You could also have the trigger just insert a request into a 'queue' of your own and have a SP that periodically wakes up and processes any pending requests. SSB and SQL Agent help this scenario by providing queues and jobs, respectively, but you could build some of this yourself (using WaitFor, etc.).

Don

|||

so service broker does come free and full featured (except for inter instance) with express? how about the sql job feature, except for maybe scheduling?

how can a trigger act asynchronously to the event that fired it in sql server 2005?

if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.

Having the trigger call a job using sp_start_job would be one option if you don't want to use Service Broker, although Service broker would be a better approach.

-Sue

|||are jobs supported in all editions (including express)? I thought i read something once saying they (might have just been scheduling) are not supported in express.|||

You could also have the trigger just insert a request into a 'queue' of your own and have a SP that periodically wakes up and processes any pending requests. SSB and SQL Agent help this scenario by providing queues and jobs, respectively, but you could build some of this yourself (using WaitFor, etc.).

Don

|||

so service broker does come free and full featured (except for inter instance) with express? how about the sql job feature, except for maybe scheduling?

How calculate SMO.Transfer progress?

I have work code who transfer database using SMO.Transfer object.

This code start my transfer and subscribe my event handlers. I have such problem: DiscoveryProgress - does not approach. Maybe need use additional options?

m_tr.ScriptingError += new ScriptingErrorEventHandler(m_tr_ScriptingError);
m_tr.ScriptingProgress += new ProgressReportEventHandler(m_tr_ScriptingProgress);

m_tr.DiscoveryProgress += new ProgressReportEventHandler(m_tr_DiscoveryProgress);
m_tr.DataTransferEvent += new DataTransferEventHandler(m_tr_DataTransferEvent);

m_script = m_tr.ScriptTransfer();
m_tr.TransferData();

Sergey,

You can define your event handlers like this:

public static void DataTransferHandler(object sender, DataTransferEventArgs e)
{
Console.WriteLine(e.DataTransferEventType.ToString() + " : " + e.Message);
}

public static void ProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

public static void ScriptingProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

Then set up your events like this:

trans.DiscoveryProgress += new ProgressReportEventHandler(ProgressEventHandler);

trans.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressEventHandler);

trans.DataTransferEvent += new DataTransferEventHandler(DataTransferHandler);

By the way, you don't need to call ScriptTransfer before calling TransferData. ScriptTransfer returns TSQL script in a StringCollection, which you are not using.

Artur Laksberg

SQL Server Team

|||Hi Artur,
I did this as you wrote. I only use the scripting progress and the discover progress for my app. The scripting event is raised, and my handler gets it, but the totals and subtotals all remain at value of 1. The discovery event is either never raised or something is wrong on my side as my handler is never called.
Do you know what else I can try? Is this a known issue?
Regards
Japie Teunissen
|||

Japie,

You are right: we seem to have a few problems here. Total shows right values for Scripting, but Transfer uses Scripting to transfer objects one at a time, hence "1 out of 1" you see in the event handler. Sort of makes sense but not useful at all.

Discover event is only raised when you call Script with a list of URNs, which most people don't. Again, confusing and not very useful.

I'll file a bug to clean this up.

Thanks to you and Sergey for pointing this out.

Artur Laksberg
SQL Server Team
Microsoft

How calculate SMO.Transfer progress?

I have work code who transfer database using SMO.Transfer object.

This code start my transfer and subscribe my event handlers. I have such problem: DiscoveryProgress - does not approach. Maybe need use additional options?

m_tr.ScriptingError += new ScriptingErrorEventHandler(m_tr_ScriptingError);
m_tr.ScriptingProgress += new ProgressReportEventHandler(m_tr_ScriptingProgress);

m_tr.DiscoveryProgress += new ProgressReportEventHandler(m_tr_DiscoveryProgress);
m_tr.DataTransferEvent += new DataTransferEventHandler(m_tr_DataTransferEvent);

m_script = m_tr.ScriptTransfer();
m_tr.TransferData();

Sergey,

You can define your event handlers like this:

public static void DataTransferHandler(object sender, DataTransferEventArgs e)
{
Console.WriteLine(e.DataTransferEventType.ToString() + " : " + e.Message);
}

public static void ProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

public static void ScriptingProgressEventHandler(object sender, ProgressReportEventArgs args)
{
Console.Write("Progress {0} of {1} . . . {2} \n", args.TotalCount, args.Total, args.Current);
}

Then set up your events like this:

trans.DiscoveryProgress += new ProgressReportEventHandler(ProgressEventHandler);

trans.ScriptingProgress += new ProgressReportEventHandler(ScriptingProgressEventHandler);

trans.DataTransferEvent += new DataTransferEventHandler(DataTransferHandler);

By the way, you don't need to call ScriptTransfer before calling TransferData. ScriptTransfer returns TSQL script in a StringCollection, which you are not using.

Artur Laksberg

SQL Server Team

|||Hi Artur,
I did this as you wrote. I only use the scripting progress and the discover progress for my app. The scripting event is raised, and my handler gets it, but the totals and subtotals all remain at value of 1. The discovery event is either never raised or something is wrong on my side as my handler is never called.
Do you know what else I can try? Is this a known issue?
Regards
Japie Teunissen
|||

Japie,

You are right: we seem to have a few problems here. Total shows right values for Scripting, but Transfer uses Scripting to transfer objects one at a time, hence "1 out of 1" you see in the event handler. Sort of makes sense but not useful at all.

Discover event is only raised when you call Script with a list of URNs, which most people don't. Again, confusing and not very useful.

I'll file a bug to clean this up.

Thanks to you and Sergey for pointing this out.

Artur Laksberg
SQL Server Team
Microsoft