|
harlingtonthewizard
Constraint Violating Yak Guru
Australia
345 Posts |
 Posted - 05/23/2012 : 00:02:16
|
Here is one example. Note this excludes email.
USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Export_to_Excel] Script Date: 05/23/2012 13:29:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Export_to_Excel]
---Database_Table_Selection_for_Excel_Export_RP and Export_to_Excel need to be updated if additional tables added
@table_name nvarchar(max)
As
--Generate column names as a recordset
declare @columns nvarchar(max), @sql nvarchar(max), @data_file nvarchar(255), @file_name nvarchar(255), @db_name nvarchar(max)
--Set DB
SET @db_name = 'VC'
--Set Table Name
SET @table_name = (
CASE
WHEN @table_name = 'User Details' THEN 'UserDetails'
WHEN @table_name = 'Site Details' THEN 'SiteDetails'
WHEN @table_name = 'Site Authentication Details' THEN 'SiteAuthentication'
WHEN @table_name = 'Site Notification Details' THEN 'SiteNotificationDetails'
WHEN @table_name = 'Session Details' THEN 'SessionDetails'
WHEN @table_name = 'Event Details' THEN 'EventDetails'
WHEN @table_name = 'Event Comments' THEN 'EventComments'
WHEN @table_name = 'Operator Actions' THEN 'OperatorAction'
WHEN @table_name = 'Site Event Details' THEN 'SiteEventDetails'
WHEN @table_name = 'Resource Monitor' THEN 'SysMonitorRec'
WHEN @table_name = 'License Details' THEN 'LicenseDetails'
WHEN @table_name = 'Site Images' THEN 'SiteImages'
END )
--Set Storage Location
Set @file_name = (SELECT EntryValue + '\VCPRS\Exports\' FROM (SELECT EntryValue FROM VCSystemData WHERE EntryName = 'VCPDataRootPath') as t)
--Print @file_name
--Set Machine Name
Declare @MachineName nvarchar (255)
Set @MachineName = (SELECT Convert(nvarchar (100), MachineName) + '\XTRALIS_CYCLOPS'
From (SELECT SERVERPROPERTY ('MachineName') As MachineName) as t)
-- Add TableName, Date, Time and .xls to file name
Set @file_name = @file_name + @table_name + '_' + CONVERT(VARCHAR(19), Getdate(), 112) + '_' + REPLACE(CONVERT(VARCHAR(19), Getdate(), 108), CHAR(58), '') + '.xls'
--Print @file_name
Select @columns=coalesce(@columns+',','')+column_name+' as '+column_name
From information_schema.columns
Where table_name=@table_name
Select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
--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'
--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 -T -S "'+@MachineName+'"'''
--Print @sql
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 "'+@MachineName+'"'''
--Print @sql
Exec(@sql)
--Copy dummy file to passed EXCEL file
Set @sql= 'exec master..xp_cmdshell ''type "'+@data_file+'" >> "'+@file_name+'"'''
--Print @sql
Exec(@sql)
--Delete dummy file
Set @sql= 'exec master..xp_cmdshell ''del "'+@data_file+'"'''
--Print @sql
Exec(@sql)
RETURN
|
 |
|
Data import
