copy d:\templates\sales.xls d:\export\sales.xls

 insert into OPENROWSET(
	'Microsoft.Jet.OLEDB.4.0', 
	'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES', 
	'SELECT * FROM [Sheet1$]')
select * from sales_part1

 insert into OPENROWSET(
	'Microsoft.Jet.OLEDB.4.0', 
	'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES', 
	'SELECT * FROM [Sheet2$]')
select * from sales_part2

create  proc [dbo].[proc_Language_Export] (@language varchar(2),@email varchar(100),@emailsubject varchar(50)) as
--
-- Variable declaration
--

declare	@cmdshell		nvarchar(4000)
declare @cmdsql			nvarchar(4000)
declare	@basedir		varchar(200)
declare	@vorlage		varchar(200)
declare	@location		varchar(200)
declare	@dummy			varchar(50)
declare @extension		varchar(4)
declare	@command		varchar(20)
declare	@filename		varchar(200)
declare	@year			varchar(4)
declare	@month			varchar(2)
declare	@day			varchar(2)
declare	@hour			varchar(2)
declare	@minute			varchar(2)
declare	@second			varchar(2)
declare @attachment1	varchar(200)
declare @attachment2	varchar(200)
declare @attachment3	varchar(200)
declare @attachment4	varchar(200)
declare @attachment5	varchar(200)
declare @attachment6	varchar(200)
declare @attachment7	varchar(200)
declare @attachment8	varchar(200)
declare @attachment9	varchar(200)
declare	@attachment10	varchar(200)
declare @attachall		varchar(4000)


select	@year		=	datepart(year,getdate())
select	@month		=	right('0'+cast(datepart(month,getdate()) as varchar (2)),2)
select	@day		=	right('0'+cast(datepart(day,getdate()) as varchar (2)),2)
select	@hour		=	right('0'+cast(datepart(hour,getdate()) as varchar (2)),2)
select	@minute		=	right('0'+cast(datepart(minute,getdate()) as varchar (2)),2)
select	@second		=	right('0'+cast(datepart(second,getdate()) as varchar (2)),2)
set		@basedir	=	'\\server1\c$\import_export\Translation\Export\'
set		@vorlage	=	'\\server1\c$\import_export\Translation\Template\'

--
-- Step 1: Exportdirectory creation (if not already there)
--

set		@command	=	'if not exist '
set		@location	=	@basedir+@language
set		@cmdshell	=	@command+@location+' '
set		@command	=	'md '
set		@cmdshell	=	@cmdshell+@command+@location
exec	master..xp_cmdshell @cmdshell,no_output;
set		@command	=	'if not exist '
set		@cmdshell	=	@command+@location+'\'+@year+@month+@day+' '
set		@command	=	'md '
set		@cmdshell	=	@cmdshell+@command+@location+'\'+@year+@month+@day
set		@location	=	@location+'\'+@year+@month+@day
exec	master..xp_cmdshell @cmdshell,no_output;

--
-- Step 2: Create exportfiles with timestamp in name, fill the files once there
--

set		@dummy		=	'Exportfile1'
set		@extension	=	'.xls'
set		@command	=	'copy '
set		@filename	=	'Exportfile1'+'_'+@hour+@minute+@second+@extension
set		@cmdshell	=	@command+@vorlage+@dummy+@extension+' '+@location+'\'+@filename
exec	master..xp_cmdshell @cmdshell,no_output;
set		@attachment1	=	@location+'\'+@filename
set		@cmdsql='
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
insert into OPENROWSET(
	''Microsoft.Jet.OLEDB.4.0'', 
	''Excel 8.0;Database='+@location+'\'+@filename+';;HDR=YES'', 
	''SELECT * FROM [Sheet1$]'')
select

stuff

from
	languageexporttable

where

	language = '''''+@language+''''''
exec (@cmdsql)

--Step 3 - ? Do the other exports

--Step ?+1 send email with results using email address supplied at proc call.

--Step 1
exec xp_cmdshell 'copy d:\templates\sales.xls d:\export\sales.xls'

--Step 2
insert into OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0', 
        'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES', 
        'SELECT * FROM [Sheet1$]')
select * from sales_part1

--Step 3
insert into OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0', 
        'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES', 
        'SELECT * FROM [Sheet2$]')
select * from sales_part2