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)

Solution 1

Create a view of your query and select it using sqlcmd.

declare @cmd varchar(8000)
select @cmd = 'sqlcmd -h-1 -W -S servername -d database -U username -P password -Q "SET NOCOUNT ON; select * from VIEW_NAME " -o "C:\OUTPUT\query.csv" '
exec master..xp_cmdshell @cmd

-h-1 removes the header

SET NOCOUNT ON removes the rows affected footer

Solution 2

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.

Solution 3

You can write to file on T-SQL using this (it works into trigger):

--char(9) = \t

    DECLARE @filename nvarchar(1000);

    SET @filename = ' (echo '+@parameterA+ char(9) +@parameterB+ ... + char(9) +@ParameterN+') > e:\file1.txt && type e:\file1.txt >> e:\file2.txt';

    exec DatabaseName..xp_cmdshell @filename, no_output

Comments

[Miguel Bessa]

@John M I could help you?

[Dan]

probably the folder needs access from mssqlserver or whatever the service user is.

[jMarcel]

you have no idea how your solution helped me! Thanks a ton!!!!