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.
Last Comment
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.LastModifiedBy
ASKER
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window