Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-12-04
4
Medium Priority
?
5,378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

618 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