Or this:

set nocount on
drop table ##myTable
go
create table ##myTable
(col1 int identity,
col2 char(1))
go

--place what you want in a global temmporary table
insert ##myTable (col2) values ('a')
go 5

declare
@Path varchar(2000),
@bcpCommand varchar(2000),
@sSQL varchar (2000),
@sfilename varchar (20),
@ofilename varchar (30),
@TabName varchar(10)

set @Path = 'C:\temp\'
set @sfilename = 'myFile'
set @TabName = '##myTable'

-- build a file name with a datetime stamp
set @ofilename = @sfilename + '_' +
replace(replace(replace(convert(varchar,GETDATE(),20),'-',''),':',''),'
','_') + '.xls'

-- export the data
set @bcpCommand = 'bcp ' + @TabName + ' out "' + @path + @ofilename + '"
-c -T -U sa -P Ladybug2'
exec master..xp_cmdshell @bcpCommand--, no_output





-----Original Message-----
From: Anderson Steve (ICardio)
[mailto:mssqldba-ezmlmshield-x87702471.[Email address protected]
Sent: January 28, 2008 3:52 PM
To: LazyDBA Discussion
Subject: RE: Automate results to excel


Try something like this. You will have to define the values for the
variables.

DECLARE @chvSub AS varchar(255)
DECLARE @strSQL AS varchar(4000)
DECLARE @separator AS char(1)
DECLARE @Recipient AS varchar(100)
DECLARE @Recipients AS varchar(500)
DECLARE @DefaultRecipients AS varchar(500)
DECLARE @RecordCount as int

SET NOCOUNT ON

SET @separator = char(9)

EXEC msdb.dbo.sp_send_dbmail @recipients=@Recipients, --
'[Email address protected]
@subject = @chvSub,
@body = 'Report for the all customers.',
@query = @strSQL,
@attach_query_result_as_file = 1, -- Send the query as an
attachment.
@query_attachment_filename = 'Report.xls',
@query_result_header = 1, -- Include column headers.
@query_result_width = 2000,
@query_result_separator = @separator


Stephen Anderson
(763) 494-2441
(763) 494-2541 Fax
www.BostonScientific.com
[Email address protected]

-----Original Message-----
From: Albert Frazer
[mailto:mssqldba-ezmlmshield-x654541.[Email address protected]
Sent: Monday, January 28, 2008 6:27 AM
To: LazyDBA Discussion
Subject: RE: Automate results to excel

Or you can use DTS for it.


Albert Frazer
Home Decor Products, Inc
www.hdpi.com
732-593-3637 (office)
732-570-4465 (cell)


-----Original Message-----
From: andrew [mailto:mssqldba-ezmlmshield-x32435801.[Email address
protected]
Sent: Monday, January 28, 2008 5:23 AM
To: LazyDBA Discussion
Subject: RE: Automate results to excel

Install and run the queries on Reporting services. You can schedule them
to export to an excel spreadsheet or email to other users etc. handy
tool!



Regards

Andrew Moore
Production DBA, Nokia Music Service


>-----Original Message-----
>From: ext Crafford R. (Ruan)
>[mailto:mssqldba-ezmlmshield-x73148991.[Email address protected]
>Sent: 28 January 2008 10:05
>To: LazyDBA Discussion
>Subject: Automate results to excel
>
>Hi all
>
>I have 5 queries that have to run daily, these queries just does Basic
>SELECT statements and joins on tables. I want to know how i can
>automate this, the results of each query needs to be put into a excel
>file and then needs to be copied to a ftp directory, Im using SQL2000.
>
>Thank you
>Ruan
>
>********************
>Nedbank Limited Reg No 1951/000009/06. The following link displays the
>names of the Nedbank Board of Directors and Company Secretary. [
>http://www.nedbank.co.za/terms/DirectorsNedbank.htm ] This email is
>confidential and is intended for the addressee only.
>The following link will take you to Nedbank's legal notice. [
>http://www.nedbank.co.za/terms/EmailDisclaimer.htm ]
>********************
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post
>a dba job: http://jobs.lazydba.com To subscribe :
>http://www.LazyDBA.com To unsubscribe:
>http://www.lazydba.com/unsubscribe.html
>
>


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page