Exporting SQL Query results to Excel

Question

On executing the below mentioned statement:

EXEC proc_generate_excel_with_columns 
     'your dbname', 'your table name','your file path'

I'm getting the following error.Can anyone help?

User name not provided, either use -U to provide the user name or use -T for Trusted Connection usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-V file format version] [-q quoted identifier] [-C code page specifier] [-t field terminator] [-r row terminator] [-i inputfile] [-o outfile] [-a packetsize] [-S server name] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"] [-x generate xml format file] [-d database name] NULL

My procedure is this:

create procedure proc_generate_excel_with_columns
(
    @db_name    varchar(100),
    @table_name varchar(100),   
    @file_name  varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
    @columns=coalesce(@columns+',','')+column_name+' as '+column_name 

from 
    information_schema.columns
where 
    table_name='dbo.vcuriosoftronic.tblPayrollGroups'
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring('D:\TestFile.xls',1,len('D:\TestFile.xls')
                            -charindex('\',reverse('D:\TestFile.xls')))
                            +'D:\TestFile.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp
          " select * from (select '+@columns+') as t"
          queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp
         "select * from [myserver]..'+@table_name+'"
         queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

Answer 1

Check out my blog article on how to use BCP to export data.

http://craftydba.com/?p=1690

The below snippet uses the -T, trusted connection. If you are running a job under the agent, it will run under that security account.

Please either pass the standard security credentials, -U -P or make sure the account has the ability to run the command.

-- BCP - Export query, pipe delimited format, trusted security, character format
DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) = 
    ' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';
SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT FirstName, LastName FROM AdventureWorks2008R2.Sales.vSalesPerson" queryout ' +
    ' "C:\TEST\PEOPLE.TXT" -T -c -q -t0x7c -r\n';
PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4;
GO

Updated assuming the BCP path is in search list. Below is a screen shot with the path removed and the query changed for SQL Server 2012.

Look at the message window, it has the BCP command from the print statement. You can put the command into a batch file to test from the DOS prompt. It is a debugging exercise for you.