Hi there,

here is the code - anonymised (sp?) of course.

As said in a previous post, this was written a long time ago when I had just learned how to write selects and very basic programming, if there are syntax errors I would guess it has to do with the @cmdsql statement building in step 3 of the programming.

The code can be used/modified and passed on (if it's good enough).

The only thing missing here is the loop to write to multiple sheets, but I think most of you could get that done. I had a similar bit of logic to get round the 64000 row problem in excel, but cheated by doing it in two steps and using TOP 64000 in the first select statement.

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

--

-- Variable declaration

--

declare@cmdshellnvarchar(4000)

declare @cmdsqlnvarchar(4000)

declare@basedirvarchar(200)

declare@vorlagevarchar(200)

declare@locationvarchar(200)

declare@dummyvarchar(50)

declare @extensionvarchar(4)

declare@commandvarchar(20)

declare@filenamevarchar(200)

declare@yearvarchar(4)

declare@monthvarchar(2)

declare@dayvarchar(2)

declare@hourvarchar(2)

declare@minutevarchar(2)

declare@secondvarchar(2)

declare @attachment1varchar(200)

declare @attachment2varchar(200)

declare @attachment3varchar(200)

declare @attachment4varchar(200)

declare @attachment5varchar(200)

declare @attachment6varchar(200)

declare @attachment7varchar(200)

declare @attachment8varchar(200)

declare @attachment9varchar(200)

declare@attachment10varchar(200)

declare @attachallvarchar(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

execmaster..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

execmaster..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

execmaster..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.

That should hopefully point you in the right direction. Unfortunately I have no idea how you would dynamically name the sheets for the excel file. I only use an empty excel file with the default names. I know you can do it using the object creation sys.sps that are in sql server (BE CAREFUL they are resource killers AFAIK).

Well, enough babbling. Have fun and please be kind when slating the code quality: I was but a young pup! 😛