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
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_fi
@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.
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
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_fi
@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.
ASKER
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.
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.
strange!! are you sure this table exists (OE_Alerts) ?
can you specify the column names!! just to test
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.LastModifiedByASKER
yes it does. here are all the fields from that table. "OE_Alerts"
WorkOrder# ParentTicket# AttachmentFileName TASK OpenDate OPENBY ATTACHCOUNT LastModifiedBy
WorkOrder# ParentTicket# AttachmentFileName TASK OpenDate OPENBY ATTACHCOUNT LastModifiedBy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I get the same error for your last t-sql but when I change the double quotes to single quotes the query works.
ASKER
yes, after I change double to single quotes
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
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
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;ASKER
Thank you, it worked.
Open in new window