Solved

Create text file in SQL Server via trigger and stored procedure?

Posted on 2007-12-04
4
5,345 Views
Last Modified: 2012-06-27
Hi

I am working on migrating to a Warehouse Management System, which has an SQL Server 2005 back end

At the moment, i have an external program which creates carrier labels, based on a fixed width text file that gets produced by the current Firebird databae, via a stored procedure, called by a trigger

I need to do the same thing in SQL Server. It will be a trigger on a table update, and that trigger will call a procedure, and within that procedure the data will be formatted, padded to the appropraite widths, and then exported to a text file to a specified location

I have 2 questions..........

1. can this be done?
2. if so, how!?

many thanks
0
Comment
Question by:seancurt
4 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 20406297
The one problem I see is that an UPDATE can affect MANY records (UPDATE myTable SET LastName = 'Smith' WHERE LastName = 'Jones' would affect every row that had a last name of 'Jones'

I can give you the process that I would try to use to accomplish something like this (realize there could be contention problems if two people update the table at the same time):

Create a table [myStagingTable) with columns for an identity column (StageID), the primary key of the table you are updating (UpdateKey) and something like a RequestID. in your trigger code something like:

DECLARE @RequestID INT

SELECT @RequestID = MAX(RequestID) + 1 FROM myStagingTable
INSERT INTO myStagingTable (RequestID, UpdateKey)
SELECT @RequestID. PrimaryKey FROM Inserted

I have SQL Server 2000, so I am not familiar with SSIS in 2005, but in 2000, you can execute a DTS package and specify the value of a global variable on the command line. Assuming the same is possible in SSIS, execute a package that passes @RequestID as a global variable value that has been defined in the package. You can then use an ActiveX task with the file system object to create your formatted output file. Going against myStagingTable you can get the PK of the updated records in the main table.

hth.
0
 
LVL 17

Accepted Solution

by:
xDJR1875 earned 500 total points
ID: 20406353
See if xp_cmdshell provides any usefullness.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=373638&SiteId=1

examples:

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

and

DECLARE @cmd sysname, @var sysname
SET @var = 'dir/p'
SET @cmd = @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd

0
 
LVL 2

Expert Comment

by:jsuessmeyer
ID: 20406437
Hi and hello to the forums,

I would not suggest to do that, the problem is that triggers are fired synchrinously in SQL Server, meaning that it would block the table / other user action fomr being executed until the process has executed. Having a problem with the triggered process and if it throws a sever error, the whole transaction (depending on your error handling) could be rolled back. The better solution would be to create the "job" to do the processing, oicking up non-processed entries on a reccurring scheduled basis.

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 20409950
I'd concur with Jens.   Personally I woud wrap the call up in a stored proc together with the insert and cal the stored proc rather than have it fired on a trigger.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now