Post reply

sp_send_dbmail

  • January 31, 2007 at 10:49 am

    #171952

    Hello,

    I am working on sp_send_dbmail, when i am executing a query and attaching the same. Below is query

    EXEC

    msdb.dbo.sp_send_dbmail

    @profile_name = 'ABC',

    @recipients = 'nag@sat.com',

    @query = 'SELECT * FROM isos_prod..tbl_sourceorders',

    @subject

    = 'Error Log ',

    @attach_query_result_as_file

    = 1 ;

     

    ERROR:

    Msg 22050

    , Level 16, State 1, Line 0

    File

    attachment or query results size exceeds allowable value of 1000000 bytes.

    The error is occuring because this is sending as  txt file where spaces are occupied much in output fiile and i want in the Grid format(xls) instead of text(txt).

    Waiting for Earliest Reply.

     

     

  • January 31, 2007 at 11:57 am

    #686924

    Hello Reddy,

    You can change the default parameters to the desired number of bytes. Click on Database Mail -> Select Configure DB Mail -> Next -> Select the radio button "View or Change System Parameters".

    On the next screen, you will see an option for Max. file size, which you can change by clicking or overwriting.

    Hope this helps your requirement. Reply back, how it has helped you.

    Thanks

     


    Lucky

  • February 1, 2007 at 3:39 am

    #687056

    Lucky,

    Many Thanks for your reply, The output is still having spaces which i dont require, ex: Name is having varchar(5000) in the output file it is showing 5000 'Luck is a Good Techy guy                 '..till 5000 ends  it will show entire 5000 chars. Basically i need the output as GRID format as a attachment.

    Many Thanks,

     

  • February 1, 2007 at 6:15 am

    #687079

    I don't think you can export the grid from Server Management Studio.  I've dealt with similar problems and ended up DTS'ing the package to an Excel format and then attached the Excel file in the email.

  • February 20, 2007 at 11:35 pm

    #690819

    Another option is to limit the size of the output column. Instead of SELECT name ..., try SELECT CAST(name as varchar(100)) ...

  • October 10, 2007 at 12:33 am

    #740649

    Had the same error message and problem.

    Added the following to fix:

    @query_result_no_padding =1

    Hope this helps.

  • August 25, 2008 at 9:08 am

    #862576

    it works for me

    Thanks a lot,

    Sarah

  • September 12, 2008 at 2:13 pm

    #871325

    this requires a hotfix found at http://support.microsoft.com/kb/920794

  • September 30, 2008 at 3:09 pm

    #879193

    @query_result_no_padding =1

    This worked for me. Big Thanks.

  • November 10, 2008 at 9:58 am

    #896393

    here is my sp....i dont get the resultset in columns (not well formatted). can anyone help with this....thanks a lot!!

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'WYZ',

    @recipients = 'name@abc.com',

    @body = 'This is test mail. See attached file',

    @query = @runquery,

    @query_attachment_filename = 'abc.xls',

    @query_result_header = 1,

    @subject = 'Test Report',

    @attach_query_result_as_file = 1,

    @query_result_no_padding = 1

  • June 17, 2009 at 9:38 am

    #1011077

    @query_result_separator

    did you try this parameter?

  • December 17, 2009 at 6:02 am

    #1093448

    add

    @query_result_separator = CHAR(9),

    and it works

  • March 30, 2010 at 5:42 am

    #1142079

    Why not just force formatting as you need it to be

    SELECT CAST([Column1] AS VARCHAR(50)) + ',' + CAST([Column2] AS VARCHAR(50)) + ',' + CAST([Column3] AS VARCHAR(50))

    FROM [Database].[dbo].[Table]

    Will return Text1,Text2,Text3

  • September 27, 2010 at 3:42 am

    #1227588

    Thanks

    Perfect solution are given .It working .So It very help for us your solution .

    Regards

    Saif

    Qazi Saif Hussain
    Mphasis, an hp company
    Pune India,
    saif.qazi0532@gmail.com:-)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply