Good day everyone.
I hope someone can help me on that one.
I have a database that include file folder and document location into tables and the actual document is store on a document server.
I would like to copy specific documents to a different location and rename the document to a more readable name using SQL Server.
I know I can use SP_COMMANDSHELL, OLE automation to do that or the xp_copyfile in SQL 2019 to do that but in all case it does not create the folder structure.
I have tried to create a command line option using xcopy option and save that to .bat file and execute but I am ask if this is a file or a directory for every files. I have than tried to use a robocopy command but it does not rename my file. This is when I tried to use the OLE automation to copy the file. All the options work fine to copy the files but either I have to press something to tell it is a document or I have to create the folder structure.
What I would like to do is simply get the files out and create the folder structure and rename the file name to a more readable name without any interventions.
Would anyone have any Idea on how I could do that?
Let me know if I am not clear.