jwcolby
jwcolby at colbyconsulting.com
Thu May 14 12:39:51 CDT 2009
Fred, That certainly seems like a good thing to do in any case, to log the "progress" of the application in a common place. I am hesitent though to replace a true error handler with a log table. John W. Colby www.ColbyConsulting.com Fred Hooper wrote: > 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 >>