POS Configurator + Stored Procedure

[Uofmlisa]

I am new to Micros 3700 and although I have been pretty good at figuring out the database tables, there is one thing that I am stumpped at. I have created a couple of stored procedures to run payroll data. I was told that I could schedule them in the POS Configurator to run the sp's on a scheduler. When I select for something to pull from a sp, it does not list the sp for me to run. Now, I have found the table that lists the sp's and the name they have in configurator and I could just add it but I have not gone so far to see what inserting a line would do to other tables connected because I would think there is a way to add it to the sequence of numbers in the list. I cannot find a way to do this. Is there a way or do I just need to insert that into the table of stored_proc_def? Any assistance would help.

 

[pmegan]

You do have to add a line to the stored_proc_def table. Micros reserves sequence and object numbers under 10,000 for expansion so insert with numbers higher than that.

The only other way to do it would be to write a batch file or script that calls your sprocs with dbisql.exe. This would be added as an external program which could then be called from an autosequence.

 

[Uofmlisa]

Ok, I have done this much and the seq numbers are in and I have even learned how to kick it off to run. Now my question is that one of my stored procedures needs to produce the results and export to a CSV file to be emailed... Any direction on this would be helpful. Thanks.

 

[pmegan]

Depending on the version of Res you're running you can:
1) Use an "output to" statement after your query
2) Insert your data into a temp table inside the stored procedure and unload it
3) If you're using Res 4.6 or better you can unload a query directly without creating a temp table. I prefer this way, it's cleaner, faster, and you can use a variable for the output file name.

If you can reuse the same filename it's easy, just hard code it. If you have to change the output filename with a timestamp, RVC, etc... then #3 would be the easiest way to do it. If that's not possible you'll have to generate the whole query on the fly and run it with an execute or execute immediate command.

 

[Uofmlisa]

Thank you for your response. I did not see this response before posting a thread on the "UNLOAD TABLE" issue that I am now having. I need to call this within a stored procedure but it is giving me a Syntax error near 'UNLOAD' when I try to create the SP. If I run the UNLOAD TABLE by itself it is fine but the second that I put Create Prodedure sp_myproc AS... and then the UNLOAD TABLE statement, it hits me with that error. Thoughts?