We have to implement a process where a text file is created in a secure
place when a value in a column on a table is changed to a specific
value. Basically, we have a mortgage system that doesn't generate
checks so we need to write out a file that the user can then go to our
check generation system and open the file. As soon as they change the
status to "ready" in the mortgage system, this file needs to be created.
The user will then open this file in the check generation application
and print the check. The user that is connected in the mortgage
application is a SQL Server login.

What would be the best way to implement this?

Some ideas we have had are as follows:
1. Create a trigger and use xp_cmdshell to write out to a text file.
The problem with this is we don't want to give the user sysadmin access
to be able to execute xp_cmdshell (I think the login would have to be
sys admin, but not completely sure). We're also not sure if the user
would have access to write to the folder because it is a SQL Server
login. Also, we're not sure that executing xp_cmdshell from a trigger
is a good idea.
2. Enable the CLR and have a trigger call a .NET program which would
have a web.config file that would contain a connection string that would
use the SQL Agent id.

We were also thinking that we could somehow incorporate Service Broker
into this (which we have never used before and don't know much about).

I am curious what others would do or have done. This is a new request
to us and we're not really sure the best direction. Option 1 was the
way our developer told us he was going to do it and then we (the DBA's)
said we need to understand other options better and determine if this is
a good idea or even possible.

Thanks!

Laura Beranek
Database Administrator
Associated Bank Corp
laura.[Email address protected]
(920)405-2589



*************************** NOTICE ***************************

This e-mail and attachment(s) may contain information that is
privileged, confidential, and/or exempt from disclosure under
applicable law. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copy of this message is strictly prohibited. If
received in error, please notify the sender immediately and
delete/destroy the message and any copies thereof. Although
Associated Banc-Corp and/or its affiliates (collectively
"Associated") attempt to prevent the passage of viruses via e-mail
and attachments thereto, Associated does not guarantee that either
are virus-free, and accepts no liability for any damage sustained
as a result of any such viruses.

Any federal tax advice contained in this communication (including
any attachments) is not intended or written to be used or referred
to in the promoting, marketing, or recommending of any entity,
investment plan or agreement, nor is such advice intended or written
to be used, and cannot be used, by a taxpayer for the purpose of
avoiding penalties under the Internal Revenue Tax Code.



MS Sql Server LazyDBA home page