Fred Hooper
fahooper at gmail.com
Thu May 14 11:09:10 CDT 2009
Not a solution but a workaround: When I've had a similar problem I create a Messages table in the db and have the procedure append a message to that table. My app just looks at that table periodically and responds accordingly. Fred On Thu, May 14, 2009 at 11:03 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > I have designed a system that uses stored procedures on SQL Server to do a > rather complex (to me) > task. I am building a table with a specific base set of fields, then I > dynamically add a new set of > fields to that table based on selection criteria fields in a view, I append > data to the table, then > I add indexes to this table, export the table to CSV, run the CSV file > through an external program, > import a resulting CSV file back into SQL Server, dynamically build views > in SQL Server, and finally > export the resulting data back out to a flat file. > > This process requires about 20-30 stored procedures. I have been using a > stored procedure that > calls the other stored procedures but this has become unwieldy. Plus the > external program runs in > one or several virtual machines and I need to be able to monitor the output > directories on these VMs > for the resulting output, and then execute the next set of SPs to get the > data back in to SQL Server > and finish the process. > > I have been using Access to execute a subset of these stored procedures. I > run the SP that runs > other SPs, then I switch to an Access app (I wrote) to do the export / > import process, then another > Access App (I wrote) to do the final flat file export. > > Now I need to string all this stuff together and use Access VBA to make > decisions on which stored > procedures to call, how often etc., iow do it all from one place. I > understand how to do all of > that but I am flying blind in that I do not yet know how to return a value > from a stored procedure > to Access, particularly an error if any, but also perhaps things like > number of records effected and > so forth. In general the SPs just work, but when they do not the result is > an empty file at the end > and no clue about what specifically went wrong. Not a good way to be > working. > > I am using some code provided by Charlotte (I believe) Basically it just > uses the ADODB.Command > object to build up everything including a variable list of parameters to > pass in to the SP. > > My understanding is that there are two ways to get data back from a stored > procedure, one is to > declare any of the input parameters as an OUTPUT and then place values in > that parameter inside of > the stored procedure. Another is that the SP can return a single value, > kinda like a function in > VBA. I have fiddled and screwed around but I do not know enough about > either of these things to > actually make it happen. > > I have reached the point in my control of SQL Server from Access that I > need to take this next step > and I am not finding what I need on the web to explain it. > > As I said, ATM I am using a single "Generic" VBA function that allows me to > pass in the name of the > server, the database, the SP, and then a variable list of parameters. I am > reluctant to hard code a > function for each of these stored procedures since there are so many of > them and they can change as > I discover bugs or add functionality. OTOH I get the feeling that > declaring a variable output might > take some special syntax that will not play well with a generic "list of > parameters" passed in to > the VBA function. > > Does anyone have any code, both the VBA side as well as the SP side that > does this stuff? Can > anyone write a short but actually usable description of how to do this > stuff. > > I will post the generic VBA function I use in a follow up email since the > code is not short. > > TIA for your assistance. > > -- > John W. Colby > www.ColbyConsulting.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >