I've been using xp_delete_file
to delete old backup files but I now an a need to delete files that are part of an application (as part of GDPR). As xp_delete_file
only will only let you delete log or backup files, I thought I'd write my own in C#.
The trouble I'm finding now is extended stored procedures can only be rolled out to the master database. I'd rather contain this new stored procedure in my own user db so I can distribute via source control etc.
Does anyone know a way round this without using xp_cmdshell?
It could be done in PowerShell. I could even write the entire thing in C#. It however be really handy to be able to do it in T-SQL as well.
I am open to using a CLR stored procedure that invokes C# code. I was thinking of changing the stored procedure to a function if I can't deploy a stored procedure.
Best Answer
Extended Stored Procedures (XPs) have been deprecated as of the release of SQL Server 2005 and new projects should not be using that API.
This can be done easily via SQLCLR, and yes, you can return a string (or most datatypes) from a C# method. The only construct restricted to returning an INT is a Stored Procedure, and that holds true for both T-SQL and SQLCLR Stored Procedures.
You would use something along the lines of:
When you create the T-SQL wrapper object, be sure to specify the
RETURNS NULL ON NULL INPUT
option so that it won't even both executing if aNULL
is passed in (you should probably add additional validation to the method, such as returning if an empty string is passed in, etc):Since the Assembly will need to be marked as
PERMISSION_SET = EXTERNAL_ACCESS
, please do not set the Database toTRUSTWORTHY ON
. Instead you should sign the Assembly, create the Asymmetric Key or Certificate in[master]
, create a Login from that Asymmetric Key or Certificate, and then grant that Login theEXTERNAL ACCESS ASSEMBLY
permission (if using SQL Server 2005 - 2016) or theUNSAFE ASSEMBLY
permission (starting with SQL Server 2017).For detailed instructions for doing this via a build process, you can try either of the two techniques I describe in the following blog posts (both work fully with SSDT or independently):
Also consider supporting my Asymmetric Key suggestion which would eliminate most of the hassle of publishing signed Assemblies:
Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE
OR, if you would rather not deal with any of this, there is a File_Delete function in the SQL# SQLCLR library (that I wrote) that does this. It even returns a string, which is the error message if an error occurs, else is just an empty string. No way to have it pass back a custom string on success, though. Also, please note that while there is a Free version, the File System functions are only available in the Full version. But the security aspect would all be handled cleanly and properly, and there is even a T-SQL Stored Procedure, that exists in the same DB as the SQLCLR code, that will set up the
Asymmetric Key
and associatedLogin
in[master]
(which could help with deployments to new systems by not requiring a full install, as long as this Stored Procedure was deployed and executed prior to deploying any Assemblies (assuming an environment of SQL Server 2017)).UPDATE
SQL Server 2019 (which was not available at the time I posted this answer) introduces a new, undocumented system stored procedure,
xp_delete_files
, that is a bit more flexible thanxp_delete_file
. For more info, please see my answer here (also on DBA.SE):How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?