Asking the user a question

Hello all,

I have a client with an application that they have purchased, it
stores its data on SQL server 2000 and so I am able to do reports and
some small VB apps that use the data. I can't however make any
significant changes to the app itself, just what it allows you to
configure.

The system records jobs, when a job is closed the client want a prompt
to pop-up asking if they want to record that the job is closed in a
special log file. I suggested that we use a checkbox (We can add
custom fields to the app) to record this, then I could write a trigger
that checks that field and adds it to the log file if it's 1, but no
they want a yes/no message box type thing popping up.

Is it possible to display (on the users screen) and get input back
from a yes/no message box in a SQL Server trigger? As far as I can
tell it isn't but it would be great if I were wrong.

Any help/suggestions greatly appreciated!!!

Thanks,

Bob.SQL Server can't invoke anything in your UI. That's something you have to do
in client-side code.

--
David Portas
SQL Server MVP
--|||Robert Hogan (busrhogan@.hotmail.com) writes:
> Is it possible to display (on the users screen) and get input back
> from a yes/no message box in a SQL Server trigger? As far as I can
> tell it isn't but it would be great if I were wrong.

In theory, yes. But for crying out loud, don't do it!

You could use sp_OAmethod and friends to run a remote OLE object
that would wait for the reponse.

But while waiting for user input - which could take several hours or
days - you are in the middle of a transaction, data will be uncommitted
and locks will be held. Could have sever impact on the situation.

A variant would be to have the OLE thing asynchronous, so that the
trigger does not wait for input, and the OLE thing would then update
the database itself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
"Robert Hogan" <busrhogan@.hotmail.com> wrote in message
news:eb62f112.0501171614.cb68c36@.posting.google.co m...
> Hello all,
> I have a client with an application that they have purchased, it
> stores its data on SQL server 2000 and so I am able to do reports and
> some small VB apps that use the data. I can't however make any
> significant changes to the app itself, just what it allows you to
> configure.
> The system records jobs, when a job is closed the client want a prompt
> to pop-up asking if they want to record that the job is closed in a
> special log file.

Well...
This is the problem with buying a package.
They do what the people wrote them guessed you wanted.
How close that is to what you do depends on how good he guessed.

> I suggested that we use a checkbox (We can add
> custom fields to the app) to record this, then I could write a trigger
> that checks that field and adds it to the log file if it's 1, but no
> they want a yes/no message box type thing popping up.

No chance.
You/They have 4 practical options:

1) Bung the field on and use a trigger to write the stuff as you suggest.

2) Contact the people wrote the package and pay them to make a custom
change.
If they're happy to do this then remember upgrades and expect to pay
through the nose for that confirmation box.

3) Re-write the package from scratch.
Probably financially unattractive unless they have a lot of reports and
customisation in mind.

4) Work out what that screen does and write one yourself that replaces it.
Some packages allow you to have custom screens, I'd guess maybe this
isn't one so maybe not so practical.
If that one screen and your other additions is all whoever closes jobs
uses then maybe it is a go-er.
> Is it possible to display (on the users screen) and get input back
> from a yes/no message box in a SQL Server trigger? As far as I can
> tell it isn't but it would be great if I were wrong.
> Any help/suggestions greatly appreciated!!!

Next time they buy a package..
Remember the hidden costs.
It's can be far cheaper to go for a bespoke app in the long run.

> Thanks,
> Bob.

--
Regards,
Andy O'Neillsql