Is there an alternative to xp_cmdshell BCP to write to a file from MSSQL?
Some context: This is to be built into a stored procedure called by an external program. I do not have the luxury to work through executable files, export functions of ssms, or any such things which require more than the calling of this stored procedure.
The reason; there's a lot of odd stuff on this server to do with user rights, and I'm not the SA. I cannot create the ##xp_cmdshell_proxy_account## (let alone assign credentials to it), and xp_cmdshell 'whoami' returns a user noone has ever seen or heard from. I've tried creating a new user based on an existing windows user and granting xp_cmdshell execute rights to it, but this still did nothing. I'm not sure if I don't have the rights or if it's something else.
So long story short, I'm fed up with trying to get this to work on this environment and am looking for an alternative. Is there one?
Write a SQL Agent Job and kick it off with sp_start_job. You can control the identity the job uses with a SQL Agent Proxy.
Or write an SSIS package, deploy it to the SSIS Catalog and run it from your stored procedure.