Creating a file from a SQL command, and then email the file to a client

Creating a file from a SQL command, and then email the file to a client

Post by Tom Hummel, International Superst » Tue, 29 Dec 1998 04:00:00



Yes, this can be done. Look into using xp_sendmail. This should be
able to do what you need.

        -Tom.

Once you have your


>I would like to have a SP that does a simple select on a table, and puts the
>results into a file, that I can then email to somebody(file would be an
>attachment), all run by the SQL task program each night.

>Can this been done ?


 
 
 

Creating a file from a SQL command, and then email the file to a client

Post by Heath Dillo » Wed, 30 Dec 1998 04:00:00


Hi.

I would like to have a SP that does a simple select on a table, and puts the
results into a file, that I can then email to somebody(file would be an
attachment), all run by the SQL task program each night.

Can this been done ?

Thanks

Heath



 
 
 

Creating a file from a SQL command, and then email the file to a client

Post by Steve Robinso » Wed, 30 Dec 1998 04:00:00


Tom,

We used to use xp_sendmail here but it seems that it is a serial process and
so if there was a problem the spid would be left orphaned and all future SQL
email would grind to a halt.  Because of the nature of the machine it meant
we had to reboot the box once at least once a day.
We now put all the output into an SQL table and periodically poll it with
VB.  The email is then sent via the MAPI Object.  Since this change we have
had no problems at all with SQL server.

Just someting to remember in case you hit the same problem.

Steve Robinson
SQLServer MVP




>Yes, this can be done. Look into using xp_sendmail. This should be
>able to do what you need.

> -Tom.

>Once you have your


>>I would like to have a SP that does a simple select on a table, and puts
the
>>results into a file, that I can then email to somebody(file would be an
>>attachment), all run by the SQL task program each night.

>>Can this been done ?



 
 
 

Creating a file from a SQL command, and then email the file to a client

Post by Vince Brad » Wed, 30 Dec 1998 04:00:00


Here's an simple example of sending an attachment based on a SQL statement:

/* Send E-Mail Notification of Files Processed */
USE master
GO

IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects
            WHERE type = 'U' and name = '##mailmsg')
    DROP TABLE ##mailmsg
GO

CREATE TABLE ##mailmsg
(
    file_date DATETIME,
    file_type CHAR(3) NULL,
    disk_name CHAR(40) NULL
)
GO

INSERT ##mailmsg
SELECT file_date, file_type, disk_name FROM db_admin..directory_dtl
GO

IF (SELECT COUNT(*) FROM ##mailmsg) > 0
  BEGIN




  EXEC master..xp_sendmail


  END

GO



>Hi.

>I would like to have a SP that does a simple select on a table, and puts
the
>results into a file, that I can then email to somebody(file would be an
>attachment), all run by the SQL task program each night.

>Can this been done ?

>Thanks

>Heath



 
 
 

1. Creating Fixed-Record Length Text File - PL/SQL Command File

I am creating a fixed-record length text file using a PL/SQL Developer
Command File as listed below (similar to SQL Plus script).  My output
file has an extra four spaces at the end of each record after the
numberic field (A.POSTED_TOTAL_AMT).  I've tried using the RTRIM
function and other methods, but I can't get rid of the four spaces.
Any ideas?

SET TERMOUT ON

ACCEPT FiscalYear NUMBER PROMPT 'Enter fiscal year:'
PROMPT Building Census expenditure data file...

SET TERMOUT OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET SPACE 0

SPOOL H:\Census\Mt02exp.txt

SELECT A.BUSINESS_UNIT || B.PROGRAM_VALUE_N || RPAD(A.ACCOUNT,6,' ')
||
       TO_CHAR(ROUND(SUM(A.POSTED_TOTAL_AMT),0),'S000000000009')
  FROM PS_LEDGER A, PS_SUBCLASS_XWLK_N B
 WHERE B.EFFDT =
       (SELECT MAX(EFFDT) FROM PS_SUBCLASS_XWLK_N
         WHERE B.SETID = SETID
           AND B.APPROPRIATION_NBR = APPROPRIATION_NBR
           AND EFFDT <= TO_DATE('30-JUN-' || &FiscalYear))
   AND B.SETID = 'STATE'
   AND A.APPROPRIATION_NBR = B.APPROPRIATION_NBR
   AND (A.LEDGER = 'ACTUALS'
   AND A.ACCOUNT LIKE '6%'
   AND A.FISCAL_YEAR = &FiscalYear
   AND A.ACCOUNTING_PERIOD <> 999)
 GROUP BY A.BUSINESS_UNIT, B.PROGRAM_VALUE_N, A.ACCOUNT
HAVING SUM(A.POSTED_TOTAL_AMT) <> 0;

SPOOL OFF

2. Corel Paradox 8

3. sql command to run sql commands in file

4. Create table Trigger?

5. Create Excel File and Email

6. Permanent FoxPro Programmer Position

7. Stored Procedure, Email and a log file (or an output file)

8. dbcc checktable(syslogs)

9. how to conver a email address text file to mdb file use code

10. attaching multiple files or container fields files to a email

11. Spool command from svrmgrl not creating file

12. Create table by batch file/command line?

13. Creating file maintenance program using dataflex commands