asked on

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

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

D B

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.

ASKER CERTIFIED SOLUTION

Daniel Reynolds

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsuessmeyer

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

ShogunWade

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.