?
Solved

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

Posted on 2007-12-04
4
Medium Priority
?
5,381 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:
Daniel Reynolds earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 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