I am able to export to excel through a stored procedure.But when i
open it the datetime column and int columns are not getting recognised.

On Mar 27, 5:08pm, nalamve...@gmail.com wrote:
> I am able to export to excel through a stored procedure.But when i
> open it the datetime column and int columns are not getting recognised.

This is the code i wrote:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[spExportData]
(
@dbName varchar(100) = 'Employees',
@sql varchar(5000) = '',
@fullFileName varchar(100) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
drop table ##TempExportData
if object_id('##TempExportData2') is not null
drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000),
@tempSQL varchar(8000)
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into
##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') +
column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') +
'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime',
'smalldatetime') then ',101'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
-- execute select query to insert data and column names into new temp
table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2
from (select ' + @columnConvert + ', ''2'' as [temp##SortID]from
##TempExportData union all select ''' + replace(@columnNames, ',',
''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from
##TempExportData2" queryout "' + @fullFileName + '" -c -t"," -CRAW'

-- execute BCP
Exec Employees..xp_cmdshell 'bcp "Employees.dbo.EmpDetails" OUT "d:
\test5.xls" -Slocalhost -T -n -r\n -t"|" -q'
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
select 1 as ReturnValue -- success

Exec Employees..spExportData 'Employees','select * from EmpDetails','d:
\test5.xls'
declare @sql varchar(6800), @dbName varchar(100), @fullFileName
varchar(100)
select @dbName = 'Employees', @sql= 'select * from EmpDetails',
@fullFileName = 'd:\test5.xls'
exec Employees..spExportData @dbName, @sql, @fullFileName