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.