We help IT Professionals succeed at work.

t-sql results as an attachment by using db email

75 Views
Last Modified: 2018-10-10
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

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

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:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.