Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    22

    Red face Unanswered: sp_send dbmail not allowing query result to be sent

    I've tried every avenue on every damn forum suggested, but to no avail!
    Need to send results of SQLPERF(logspace), that have been stored in a table, via sp_send_dbmail to recipient.

    Step 2 of job is where failure occurs. Please help!

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name= 'MyDBA',
    @recipients= 'Mack@mydba.co.za',
    @subject='Log Warning',
    @query='SELECT * from #TempForLogSpace WHERE LogSpaceUsed >80




  2. #2
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    What is the error? I would suspect Step 2 does not have access to the local temp table. If Step 1 creates the temp table, try it create a global one - but I doubt that will work. Global temp tables, like local temp tables, are automatically dropped when the session that creates it ends and no other sessions are referencing the table.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...in which case append step 2 to the contents of step1. This will resolve the issue.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by brucevde
    What is the error? I would suspect Step 2 does not have access to the local temp table. If Step 1 creates the temp table, try it create a global one - but I doubt that will work. Global temp tables, like local temp tables, are automatically dropped when the session that creates it ends and no other sessions are referencing the table.
    Just create a table you truncate and load if a #temp table does not work.

  5. #5
    Join Date
    Aug 2009
    Posts
    22
    Partial(!) Thanks guys

    The drop of table rather than the temp table and combining steps one and two (create table, select into and send mail) worked, BUT...
    Still not getting results of the @query sent ie the created and populated table...?

  6. #6
    Join Date
    Aug 2009
    Posts
    22

    Smile Check Logspace and have it sent in table form to e-mail

    Got it working!

    Full Script to get info logspace from all your databases,
    get it into table, and the table mailed to you. So no need to physically go to DB and check.

    DROP TABLE TempForLogSpace
    go
    CREATE TABLE TempForLogSpace
    (DBName Varchar(32),
    LogSize real,
    LogSpaceUsed real,
    Status int)
    INSERT INTO TempForLogSpace EXEC ('dbcc sqlperf(logspace)')
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name='your_profile',
    @recipients='Gandalf@LodOfTheRings.com',
    @subject='Log Warning',
    @query='SELECT * from TempForLogSpace WHERE LogSpaceUsed >80',
    @body='@query'
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •