Create a file using a SQL DB Trigger

[Lauren Quantrell]

Is there a way to create a text file (such as a Windows Notepad file)
by using a trigger on a table? What I want to do is to send a row of
information to a table where the table: tblFileData has only one
column: txtOutput
I want to use the DB front end (MS Access) to send the text string to
the SQL backend, then have the SQL Server create a file to a path,
such as F:/myfiledate.txt that holds the text in txtOutput, then the
trigger deletes the row in tblFileData.
Can this be done easily?
Any help is appreciated

[John Bell]

Re: Create a file using a SQL DB Trigger

Hi

I am not sure why you should want to do this. If the transaction is rolled
back then the file will have different information to the database table.
You may want to look at the stored procedure xp_cmdshell, such as
http://tinyurl.com/64azq. Use of the echo command will write the information
into a file select @sql = echo ' + @v + ' >
\\Myserver\MySh are\myfiledate. txt'

Note: The example does not take into account multiple rows in the inserted
table.

John

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 407131443.33a87 3bb@posting.goo gle.com...[color=blue]
> Is there a way to create a text file (such as a Windows Notepad file)
> by using a trigger on a table? What I want to do is to send a row of
> information to a table where the table: tblFileData has only one
> column: txtOutput
> I want to use the DB front end (MS Access) to send the text string to
> the SQL backend, then have the SQL Server create a file to a path,
> such as F:/myfiledate.txt that holds the text in txtOutput, then the
> trigger deletes the row in tblFileData.
> Can this be done easily?
> Any help is appreciated[/color]

[Lauren Quantrell]

Re: Create a file using a SQL DB Trigger

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, txtOrderDateTim e.
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:\(txtOrderDat e)"-"(txtOrderNumbe r)"-"(txtEmployeeNa me).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



"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<GO7Jc.282 2$Ec4.33496260@ news-text.cableinet. net>...[color=blue]
> Hi
>
> I am not sure why you should want to do this. If the transaction is rolled
> back then the file will have different information to the database table.
> You may want to look at the stored procedure xp_cmdshell, such as
> http://tinyurl.com/64azq. Use of the echo command will write the information
> into a file select @sql = echo ' + @v + ' >
> \\Myserver\MySh are\myfiledate. txt'
>
> Note: The example does not take into account multiple rows in the inserted
> table.
>
> John
>
> "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> news:47e5bd72.0 407131443.33a87 3bb@posting.goo gle.com...[color=green]
> > Is there a way to create a text file (such as a Windows Notepad file)
> > by using a trigger on a table? What I want to do is to send a row of
> > information to a table where the table: tblFileData has only one
> > column: txtOutput
> > I want to use the DB front end (MS Access) to send the text string to
> > the SQL backend, then have the SQL Server create a file to a path,
> > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > trigger deletes the row in tblFileData.
> > Can this be done easily?
> > Any help is appreciated[/color][/color]

[John Bell]

Re: Create a file using a SQL DB Trigger

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

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 407170613.22ad2 ee0@posting.goo gle.com...[color=blue]
> 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, txtOrderDateTim e.
> 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:\(txtOrderDat e)"-"(txtOrderNumbe r)"-"(txtEmployeeNa me).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
>
>
>
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message[/color]
news:<GO7Jc.282 2$Ec4.33496260@ news-text.cableinet. net>...[color=blue][color=green]
> > Hi
> >
> > I am not sure why you should want to do this. If the transaction is[/color][/color]
rolled[color=blue][color=green]
> > back then the file will have different information to the database[/color][/color]
table.[color=blue][color=green]
> > You may want to look at the stored procedure xp_cmdshell, such as
> > http://tinyurl.com/64azq. Use of the echo command will write the[/color][/color]
information[color=blue][color=green]
> > into a file select @sql = echo ' + @v + ' >
> > \\Myserver\MySh are\myfiledate. txt'
> >
> > Note: The example does not take into account multiple rows in the[/color][/color]
inserted[color=blue][color=green]
> > table.
> >
> > John
> >
> > "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> > news:47e5bd72.0 407131443.33a87 3bb@posting.goo gle.com...[color=darkred]
> > > Is there a way to create a text file (such as a Windows Notepad file)
> > > by using a trigger on a table? What I want to do is to send a row of
> > > information to a table where the table: tblFileData has only one
> > > column: txtOutput
> > > I want to use the DB front end (MS Access) to send the text string to
> > > the SQL backend, then have the SQL Server create a file to a path,
> > > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > > trigger deletes the row in tblFileData.
> > > Can this be done easily?
> > > Any help is appreciated[/color][/color][/color]

[Erland Sommarskog]

Re: Create a file using a SQL DB Trigger

Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> 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, txtOrderDateTim e.
> 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:\(txtOrderDat e)"-"(txtOrderNumbe r)"-"(txtEmployeeNa me).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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Lauren Quantrell]

Re: Create a file using a SQL DB Trigger

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, txtOrderDateTim e.
The output needs to be:
f:\(txtOrderDat e)"-"(txtOrderNumbe r)"-"(txtEmployeeNa me).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



Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns952B28 401363Yazorman@ 127.0.0.1>...[color=blue]
> Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=green]
> > 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, txtOrderDateTim e.
> > 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:\(txtOrderDat e)"-"(txtOrderNumbe r)"-"(txtEmployeeNa me).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.[/color]
>
> 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.[/color]

[Erland Sommarskog]

Re: Create a file using a SQL DB Trigger

Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> 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.[/color]

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.
[color=blue]
> It will not have to loop through rows[/color]

Since a trigger fires once per statement, and thus can cover many rows
your code must be able to handle multi-row inserts.
[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Create a file using a SQL DB Trigger

Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> 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?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Lauren Quantrell]

Re: Create a file using a SQL DB Trigger

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.db o.vOutput out "c:\output\orde r files\' +
@TemplateFileNa me +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master.dbo.xp_c mdshell @ExportCommand


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





"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<GO7Jc.282 2$Ec4.33496260@ news-text.cableinet. net>...[color=blue]
> Hi
>
> I am not sure why you should want to do this. If the transaction is rolled
> back then the file will have different information to the database table.
> You may want to look at the stored procedure xp_cmdshell, such as
> http://tinyurl.com/64azq. Use of the echo command will write the information
> into a file select @sql = echo ' + @v + ' >
> \\Myserver\MySh are\myfiledate. txt'
>
> Note: The example does not take into account multiple rows in the inserted
> table.
>
> John
>
> "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> news:47e5bd72.0 407131443.33a87 3bb@posting.goo gle.com...[color=green]
> > Is there a way to create a text file (such as a Windows Notepad file)
> > by using a trigger on a table? What I want to do is to send a row of
> > information to a table where the table: tblFileData has only one
> > column: txtOutput
> > I want to use the DB front end (MS Access) to send the text string to
> > the SQL backend, then have the SQL Server create a file to a path,
> > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > trigger deletes the row in tblFileData.
> > Can this be done easily?
> > Any help is appreciated[/color][/color]

[John Bell]

Re: Create a file using a SQL DB Trigger

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_pro xy_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







"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 407230525.2fc62 09b@posting.goo gle.com...[color=blue]
> 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.db o.vOutput out "c:\output\orde r files\' +
> @TemplateFileNa me +
> '" -T -c -S ' + @@SERVERNAME
> EXEC @ReturnCode = master.dbo.xp_c mdshell @ExportCommand
>
>
> I have granted execute permission to xp_cmdshell for both users and on
> the view.
>
>
>
>
>
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message[/color]
news:<GO7Jc.282 2$Ec4.33496260@ news-text.cableinet. net>...[color=blue][color=green]
> > Hi
> >
> > I am not sure why you should want to do this. If the transaction is[/color][/color]
rolled[color=blue][color=green]
> > back then the file will have different information to the database[/color][/color]
table.[color=blue][color=green]
> > You may want to look at the stored procedure xp_cmdshell, such as
> > http://tinyurl.com/64azq. Use of the echo command will write the[/color][/color]
information[color=blue][color=green]
> > into a file select @sql = echo ' + @v + ' >
> > \\Myserver\MySh are\myfiledate. txt'
> >
> > Note: The example does not take into account multiple rows in the[/color][/color]
inserted[color=blue][color=green]
> > table.
> >
> > John
> >
> > "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> > news:47e5bd72.0 407131443.33a87 3bb@posting.goo gle.com...[color=darkred]
> > > Is there a way to create a text file (such as a Windows Notepad file)
> > > by using a trigger on a table? What I want to do is to send a row of
> > > information to a table where the table: tblFileData has only one
> > > column: txtOutput
> > > I want to use the DB front end (MS Access) to send the text string to
> > > the SQL backend, then have the SQL Server create a file to a path,
> > > such as F:/myfiledate.txt that holds the text in txtOutput, then the
> > > trigger deletes the row in tblFileData.
> > > Can this be done easily?
> > > Any help is appreciated[/color][/color][/color]