Writing T-SQL to txt or csv file
I spent all day cleaning up the answers. Apparently tsql doesn't have its own great commands to write to a file. Here is my dilemma
I have a download file that I create where one line can be up to 10K + in length. In SQL Server varchar (MAX) the limit is 8000 (so I believe), so I split these lines into multiple variables. I tried to do PRINT, but the window window allows 4000. The workaround is to print these broken lines one variable at a time, but this can get tedious for manual labor, so I decided to look at writing it to a txt file. time.
I looked at BCP via xpcommandshell and it looked promising. The problem was that I could get this line to work on the command line, but the exact same line does not work in the 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 fine on command line
despite this little progress, my manager was reluctant to take this route. So instead I chose sp_OACreate and sp_OAMethod after enabling sp_configure by executing this line in SQL: sp_configure "Ole Automation Routines", 1
One of the first lines of this route is:
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
@hr gives 0, so good, but @objFileSystem gives 16711422 and @hr ends up being -2146828218 which I believe is the resolution.
Actually I'm in a quandary when I find something simple, but I'm all at a loss to find anything specific, just write a couple of variables in a row before adding a new line and repeating the process.
If anyone can skillfully help me figure out BCP or sp_OACreate, then I would really appreciate the web just helping (and that happens after spending a lot of time searching Microsoft's own site for an answer)
source to share
The reason your BCP is not working is because you ran it from xp_cmdshell with a trusted user. xp_cmdshell does not run under user script. You can either a) change the bcp command to use the sql login / password, or b) create a job to run it (not xp_cmdshell) because you can control which user it starts, both with run as and credentials. Then you can start the job in the script using sp_start_job.
Another good option is to create an SSIS package and run it through the command line (say in a bat file) or run it again through a job.
source to share
Create a view of your request and select it with 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 title
SET NOCOUNT ON
removes lines affected by the footer
source to share
You can write to a file in T-SQL using this (it works in a 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
source to share