Avatar of Fay A
Fay A asked on

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.
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Fay A

8/22/2022 - Mon
Jason Yousef

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

ASKER
Fay A

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 Yousef

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Fay A

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

WorkOrder#      ParentTicket#      AttachmentFileName      TASK      OpenDate      OPENBY      ATTACHCOUNT      LastModifiedBy
ASKER CERTIFIED SOLUTION
Jason Yousef

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Fay A

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

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jason Yousef

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

ASKER
Fay A

Thank you, it worked.