Want to write from a table variable to a text file from a stored procedure.Read about xp_cmdshell bcp etc. but worried because it's supposed to be a security problem and needs to be from a permanent database.Also am getting error "The EXECUTE permission was denied on the object 'xp_cmdshell'..."

1. Is xp_cmdshell a bad idea to use even if I get permissions ?2. Can a "permanent" table be used in a stored procedure starting out fresh each time with 0 rows rather than use a table variable ?

Another way to do it to use SSIS /Export import wizard to create a package to do the data export to text file and call it from sql server job. Then in your sp use sp_start_job to launch the job and do data export.