whats wrong with this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

whats wrong with this query

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-03-22 : 00:53:58

DECLARE @msg VARCHAR (1024)
DECLARE @eSubject VARCHAR (1024)
DECLARE @eQuery nvarchar(max)

set @eSubject = 'JUMP Daily Email Fail Check ' +rtrim(convert(char,day(getdate())))+'/'+rtrim(convert(char,month(getdate())))+'/'+rtrim(convert(char,year(getdate())))+'.'
set @eQuery = N' select mailitem_id,recipients,copy_recipients,subject,send_request_user, sent_status,sent_date
from msdb.dbo.sysmail_allitems where send_request_user=''medinfowebuser''
and sent_status=''failed''
and DATEADD(dd, DATEDIFF(dd, 0,last_mod_date), 0) = DATEADD(dd, DATEDIFF(dd, 0,''2010-11-15 15:39:39.230''), 0) '

EXEC msdb..sp_send_dbmail
@recipients=N'A@a.com', --@email

@body=@msg,
@subject =@eSubject,
--@Body_Format = 'HTML',
@profile_name ='NCS SMTP Server',
@query = @eQuery

I am getting the below error

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Sqlcmd: Error: Internal error at GetDataRowset (Reason: Not enough storage is available to complete this operation).
Sqlcmd: Error: Internal error at FormatRowset (Reason: Not enough storage is available to complete this operation).

Please help.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-03-22 : 03:55:41

I copied the exact code you posted, ran it on my server, and I got the email just fine.

The error message is indicating a problem with storage...what is the disk situation on your server? Full disks can cause all sorts of errors.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-03-22 : 04:29:44

Thanks ,i was usign SQL server 2005 SP2

I have used convert function to change all the select field columns to varchar and it worked out.

Thanks,
Gangadhara MS
SQL Developer and DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources