Design Question

[marcmc]

I have a VB App that acts as a maintenance of a number of routines to be set
up, edited and run on a SQL Server Db.

The nature of this app is to return results from the routines(sProcs ) and
pump out a log file with the results from the .net App. A SMS is also sent to
a support mobile phone to tell on cover staff to come in or out depending on
the results of these routines.

I cannot kick off the selected routines until a large DTS routin is run.
This routine runs for about 5-6 hours and it is quite easy to determine when
it has completed.

My issue is:
Should I poll/trigger when the DTS is complete from SQLServer or from my
Vb.net App? (I have no preference at the moment - although the fact I'm
writing this on a dotnet forum is testament that I am more a .net man than
Sql man!).
Whether I poll from SQL or from .net App, i MUST keep the .net App open for
the duration of the poll in order to send the SMS and create the log. Am I
thinking the right way about this or what are the alternatives?

I could have written this App completely in SQL but I wouldn't have had the
safety of a desktop app and various levels of security I can manage from .net
nor the ability to manage/maintain the types of log and SMS functionality I
want.

Any input much onliged...

marc

[marcmc]

RE: Design Question

Don't be afraid to share you thoughts guys

[JerryH]

Re: Design Question

marcmc wrote:[color=blue]
> I have a VB App that acts as a maintenance of a number of routines to be set
> up, edited and run on a SQL Server Db.
>
> The nature of this app is to return results from the routines(sProcs ) and
> pump out a log file with the results from the .net App. A SMS is also sent to
> a support mobile phone to tell on cover staff to come in or out depending on
> the results of these routines.
>
> I cannot kick off the selected routines until a large DTS routin is run.
> This routine runs for about 5-6 hours and it is quite easy to determine when
> it has completed.
>
> My issue is:
> Should I poll/trigger when the DTS is complete from SQLServer or from my
> Vb.net App? (I have no preference at the moment - although the fact I'm
> writing this on a dotnet forum is testament that I am more a .net man than
> Sql man!).
> Whether I poll from SQL or from .net App, i MUST keep the .net App open for
> the duration of the poll in order to send the SMS and create the log. Am I
> thinking the right way about this or what are the alternatives?
>
> I could have written this App completely in SQL but I wouldn't have had the
> safety of a desktop app and various levels of security I can manage from .net
> nor the ability to manage/maintain the types of log and SMS functionality I
> want.
>
> Any input much onliged...
>
> marc
>
>[/color]


Are you using DTS objects in .NET? A couple of small hoops to jump
through to get the Libs installed on your machine, but no big deal.

http://www.sqldev.net/dts/DotNETCookBook.htm

You could actually start the DTS via DTS Objects from your .NET app, and
monitor when the DTS was finished (and perhaps even raise some events to
aid in your logging functions).

HTH