
February 29, 2012 22:17 by
btsguy
2f7db943-1b0c-41e5-8cb2-3d3912115146|0|.0

February 29, 2012 21:09 by
btsguy
f95b7c0e-57ac-433e-a884-29c7ce61e3c8|0|.0

February 29, 2012 21:08 by
btsguy
c210a5a5-0e4a-43c8-8a8d-8a60fe639a1a|0|.0

February 29, 2012 19:47 by
btsguy
caef40a2-5268-4124-8226-0244d9da1c1a|0|.0

February 23, 2012 21:07 by
btsguy
f71e736e-c2d5-42a5-82ac-5bc7772cf38e|0|.0

February 23, 2012 19:17 by
btsguy
c0c24ad7-8c40-4705-9c31-3b57bfc6e246|0|.0

February 23, 2012 17:47 by
btsguy
18ada715-abeb-4a84-a985-efb1938a2007|0|.0

February 16, 2012 21:05 by
btsguy
alter procedure usp_Export2ExcelVS
(
@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
from
information_schema.columns
where
table_name=@table_name
select @columns = '''''' + replace(@columns,',',''''',''''') + ''''''
print @columns
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
print @data_file
--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select '+@columns+' as t" queryout "'+@file_name+'" -c -T -S "<server>"'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T -S "<server>"'''
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)
GO
/*
exec usp_Export2ExcelVS 'Northwind','OrderHistory','c:\temp\excel.xlsx'
*/
d87948d2-1083-4c75-9fd3-fefca8c6d5b5|0|.0

February 16, 2012 16:26 by
btsguy
ebf88298-3071-4614-9618-4bb6c554dd0f|0|.0

February 14, 2012 18:26 by
btsguy
8558d372-2005-4757-8e72-b876a3393b7a|0|.0