SQL Server Performance

Sending Excel file through sql mail

Discussion in 'General Developer Questions' started by gk_sql, Jul 6, 2007.

  1. gk_sql New Member

    is there any way to send the Excel file from the database server thorough mail.
    Mail server is configured for sql server and is working fine with text files.
    It will be a great help share any ideas for how to do send an excel file.

    thanks
    gopi
  2. DilliGrg Member

    Found this article online but can't remember who the original author was. Try using this logic which might help on your case.

    /*
    In order to achieve the export, you must first create an empty Excel file with a fixed name and place on the server.
    I called it Empty.xls and placed it in the c: emp directory. This file is not to be deleted and acts like a template
    for the target Excel file before it is populated by data.

    The Empty.xls file is built with a single spread sheet called ExcelTable and the first (and only)
    line in the file consists of the letters A,B,C,...Z. These letters act like the column names of the Excel table.
    That means that up to 26 columns can be exported in a single query.
    (The given stored procedure code can be altered to support more columns in the result set.
    Simply write F1, F2 ,F3... in the Excel template and change the column list in the procedure to reflect the change.)

    The T-SQL stored procedure, usp_WriteToExcel, gets the target Excel file name and path, the number of columns in the result set
    and the T-SQL query. The query should use the convert function for every non-string exported column
    since the resulting Excel cells are actually strings.

    The procedure copies the empty.xls template file to the new target Excel file.
    Then it builds a linked server to that file and uses dynamic T-SQL to construct
    and Insert/Select statements that write the data to the Excel file.

    */

    Create procedure usp_WriteToExcel (@fileName varchar(100),
    @NumOfColumns tinyint,
    @query varchar(200))
    as
    begin
    declare @dosStmt varchar(200)
    declare @tsqlStmt varchar(500)
    declare @colList varchar(200)
    declare @charInd tinyint

    set nocount on

    -- construct the columnList A,B,C ...
    -- until Num Of columns is reached.

    set @charInd=0
    set @colList = 'A'
    while @charInd < @NumOfColumns - 1
    begin
    set @charInd = @charInd + 1
    set @colList = @colList + ',' + char(65 + @charInd)
    end

    -- Create an Empty Excel file as the target file name by copying the template Empty excel File
    set @dosStmt = ' copy c:empempty.xls ' + @fileName
    exec master..xp_cmdshell @dosStmt

    -- Create a "temporary" linked server to that file in order to "Export" Data
    EXEC sp_addlinkedserver 'ExcelSource',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    @fileName,
    NULL,
    'Excel 5.0'

    -- construct a T-SQL statement that will actually export the query results
    -- to the Table in the target linked server
    set @tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' + ' ( ' + @colList + ' ) '+ @query

    print @tsqlStmt

    -- execute dynamically the TSQL statement
    exec (@tsqlStmt)

    -- drop the linked server
    EXEC sp_dropserver 'ExcelSource'
    set nocount off
    end
    GO

    -- Use master
    -- go
    -- exec usp_WriteToExcel
    -- -- Target Excel file
    -- 'c:empNorthProducts.xls' ,
    --
    -- -- Number of columns in result
    -- 3,
    --
    -- -- The query to be exported
    -- 'select convert(varchar(10),ProductId),
    -- ProductName,
    -- Convert (varchar(20),UnitPrice) from Northwind..Products'






    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  3. gk_sql New Member

    Thanks Dill,

    Is there any way we can send the excel file through sql mail.

    thanks
    gk

  4. Will192 New Member

    This is copied directly from one of my DTS jobs. (with names changed to protect the innocent)

    Dim objMail
    Set objMail = CreateObject("CDO.Message")
    objMail.From = "from@emailadd"
    objMail.To = "to@emailadd"
    objMail.AddAttachment ( "c:BOOK1.XLS")
    objMail.Subject="Email message"
    objMail.TextBody = "This was sent from a sql server"
    objMail.Send
    Set objMail = nothing


    It's not technically SQL Mail, but it works.

    Live to Throw
    Throw to Live
  5. MohammedU New Member

    SQL mail works for Excel files without any issues...
    Have you tried to send Excel file and what was the error you got...
    I regularly use them without any issues...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page