[Pam Palmer]

GP 2018
We are attempting to create an SQL trigger that will create a file folder on the server each time a new order is created in our GP.

The purpose is so that when order entry creates a new order, the folder is automatically created.  In this folder will go all quotes/takeoffs (excel), quotes from vendors, copies of packing slip/motor freight reports etc.  Anything related to the order.

While we could use the doc attach feature within GP to accomplish all of this, our employer is wanting to have a separate folder on the server for even non-GP users to access.

Our IT guys is struggling with the trigger and we feel it has something to do with permissions.

If there are any sql people out there interested in offering any advice, it would be appreciated.

Thank you.

——————————
Pam Palmer
Project Mgr/Database Admin
Temple, Inc.
Decatur AL
——————————

[Charles Allen]

The issue is most likely due to the trigger running under the security context of a SQL login which doesn’t have Windows permissions. 

Here’s a link to the Execute As functionality in a trigger: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15. 

Hopefully, this info will help the developers.

——————————
Charles Allen
Senior Managing Consultant
BKD Technologies
Houston, TX
——————————
——————————————-

[David Joosten]

Pam,
Sometime back, was maybe as far back as SQL 2000, there was a server configuration option added to SQL server which needs to be enabled for SQL to use “xp_cmdshell”, which is what is used to run command line. It was done as it is a huge security risk to allow a SQL script to run command line scripts. Personally, I do not recommend enabling this option because of the security risk. The link to the Microsoft documentation on the option and the feature are below: –

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15

In the xp-cmdshell document, you will also see around halfway down how you can run that with specific permissions.

I would also suggest that you should not do this as part of a trigger, but rather run a “frequent” SQL job that will look for new orders and then create the folder. That way you are not affecting the performance and stability of GP.

I will say that if you wrote some fairly straight forward script in PowerShell or C# or Python, you could do the same thing running from windows task scheduler that will create the folder structure for you.

If you are a O365 user, I would also say that using SharePoint online is probably the most flexible option for this and you could use Power Automate to auto create the folders for you.

If there are any of these options that you would like more detail on, please reach out.

——————————
David Joosten
IT Executive
Premier FMCG (Pty) Ltd
Midrand
——————————
——————————————-

[Joseph C. Markovich]

Hi Pam-

This could be something really simple — a VBA or VB.net customization to create a button on the Sales Transaction Entry form. When a person clicks the button, it can check the server location for the folder. If not there, create it. If it already exists, it just opens for the user.

If you want to take this to the next level (and be even cooler then you are now!), you can use a combination of a button on the GP form and SharePoint and OneDrive for this. The OneDrive client lets you sync a document library on SharePoint to the local computer. It creates a copy of the document library (as just links) on the user’s PC. Any time something is dropped into any of the folders, it will sync it to SharePoint automatically. 

The non-GP users can just access this information from any device now — mobile phones, Microsoft Teams, web browser.

I hope this helps a bit.
Joe

——————————
Joseph C. Markovich
Valparaiso IN
——————————
——————————————-