October 14, 2008 at 7:32 am
Our shipping software looks into a folder for text file names that match a reference number we scan into the app... ie 013234.txt or 9871234.txt. So if I scanned in 0123321 then it would look into that directory and pull the information out of the 0123321.txt file and populate it into the shipping information than create a shipping label. At this point I have the process to create these files automated with Access however Access seems to go to sleep from time to time and I wind up having to go to the machine running the app and jiggle the mouse to get it working again.
I have done some research on triggers and I think it may be a good solution for the problem. At this point I am thinking I need to setup a trigger on INSERT on the table where our pickslips are created and then have that trigger create the csv file and drop it into the correct folder.
How would I go about getting it to create the properly formatted csv file in the proper directory?
October 14, 2008 at 7:44 am
Actually, since you posted this in a SQL Server 2005 forum, this looks like a good place to use Service Broker. Creating the csv file directly in a trigger could result in the loss of data should there be a failure during the process.
Wish I could help further, but I haven't had much opportunity to dig into Service Broker even though I have a project waiting to use it. Fighting fires and other priorities have kept it on the back burner.
😎
October 14, 2008 at 8:07 am
kellyrmartin (10/14/2008)
At this point I have the process to create these files automated with Access however Access seems to go to sleep from time to time and I wind up having to go to the machine running the app and jiggle the mouse to get it working again.
This sounds like a problem with your Power or EnergySaver settings.
How would I go about getting it to create the properly formatted csv file in the proper directory?
Well first, Lynne is right, you don't want to do file work inside a trigger, and Service Broker is to solution to that. If you go to my Blog (www.MovingSQL.com) the most recent post has the slides and SQL code from a Service Broker presentation that I did at the Philly.net code camp this past weekend, including how to setup Service Broker to effectively do asynchronous triggers. If you take a look at that and still have questions about how to do that part, let us know.
As for creating the output files (in your Service Broker Queue's receive procedure), there are 3 ways that you can go:
1) xp_CmdShell to bcp.exe
2) SSIS
3) .Net code (CLR or command-line app)
I have done both (1) and (3), however, I am not familiar with how to do SSIS (2) from T-Sql, so I will let someone else address that.
(1) is the most straightforward and probably the fastest, however, BCP's formatting abilities are a little limited and you may not be able to get exactly the format that you want. On the other hand you may be able to pre-process the data through a view or a staging table to compensate for this.
(3) is certainly do-able, however, you should be comfortable doing .Net development before you consider this. If you do, then myself and others can probably come up with plenty of examples to start from.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply