Hi,
we are trying to develop ach origination (bank direct debits and deposits) right from sqlserver without using ach software...our mgmt team rightly points out "...why use a separate software that you'll have to transfer your data into when sqlserver has all the capabilities to build the files and create tramsmittable, formatted messages.
Does anyone know of any examples...like best practices for setting up the headers (file header, batch header, etc) for multiple re-use...or any sample execs for creating the files on the fly? -- we're experimenting with our own functions for this and will share what we create, but any examples from anyone who has been working on something similar would be great to look at.
Thx much,
Haven't done ACH, but I've created stored procedures to generate some batch files for mainframe consumption before (2 columns per row), one is record id, one is data. Built up a temp table like that, then selected out of the temp table ordered by record id. I wouldn't say it's the greatest solution in the world, but it worked.
SQL Server really is the back end database. It's for storing a retrieving data. The application is (SHOULD) responsible for presenting (formatting) that data into whatever you want. You can use T-SQL (Or I guess the built-in .NET stuff now) to pre-format the data, but I would argue that mixes the data and presentation layers which is against good design practices. Others may argue that the tables, etc are the data layer, and the stored procedures are acting as the presentation layer.
