Create a file using a SQL DB Trigger -- sql server (alternate)

laurenquantrell NO[at]SPAM hotmail.com
7/17/2004 7:13:28 AM

John,
Thanks for your reply.
In this application, the client needs to be able to make the data
"portable" to users who they do not want to have access to the
application itself. The idea is to create a small text document of a
small amount of data, then post the data onto a server where the users
can download this file onto a portable device. The file may be updated
repeadly, but at the time the user downloads it, it will be the latest
file. The file itself contains a small list of customer orders and
instructions for the user. I know this is not the ideal way to do this
but it makes sense for this customer within their business practices.
I have avoided using triggers at all in this rather large application,
but this seems like the point where I need to make the plunge.
What I have is a table tblFileData and it has the columns: txtOutput,
txtEmployeeName, txtOrderNumber, txtOrderDateTime.
What I need is to create a trigger so that when a record is inserted
into the table, the trigger fires and creates a .txt file into a
mapped path on the server
f:\(txtOrderDate)"-"(txtOrderNumber)"-"(txtEmployeeName).txt with
txtOutput as the body of the file (example:
f:\20041225-987654-smithJohn.txt)
Then after creating the txt file, the trigger deletes the table row.

I have to admit I'm completely at ground zero when it comes to doing
this with a trigger so any help in constructing this is appreciated.
lq

[quoted text, click to view]

John Bell
7/17/2004 8:15:10 PM

Hi

The example I posted would have given you just about everything you needed
to create these files although as I pointed out it is not necessarily going
to reflect the actual data! Usually this sort of task is carried out by a
scheduled (possibly DTS) job, although if you schedule it to run too often
it may be better to change it to an on demand process.

Books online will give you more details regarding DTS along with the
following site http://www.sqldts.com/default.aspx?200

Another alternative would be to provide a web interface.

John

[quoted text, click to view]

Erland Sommarskog
7/18/2004 10:16:25 PM

Lauren Quantrell (laurenquantrell@hotmail.com) writes:
[quoted text, click to view]

If you don't know how to write this trigger, this is a good thing,
because that means that you will not do something which is really bad.

It could make sense to write to a text filr from a trigger for debugging
purposes, but for what you describe above, I go as far to say that this
is an unacceptable solution. There are two major problems:

1) The data you write to the file is uncommitted. If an error occurs
in the transaction, and it is rolled back, the user goes out on the
field with bogus data.

2) Forking out to write to files is slow, particularly if you will do
it for many rows - and you will have to iterate over them. This
could be a major bottleneck in your application.

The best would be to write a mini-app that accesses the databases and
gets data to the portable devices. If you absolutely must have files,
set up a job in SQL Agent which runs once a minute and gets rows and
updates the file with the most recent data.

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

Books Online for SQL Server SP3 at

laurenquantrell NO[at]SPAM hotmail.com
7/19/2004 5:46:34 PM

Erland,
As always, you've been very helpful and I appreciate your thoughts on
this.
In this installation, the design of the output is dictated by the
client because of other apps that will use this text file. It's a
first generation attempt on their part to deal with data that they
don't want the users to link to in any way.
So I'm stuck with it.
Also, they don't want users to have access to the path f:\ otherwise
I'd just do the whole thing in VBA with the Access front end.
My concept of this is that the trigger will fire on the inserting of a
new record in my table tblFileData - the trigger will create the file
and then the trigger will delete the row. It will not have to loop
through rows and there are only four columns in the table: txtOutput,
txtEmployeeName, txtOrderNumber, txtOrderDateTime.
The output needs to be:
f:\(txtOrderDate)"-"(txtOrderNumber)"-"(txtEmployeeName).txt with
txtOutput as the body of the file (example:
f:\20041225-987654-smithJohn.txt)
The front end application handles the inserting of a row in
thbFileData every time a change is made to one form, another app (on I
don't have control over) overwrites any previous files, so the output
file will contain the most recent.

I am totally under the gun on this one. Is this trigger easy to
construct? I have never contsructed one and I'm at zero hour. Ouch.

lq

[quoted text, click to view]

Erland Sommarskog
7/20/2004 8:24:30 AM

Lauren Quantrell (laurenquantrell@hotmail.com) writes:
[quoted text, click to view]

Huh? So the row is not to be persisted? In that case, you could use
an INSTEAD OF trigger. An INSTEAD OF triggers sets instead of the command,
which means that if you don't redo the command in the trigger, the command
will not be carried out. The main target for INSTEAD OF triggers are views,
so a trigger in that case would divert data to the appropriate rows. But
you could use it for anything.

[quoted text, click to view]

Since a trigger fires once per statement, and thus can cover many rows
your code must be able to handle multi-row inserts.

[quoted text, click to view]

It's too easy to construct. In a trigger you have the virtual tables
"inserted" and "deleted" to play with. (In an INSERT trigger, only
"inserted".) These tables holds the before- ("deleted") and
after-image ("inserted") of the rows affected rows. Then you can use
xp_cmdshell to execute DOS commands, including writing to a file.

The catch here, is that you need to grant users access to xp_cmdshell,
which is in master. But once you have done this, a user that manage to
find a query tool can log in and run whatever commands he like with
xp_cmdshell. That's why I say it too easy.

One way to address this is write a wrapper stored procedure that you
place in master which accepts the arguments. This wrapper would then
call xp_cmdshell, and you would grant access to the wrapper. You would
have to enable cross-database ownership chaining for this to work.
Which again is a security consideration to think twice over.

Yet another alternative is to write your own extended stored procedure
that writes to the file, but this requires C programming skills.

Maybe you should rethink and find another solution, after all.

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

Books Online for SQL Server SP3 at

laurenquantrell NO[at]SPAM hotmail.com
7/22/2004 10:07:13 AM

Erland,
I hate to tell you "YOU told ME so"...
You were right. Forget the trigger.

I have come up with this instead in a stored procedure. An SP fires
and stores the text in a table with five columns. The SP below
extracts the text into a file.

The only problem is, I need to create a file that only contains text
from one of the columns, a column names OutputText.
DO you know how I can do this?

Alter PROCEDURE "usp_ExportData"
@FileName varchar(100)
AS
SET NOCOUNT ON
DECLARE @ReturnCode int
DECLARE @ExportCommand varchar(255)

SET @ExportCommand =
'BCP myDatabaseName..myTableName out "C:\' +

@FileName +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master..xp_cmdshell @ExportCommand
RETURN(@ReturnCode)
/* GO */

DECLARE @ReturnCodeX int
EXEC @ReturnCodeX = usp_ExportData 'MyFile.txt'
PRINT @ReturnCodeX

[quoted text, click to view]

Erland Sommarskog
7/22/2004 10:09:57 PM

Lauren Quantrell (laurenquantrell@hotmail.com) writes:
[quoted text, click to view]

You will need to use a format file with your BCP command. The format
file for this particular case would look like this:

8.0
1 SQLCHAR 0 0 "\r\n" ? "" ""

For the question mark replace the number of the column you want to export
to the file. (Numbers start at 1.)

The last two columns that I've just set as "" are the column name
(informational only to BCP) and the collation for the column in the
file.

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

Books Online for SQL Server SP3 at

laurenquantrell NO[at]SPAM hotmail.com
7/23/2004 6:25:47 AM

John,
OK, I trashed the isea of a trigger and have adopted your suggection
of using xp_cmdshell from a stored procedure. Whenever the data is
inserted into the table in the SP, the same SP calls xp_cmdshell.

This is what I have done...
I have created a view named vOutput that shows one column in a table.
I insert a row into the table and then I'm using this code to create a
file with the text in the single row.

This code works fine when I'm signed on with an account that has
server admin rights, however it fails when I signin with an account
that does not have server admin rights.

Can you shed some light on solving this?

Alter PROCEDURE OutputOrders
@FileName nvarchar(50)

AS
set nocount on

DECLARE @ReturnCode int
DECLARE @ExportCommand varchar(255)

SET @ExportCommand =
'BCP myServerName.dbo.vOutput out "c:\output\order files\' +
@TemplateFileName +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master.dbo.xp_cmdshell @ExportCommand

I have granted execute permission to xp_cmdshell for both users and on
the view.

[quoted text, click to view]

John Bell
7/23/2004 9:17:38 PM

Hi

This is direct from books online. I assume that it is a permissions problem
on xp_cmdshell and not on the directory itself:
"By default, only members of the sysadmin fixed server role can execute this
extended stored procedure. You may, however, grant other users permission to
execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in
which the SQL Server service is running. When the user is not a member of
the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server."

If this is not a xp_cmdshell permission problem then it could be that access
to the directory for the SQL Server Agent proxy account is not valid or the
user account that started SQL Server for windows 9.x.

As debug option you may want to execute a "dir C:\*" command

John

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Create a file using a SQL DB Trigger