T-SQL writing to a file txt or csv

Question

I've spent all day scouring the net on answers. Apparently tsql doesn't have its own nifty write to file commands. Here is my dilemma

I have a load file that I am creating where a single line can reach 10K+ in length. On SQL Server varchar(MAX) limit is 8000 (so I believe) so I broke those lines into several variables. I tried to do PRINT but the window pane has allows 4000. The workaround is to print those broken lines one variable at a time but that can get tedious for manual labor so I opted to look into writing it into a txt file one variable at a time.

I looked into BCP via xpcommandshell and it looked promising. Issue was that I could get this line to work on the command prompt yet that exact same line doesn't work on TSQL query:

declare @cmd varchar(8000)
select @cmd = 'bcp Client_DB "Select name from dbo.t_TagBuild_set" queryout "Desktop\LAMB\dummy.txt" -c -t, -T'
exec master..xp_cmdshell @cmd

bcp Client_DB "Select name from dbo.t_TagBuild_set" queryout "Desktop\LAMB\dummy.txt" -c -t, -T works perfectly fine on command prompt

despite this slight progress, my manager didn't want to go that route. So instead I opted for sp_OACreate and sp_OAMethod after enabling sp_configure via executing this line on SQL: sp_configure 'Ole Automation Procedures', 1

One of the very first lines on this route is this:

EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

@hr gives a 0 so that's good but @objFileSystem yields 16711422 and @hr eventually becomes -2146828218 which i believe is permissions.

i really am at a loss on finding something simple to do yet i've made this increasingly difficult on myself to find something concrete just to write to a couple variables in a row before adding a new line and repeat the process.

If anyone can expertly help me figure out BCP or sp_OACreate then I'd be very appreciative cause the net as is barely helps (and this is after I spent a lot of time looking through Microsofts own site for an answer)

Answer 1

The reason your BCP didn't work is because you were running it from xp_cmdshell with a trusted user. xp_cmdshell is not run under the user running the script. You can either a) change your bcp command to use a sql login/password or b) create a job to run it (not xp_cmdshell) because you can control what user it is run as by using run as and a credential. You can then launch the job within a script by using sp_start_job.

Your other good option is to create an SSIS package and either run it through the command line (say in a bat file) or again run it through a job.