SQL Output

hi,
may be this will help you to save of the table contents to a txt file only.

declare @as_bcp varchar(255) --bcp cmd
declare @as_select varchar(255) --select stmt
declare @as_appcode varchar(20) --filename

--sample query
set @as_select = 'bcp "select problem_category, problem_description, prodn_supervisor, problem_entrydate from production.dbo.problem_master order by problem_category, problem_description"'

begin

select @as_appcode = 'abc' --file name

set @as_bcp = @as_select + ' queryout ' + @as_appcode + '.txt -S -U -P -w'

select @as_bcp

exec master..xp_cmdshell @as_bcp

end

after saving the file you can use the xp_sendmail command for sending the mail to the group of users
exec master.dbo.xp_sendmail @recipients='recipient1;recipient2;..', @attachments=,@subject=

hope this helps you to work with and solve the problem.

by using stored procedures and dts with a job here is how it goes...

in the SP -

CREATE PROCEDURE [StoreProcedureName] AS
Declare @mailRecipients varchar(600) -- this will allow you to input names into the mail recpt. line
Declare @blindccrecipients varchar (102) -- if you need to blind cc the results
Declare @strMessage varchar(103) -- you can put in your own header in to this to define the results
Declare @strSQL2 varchar(80) -- i use a SP here as well to run the query
Declare @table_qualifier varchar(80) -- The Subject of the message

set @mailrecipients = 'xyz@abc.com;cde@abc.com' -- use ; to seperate email addys
set @blindccrecipients = ' ' -- same here
set @strmessage = ' '-- again input here for you own header row or what ever else you want
set @strSql2= 'sp_StoredProcedureName-- you can put in a query here or use an alternate sp
set @table_qualifier = 'SubjectLine of E-Mail'

EXEC master..xp_sendmail @recipients = @mailrecipients
,@dbuse = AirClic
,@blind_copy_recipients = @blindccrecipients
,@message = @strMessage
,@query = @strSQL2
,@attach_results = False -- here you can attach results in a .txt file or if false it appeares in the body of the e-mail
,@subject = @table_qualifier
,@no_header = False -- use actual query header
,@width = 100
GO

The sp i use for the query is just a std. select query.

Then i schedule a DTS Package to run this procudure using the execute sql task i call this procedure.. then i schedule it in a job

A simple VB ado / smtp app may be your solution. Set the properties to run the form minimized and scheduled on a server or workstation of your choice.

1. Create a new SQL Server Job
2. Create a new Step of type T-SQL in the job
3. Plug your select statement in here
4. In the advanced tab enter an output file

This is the file creation part of your task.

For the email part of your task you create another job step of type T-SQL, then do one of two things:

1. use xp_Sendmail which only works with Outlook and is a real &*^$# to set up.

2. use xp_smtp_sendmail, which we have a had a lot of success with (http://sqldev.net/xp/xpsmtp.htm)

Thats the email part of your task.

Just remember to use the file that you created in the first step as an attachment in your sendmail xp.

No VB Apps, No huge SP's, simple!!

Hi guys

many thanks for all your submissions, however only the last one by Nick worked as we dont have a mapi installed on our server
Thanks Nick that worked brilliantly

Tim