t-sql results as an attachment by using db email

I am getting the following error while running a t-sql results as an email attachment by using  msdb.dbo.sp_send_dbmail.  I can run the query fine in a query window.

Msg 1038, Level 15, State 4, Line 6
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.


Here is my query

Truncate Table OE_Alerts
DECLARE @query NVARCHAR(MAX), @Count INT
SET @query = N'select  t.WO_NUM as WorkOrder#, t.PARENTWOID as ParentTicket#, max(a.LogicalName) as AttachmentFileName, t.TASK, max(t.OPENDATE) as OpenDate, t.OPENBY,  t.ATTACHCOUNT, a.LastModifiedBy
from TASKS t, Attachment a
where t.PARENTWOID = a.AttachmentOwnerId
and a.AttachmentOwnerId = t.PARENTWOID
and t.WO_NUM = t.PARENTWOID
and t.LOOKUP1 <> ""
and t.LOOKUP2 = "OE - Level 3" or LOOKUP2 = "OE - Level 4" or LOOKUP2 = "OE - Level 5"
and t.OPENDATE >= DATEADD(MINUTE, -30, GETUTCDATE())
and t.ATTACHCOUNT <> "0"
group by t.woid,  t.WO_NUM,t.TASK,t.OPENBY,  t.PARENTWOID,t.ATTACHCOUNT,a.LastModifiedBy'
INSERT INTO OE_Alerts
EXECUTE SP_EXECUTESQL @query
SET @Count = @@ROWCOUNT
IF @Count > 0
EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'DatabaseAlerts',
@recipients = 'Alerts@abc.org',
@subject = 'There is a new attachment to the parent ticket',
@execute_query_database = 'TRACKIT_DATA',
@body_format = 'HTML',
      @query = 'SELECT * FROM OE_Alerts',
      @body = 'Attachment.',
@attach_query_result_as_file = 1,
@exclude_query_output = 1,
@query_result_header = 1,
@query_result_width = 32767,
@query_no_truncate = 0;

Any idea whats wrong here, please let me know.
Fay ADBAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason YousefSr. BI  DeveloperCommented:
what happens when you run

Truncate Table OE_Alerts

declare @Count int

INSERT INTO OE_Alerts
select  t.WO_NUM as WorkOrder#, 
t.PARENTWOID as ParentTicket#, max(a.LogicalName) as AttachmentFileName, 
t.TASK, max(t.OPENDATE) as OpenDate, t.OPENBY,  t.ATTACHCOUNT, a.LastModifiedBy 
from TASKS t, Attachment a
where t.PARENTWOID = a.AttachmentOwnerId
and a.AttachmentOwnerId = t.PARENTWOID
and t.WO_NUM = t.PARENTWOID 
and t.LOOKUP1 <> ""
and t.LOOKUP2 = "OE - Level 3" or LOOKUP2 = "OE - Level 4" or LOOKUP2 = "OE - Level 5"
and t.OPENDATE >= DATEADD(MINUTE, -30, GETUTCDATE()) 
and t.ATTACHCOUNT <> "0"
group by t.woid,  t.WO_NUM,t.TASK,t.OPENBY,  t.PARENTWOID,t.ATTACHCOUNT,a.LastModifiedBy

select @Count = @@ROWCOUNT


IF @Count > 0
EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'DatabaseAlerts',
@recipients = 'Alerts@abc.org',
@subject = 'There is a new attachment to the parent ticket', 
@execute_query_database = 'TRACKIT_DATA',
@body_format = 'HTML',
      @query = 'SELECT * FROM OE_Alerts',
      @body = 'Attachment.',
@attach_query_result_as_file = 1,
@exclude_query_output = 1,
@query_result_header = 1,
@query_result_width = 32767,
@query_no_truncate = 0;

Open in new window

0
Fay ADBAAuthor Commented:
same error

Msg 1038, Level 15, State 4, Line 14
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
0
Jason YousefSr. BI  DeveloperCommented:
strange!!  are you sure this table exists (OE_Alerts) ?
can you specify the column names!! just to test

INSERT INTO OE_Alerts (WorkOrder#,ParentTicket#,...........)
select  t.WO_NUM as WorkOrder#, 
t.PARENTWOID as ParentTicket#, max(a.LogicalName) as AttachmentFileName, 
t.TASK, max(t.OPENDATE) as OpenDate, t.OPENBY,  t.ATTACHCOUNT, a.LastModifiedBy 
from TASKS t, Attachment a
where t.PARENTWOID = a.AttachmentOwnerId
and a.AttachmentOwnerId = t.PARENTWOID
and t.WO_NUM = t.PARENTWOID 
and t.LOOKUP1 <> ""
and t.LOOKUP2 = "OE - Level 3" or LOOKUP2 = "OE - Level 4" or LOOKUP2 = "OE - Level 5"
and t.OPENDATE >= DATEADD(MINUTE, -30, GETUTCDATE()) 
and t.ATTACHCOUNT <> "0"
group by t.woid,  t.WO_NUM,t.TASK,t.OPENBY,  t.PARENTWOID,t.ATTACHCOUNT,a.LastModifiedBy

Open in new window

0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Fay ADBAAuthor Commented:
yes it does. here are all the fields from that table. "OE_Alerts"

WorkOrder#      ParentTicket#      AttachmentFileName      TASK      OpenDate      OPENBY      ATTACHCOUNT      LastModifiedBy
0
Jason YousefSr. BI  DeveloperCommented:
would that run?  that's the query you need to troubleshoot
 
INSERT INTO OE_Alerts (WorkOrder#,ParentTicket#,AttachmentFileName ,TASK , OpenDate ,OPENBY,ATTACHCOUNT,LastModifiedBy)
select  t.WO_NUM as WorkOrder#, 
t.PARENTWOID as ParentTicket#, max(a.LogicalName) as AttachmentFileName, 
t.TASK, max(t.OPENDATE) as OpenDate, t.OPENBY,  t.ATTACHCOUNT, a.LastModifiedBy 
from TASKS t, Attachment a
where t.PARENTWOID = a.AttachmentOwnerId
and a.AttachmentOwnerId = t.PARENTWOID
and t.WO_NUM = t.PARENTWOID 
and t.LOOKUP1 <> ""
and t.LOOKUP2 = "OE - Level 3" or LOOKUP2 = "OE - Level 4" or LOOKUP2 = "OE - Level 5"
and t.OPENDATE >= DATEADD(MINUTE, -30, GETUTCDATE()) 
and t.ATTACHCOUNT <> "0"
group by t.woid,  t.WO_NUM,t.TASK,t.OPENBY,  t.PARENTWOID,t.ATTACHCOUNT,a.LastModifiedBy

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fay ADBAAuthor Commented:
I get the same error for your last t-sql but when I change the double quotes to single quotes the query works.
0
Fay ADBAAuthor Commented:
yes, after I change double to single quotes

INSERT INTO OE_Alerts (WorkOrder#,ParentTicket#,AttachmentFileName ,TASK , OpenDate ,OPENBY,ATTACHCOUNT,LastModifiedBy)
select  t.WO_NUM as WorkOrder#,
t.PARENTWOID as ParentTicket#, max(a.LogicalName) as AttachmentFileName,
t.TASK, max(t.OPENDATE) as OpenDate, t.OPENBY,  t.ATTACHCOUNT, a.LastModifiedBy
from TASKS t, Attachment a
where t.PARENTWOID = a.AttachmentOwnerId
and a.AttachmentOwnerId = t.PARENTWOID
and t.WO_NUM = t.PARENTWOID
and t.LOOKUP1 <> ''
and t.LOOKUP2 = 'OE - Level 3' or LOOKUP2 = 'OE - Level 4' or LOOKUP2 = 'OE - Level 5'
and t.OPENDATE >= DATEADD(MINUTE, -30, GETUTCDATE())
and t.ATTACHCOUNT <> '0'
group by t.woid,  t.WO_NUM,t.TASK,t.OPENBY,  t.PARENTWOID,t.ATTACHCOUNT,a.LastModifiedBy
0
Jason YousefSr. BI  DeveloperCommented:
oh i didn't see the double quotes, I've copied and pasted your original query, so try to add all of it together

Truncate Table OE_Alerts

declare @Count int


INSERT INTO OE_Alerts (WorkOrder#,ParentTicket#,AttachmentFileName ,TASK , OpenDate ,OPENBY,ATTACHCOUNT,LastModifiedBy)
select  t.WO_NUM as WorkOrder#, 
t.PARENTWOID as ParentTicket#, max(a.LogicalName) as AttachmentFileName, 
t.TASK, max(t.OPENDATE) as OpenDate, t.OPENBY,  t.ATTACHCOUNT, a.LastModifiedBy 
from TASKS t, Attachment a
where t.PARENTWOID = a.AttachmentOwnerId
and a.AttachmentOwnerId = t.PARENTWOID
and t.WO_NUM = t.PARENTWOID 
and t.LOOKUP1 <> ''
and t.LOOKUP2 = 'OE - Level 3' or LOOKUP2 = 'OE - Level 4' or LOOKUP2 = 'OE - Level 5'
and t.OPENDATE >= DATEADD(MINUTE, -30, GETUTCDATE()) 
and t.ATTACHCOUNT <> '0'
group by t.woid,  t.WO_NUM,t.TASK,t.OPENBY,  t.PARENTWOID,t.ATTACHCOUNT,a.LastModifiedBy


select @Count = @@ROWCOUNT


IF @Count > 0
EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'DatabaseAlerts',
@recipients = 'Alerts@abc.org',
@subject = 'There is a new attachment to the parent ticket', 
@execute_query_database = 'TRACKIT_DATA',
@body_format = 'HTML',
      @query = 'SELECT * FROM OE_Alerts',
      @body = 'Attachment.',
@attach_query_result_as_file = 1,
@exclude_query_output = 1,
@query_result_header = 1,
@query_result_width = 32767,
@query_no_truncate = 0;

Open in new window

0
Fay ADBAAuthor Commented:
Thank you, it worked.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.