September 14, 2021 at 3:09 pm
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.
September 14, 2021 at 4:56 pm
Be careful using undocumented procedures like xp_copyfile, Microsoft can and will change them and the behaviour resulting in breaking changes for you.
You will be best to write your own CLR or other routines to do the moving for you.
September 14, 2021 at 5:39 pm
Personally, this feels like a job for powershell, not SQL Server.
Just because SQL Server CAN do it, doesn't mean it SHOULD do it.
SQL Server is a database engine and you are asking it to make changes to the filesystem. If you wanted to store the previous and new locations in the database, I see no issue with that, but I would be looking at a non-database tool (custom code in a .NET application or powershell or something) to handle actually moving the files.
The advantage to using .NET or Powershell is that their filesystem libraries are documented and VERY unlikely to change without notice. Undocumented database features MAY change or be removed without notice.
Writing your own CLR as Ant-Green suggests is another option, but I would still rather pick the right tool for the job and a database engine just feels like the wrong tool for filesystem management.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 14, 2021 at 5:47 pm
I tend to agree with @Mr. Brian Gale PowerShell is going to be way better for filesystem management.
Too many unknowns when you go the other route.
I am an AWS Certified Security Cloud Architect @ iKooru
You can connect with me on Linkedin
Hit me up if you want to do a new product launch
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply