March 20, 2023 at 1:37 pm
Hi all,
I am running the following query in SQL, trying to export some data to a csv file, with header.
When I try to execute it from SQL server, it exports only the header and not the row data as well.
When I print it and execute it from cmd, the file contains the header and the row data as well.
Any ideas what could be the problem?
declare @getdate-2 as nvarchar(8)
set @getdate-2 = (select convert(nvarchar(8),getdate(),112))
DECLARE @sql_e varchar(4000),
@stm varchar(4000),
@outputDir varchar(255),
@CsOutputFile varchar(200)
set @sql_e = 'select ''Field_1'', ''Field_2'', ……., ''Field_n'''
set @sql_e = @sql_e + ' union all select convert(nvarchar(50), Field_1), convert(nvarchar(50), Field_2), …………, convert(nvarchar(50), Field_n)'
set @sql_e = @sql_e + ' from Database.dbo.Table_Name'
set @outputDir = '\\XXX\'
set @CsOutputFile = 'File_Name_' + @getdate-2 + '.csv'
set @stm = 'bcp "' + @sql_e + '" QUERYOUT "' + @outputDir + @CsOutputFile + '" -c -C1253 -t ";" -r \n -S ' + @@SERVERNAME + ' -T'
--select @stm
EXEC master..xp_cmdshell @stm
March 20, 2023 at 2:40 pm
Security. Your login has different privs than whatever process you're running under through the server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2023 at 2:49 pm
So, what kind of privileges should be given?
March 20, 2023 at 3:43 pm
Whatever will make the behavior the same. I can't tell from where I sit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2023 at 5:22 pm
where is the output folder located? The domain user which is running the SQL Server Service needs access to that folder. Ask your NS team to provide access to the same... for read/write/delete/create files.
=======================================================================
March 21, 2023 at 7:37 am
@Emperor100
In this case, I suppose neither the file with just headers would be exported, correct?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply