Open Forum

Expand all | Collapse all

SQL trigger to create a folder on the server?

  • 1.  SQL trigger to create a folder on the server?

    GOLD CONTRIBUTOR
    Posted 14 days ago
    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
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: SQL trigger to create a folder on the server?

    TOP CONTRIBUTOR
    Posted 13 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: SQL trigger to create a folder on the server?

    GOLD CONTRIBUTOR
    Posted 13 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: SQL trigger to create a folder on the server?

    TOP CONTRIBUTOR
    Posted 11 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role