• xp_cmdshell does not generate any text file in itself, but you run something from xp_cmdshell that generates a text file.

    You could just as well as run the same thing from a client program. If this is a stored procedure that you run from a job, you can instead run BCP or whatever it is from a CmdExec job.

    If you want to keep the stored procedure, because it prepares some data for the export, you can write a CLR stored procedure which spawns a process and executes the command. The advantage over xp_cmdshell is that the CLR procedure runs a specific command which is hardcoded into it; it cannot be used for attacks.

    But even better may be to write the file directly from C#. This takes a little longer time to develop, but gives you more flexibility if you need a format that BCP cannot offer.

    In both these cases, the assembly needs to have EXTERNAL_ACCESS permission, which requires some red tape with signing and server-level permissions. But once you have set up, you have something which is better than today.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]