What method for user trigger SQL script in GPv6

[markajem]

GPv6
MS SQl 2000

I needed to write a special sql script to update records for a specific customer that transmits their orders EDI. Without going into further detail about that; what I need to know is what would be the easiest way for a GP user to be able to trigger my sql code when needed without having to go into SQL Qry Analyser. I do not want him or the other person being able to do that. Would a VB do it. I dont have much experience in that area but would be willling to give it a shot if need be.

Any thoughts would be greatly appreciated.

 

[clicker666]

I wouldn't use VB, SQL stored procedures will likely do what you need to do.

What I have done to figure out my triggers is this:

Run a SQL trace, looking for a specific user. With you logged in as that user, get your GP to the point just before you want to trigger. Start the trace and begin the GP activity you want the trigger to hit. Stop the trace and look through the results to see what was affected when you performed the trigger action.

I then toss a reference to a stored procedure in the existing SQL.

 

[markajem]

I'm sorry, I think I miss lead you. I did not want to create a SQL Trigger persay, I wanted to create some type of code that a user can run when he needs to update the records. It won't always be at the same point in his process; this will vary depending on the situations. I just don't want to give him access to SQL Qry Analyser. My only other alternative would be that everytime they need to run this update they will have to contact the MIS Department and we will not always be able to run it at that moment for them.

Any other options than the one you mentioned? It also does not have to be within the GP environment.

Thanks

 

[clicker666]

Oh! Let me see if I understand you.

You want a user to be able to say, run a VB app, that will update some back-end SQL data? I'd be careful when messing with GP data through a custom front end, but it's certainly something that can be done. I mostly use my VB apps to pull reports up that are just too painful to grab out of GP. I like the control I can use in retrieving data. I've got one for some customer reporting and one for Wennsoft Service Manager turn-around times.

 

[markajem]

Yes, you got it. My VP application would run the SQL script when the data needs updating. We are very sure of this data and that it can be updated thru the backdoor. It only effects one table and we know for a fact that those fields are not in any other table that would also need updating. it's just that because of our integration and our pilehigh customization that this particular assignment is best done this way. Our EDI customer transmits a particlar code (numbers) to us. It is now being captured in the header commentxt. This script takes that code for the order and populates a field in one of our integration tables by the line items in the order. Then after that it is released to our WMS system where that database is then populated. So basically now this whole thing works if we run the script by hand but some days we may not need to do it and some days if updates to the existing order need to be made it could be run several times and if no one in MIS is available it holds up the warehouse from processing the order. So I just need a means of allowing a user to run the script from the front end.

 

[clicker666]

Shouldn't be a big problem, the ability to run SQL scripts is built into VB. You'll just need to make a database connection. I would just basically make a front end that runs a script with a button push, or if you desire something that automatically runs and exits using a schedule. (say AT)