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

    Unanswered: Cannot get T-SQL syntax corrected.

    Hi, yes, I'm new to SQL, and my t-sql still flaky, so pls someone correct it for me..!
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name='MyDBA',
    @recipients='jean@mydba.co.za',
    @subject='Database size of all Databases',
    @query='EXEC sp_msforeachdb 'SELECT '?', * from [?]..sysfiles' ',
    @body='@query'
    go

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to escape your single quotes on your @query.

    I'd construct the command in a separate variable first to make things clearer:
    Code:
    DECLARE @sql varchar(500)
    
    SET @sql = 'EXEC sp_msforeachdb ''SELECT ''''?'''', * FROM [?]..sysfiles'' '
    
    PRINT @sql
    Code:
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name='MyDBA',
    @recipients='jean@mydba.co.za',
    @subject='Database size of all Databases',
    @query=@sql,
    @body='@query'
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2009
    Posts
    22
    You are a ROCKSTAR!
    Thanks, worked perfectly, and clears up many other issues I'm having too!
    Any chance you can help me to neaten the output as received in the mail?

Posting Permissions

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