t-sql results as an attachment by using db email

Fay A
Fay A used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jason YousefSr. BI  Developer

Commented:
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

Fay ADBA

Author

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.
Jason YousefSr. BI  Developer

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fay ADBA

Author

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

WorkOrder#      ParentTicket#      AttachmentFileName      TASK      OpenDate      OPENBY      ATTACHCOUNT      LastModifiedBy
Sr. BI  Developer
Commented:
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

Fay ADBA

Author

Commented:
I get the same error for your last t-sql but when I change the double quotes to single quotes the query works.
Fay ADBA

Author

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
Jason YousefSr. BI  Developer

Commented:
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

Fay ADBA

Author

Commented:
Thank you, it worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial