We help IT Professionals succeed at work.

Invalid column name 'filename' in trigger

Phosphor
Phosphor used Ask the Experts™
on
Hello Experts,
An on-inserted trigger:
I can't get past this error ("Invalid column name 'filename'.") when I run the alter trigger, but not if I parse it. One column's name in the inserted selects is "filename".

Just using [brackets], column alias: (SET @fil = (SELECT filename as f FROM inserted) or mutipart identifier: "fm_faxin_att.filename" still will not work.
Any ideas?

Thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		TCG
-- Create date: 10/17/09
-- Mod date:	08/03/11
-- Description:	testMail Trigger
-- =============================================
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
DECLARE @date datetime
DECLARE @routed_to nvarchar(255)
DECLARE @remote_id nvarchar(255)
DECLARE @result nvarchar(255)
DECLARE @fil nvarchar(255)
DECLARE @pages_transferred int
 
SET @date = (SELECT date FROM inserted)
SET @routed_to = (SELECT routed_to FROM inserted)
SET @fil = (SELECT filename FROM inserted)
SET @remote_id = (SELECT remote_id FROM inserted)
SET @result = (SELECT result FROM inserted) 
SET @pages_transferred = (SELECT pages_transferred FROM inserted)
--SET @id = (SELECT id FROM inserted)
WAITFOR DELAY '00:00:02'
IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
  BEGIN
SELECT     fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
FROM         fm_faxin INNER JOIN
                      fm_faxin_att ON fm_faxin.id = fm_faxin_att.id RIGHT OUTER JOIN
                      inserted ON inserted.id = fm_faxin_att.id
	DECLARE @sub varchar(255)
	DECLARE @msg varchar(MAX)
	SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + @fil + ' ---- '
    SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 
'You can view the fax by following this link: https://www.example.com/ibfax/' + @fil

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ron MalmsteadInformation Services Manager

Commented:
filename is a reserved word.

If you wish to use it as a column name you must enclose it in brackets.

[filename]

Commented:
Select @date = [date],
        @routed_to = routed_to,
        @fil = [filename],
        @remote_id = remote_id,
        @result result,
        @pages_transferred = pages_transferred
 FROM   inserted

Open in new window

Commented:
sorry, the id is missing,
Again

Select 
        @ID = INSERTED.ID
        @date = INSERTED.[date],
        @routed_to = INSERTED.routed_to,
        @fil = INSERTED.[filename],
        @remote_id = INSERTED.remote_id,
        @result = INSERTED.result,
        @pages_transferred = INSERTED.pages_transferred
 FROM   INSERTED

Open in new window

Author

Commented:
Same issue, will parse but not alter when run.

Any other ideas?
HainKurtSr. System Analyst

Commented:
did you try [filename]?
HainKurtSr. System Analyst

Commented:
this works fine

with x as (select 'c:\x.txt' as filename)
select filename from x

filename
c:\x.txt

are you sure you have column with a name "filename"?
Top Expert 2012

Commented:
Contrary to popular belief "filename" is not a reserved keyword. At least not yet.  "FILE" on the other hand is a reserved keyword.

Author

Commented:
below is what I have in there right now, I think the problem is having to use "fm_faxin_att.filename" in the  the bottom select statement.  Line 39

 
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
DECLARE @date datetime
DECLARE @routed_to nvarchar(255)
DECLARE @remote_id nvarchar(255)
DECLARE @result nvarchar(255)
DECLARE @fil nvarchar(255)
DECLARE @pages_transferred int
--*****************************************************************
Select 
        --@ID = INSERTED.ID
        @date = INSERTED.[date],
        @routed_to = INSERTED.routed_to,
        @fil = INSERTED.[filename],
        @remote_id = INSERTED.remote_id,
        @result = INSERTED.result,
        @pages_transferred = INSERTED.pages_transferred
 FROM   INSERTED

--***************************************************************** 
--SET @date = (SELECT date FROM inserted)
--SET @routed_to = (SELECT routed_to FROM inserted)
----SET @fil = (SELECT filename FROM inserted)
--SET @remote_id = (SELECT remote_id FROM inserted)
--SET @result = (SELECT result FROM inserted) 
--SET @pages_transferred = (SELECT pages_transferred FROM inserted)
----SET @id = (SELECT id FROM inserted)
WAITFOR DELAY '00:00:02'
IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
  BEGIN
SELECT     fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
FROM         fm_faxin INNER JOIN
                      fm_faxin_att ON fm_faxin.id = fm_faxin_att.id RIGHT OUTER JOIN
                      inserted ON inserted.id = fm_faxin_att.id

Open in new window

Commented:
I agree with HainKurt
HainKurtSr. System Analyst

Commented:
also use this:

SET @date = (SELECT date FROM inserted)
SET @routed_to = (SELECT routed_to FROM inserted)
SET @fil = (SELECT filename FROM inserted)
SET @remote_id = (SELECT remote_id FROM inserted)
SET @result = (SELECT result FROM inserted)
SET @pages_transferred = (SELECT pages_transferred FROM inserted)
-->
select @date = date, @routed_to = routed_to, @fil = filename, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted
Ron MalmsteadInformation Services Manager

Commented:
..can you post the error message so we know what line number it is that is err?

Author

Commented:
"are you sure you have column with a name "filename"?"

Yes it's the only column being used from the attachment table.

Commented:
tryit with the variable
SELECT     fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
FROM         fm_faxin INNER JOIN
                      fm_faxin_att ON fm_faxin.id = fm_faxin_att.id 
Where  fm_faxin_att.id = @ID

Open in new window

Ron MalmsteadInformation Services Manager

Commented:
Change fm_faxin_att.filename

to

fm_faxin.fil
HainKurtSr. System Analyst

Commented:
also for this trigger to work, you should have one record in inserted table :)

if you insert multiple records somehow, your trigger will fail since you have multiple records in inserted table...
Top Expert 2012

Commented:
Please post the entire TRIGGER and the schema for fm_faxin.  The TRIGGER you posted is truncated.

Also, it should be pointed out, that as written your code is fundementally flawed.  It only supports inserts with a single row.
HainKurtSr. System Analyst

Commented:
when you run

SELECT top 1 filename from  fm_faxin_att

or

SELECT top 1 * from  fm_faxin_att

do you see filename column?

Author

Commented:
"Please post the entire TRIGGER"

My initial post covers that,

Top 1 yields
filename
------------------
pdfC2FC8.pdf

(1 row(s) affected)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		TCG
-- Create date: 10/17/09
-- Mod date:	08/03/11
-- Description:	testMail Trigger
-- =============================================
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
DECLARE @date datetime
DECLARE @routed_to nvarchar(255)
DECLARE @remote_id nvarchar(255)
DECLARE @result nvarchar(255)
DECLARE @fil nvarchar(255)
DECLARE @pages_transferred int

SET @date = (SELECT date FROM inserted)
SET @routed_to = (SELECT routed_to FROM inserted)
--SET @fil = (SELECT filename FROM inserted)
SET @remote_id = (SELECT remote_id FROM inserted)
SET @result = (SELECT result FROM inserted) 
SET @pages_transferred = (SELECT pages_transferred FROM inserted)
--SET @id = (SELECT id FROM inserted)
WAITFOR DELAY '00:00:02'
IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
  BEGIN
SELECT     fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
FROM         fm_faxin INNER JOIN
                      fm_faxin_att ON fm_faxin.id = fm_faxin_att.id RIGHT OUTER JOIN
                      inserted ON inserted.id = fm_faxin_att.id
	--SET @fil = filt
	DECLARE @sub varchar(255)
    DECLARE @msg varchar(MAX)
	SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + '@fil' + ' ---- '
    SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 
'You can view the fax by following this link: https://www.example.com/ibfax/' + '@fil' 
 
EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'tcg',
	@recipients='tcg@example.com; tester@example.com',
--	@recipients=N'tester@example.com', 
	@body= @msg,  
	@subject = @sub 

END
END

Open in new window

Top Expert 2012

Commented:
>>My initial post covers that<<
Nope.  As I previously indicated, your initial post was truncated.

Now all we need is the schema for the fm_faxin table and the mystery should be solved.

HainKurtSr. System Analyst

Commented:
i dont see any issue with the code you submitted last...
when you comment out line 38-41, does it compile?
Top Expert 2012

Commented:
Also, and once again, you do understand that this TRIGGER as posted does not support any INSERT that affects more than one row, right?

Author

Commented:
"Also, and once again, you do understand that this TRIGGER as posted does not support any INSERT that affects more than one row, right?"

Yes, they only happen one at a time -- always.

2 tables see screen shot attached... fmTables

Author

Commented:
"i dont see any issue with the code you submitted last...
when you comment out line 38-41, does it compile?"

No,

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "fm_faxin_att.filename" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "fm_faxin.date" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "fm_faxin.remote_id" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "fm_faxin.result" could not be bound.
HainKurtSr. System Analyst

Commented:
I guess you did not comment out those lines: 38,39,40, 41, four lines...
HainKurtSr. System Analyst

Commented:
I mesan these lines

--SELECT     fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
--FROM         fm_faxin INNER JOIN
--                      fm_faxin_att ON fm_faxin.id = fm_faxin_att.id RIGHT OUTER JOIN
--                      inserted ON inserted.id = fm_faxin_att.id

Author

Commented:
This trigger does work if I comment out the  --SET @fil = (SELECT filename FROM inserted) and treat @fil as a string.

Just got an email as a fax arrived:
To:tcg@example.com
From:  NoReply@example.com
Subject:New VIEW FAX notification --- 5553523095 @fil ----

NEW  -- We received your fax at Aug  4 2011  2:36PM. It was 4 pages
 You can view the fax by following this link: https://www.example.com/ibfax/@fil

Author

Commented:
"I mesan these lines"

I did comment out those lines.
Sr. System Analyst
Commented:
I simpilfied a bit and formnatted your code, give it a try

modify this line as necessary (I am not sure what datatype is your id column)

DECLARE @id int
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for trigger here
	DECLARE @date datetime
	DECLARE @routed_to nvarchar(255)
	DECLARE @remote_id nvarchar(255)
	DECLARE @result nvarchar(255)
	DECLARE @fil nvarchar(255)
	DECLARE @pages_transferred int
	DECLARE @id int

	select @id =id, @date = date, @routed_to = routed_to, @fil = filename, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted 

	WAITFOR DELAY '00:00:02'
	IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
	BEGIN
		SELECT fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
		  FROM fm_faxin INNER JOIN
		       fm_faxin_att ON fm_faxin.id = fm_faxin_att.id
		 WHERE fm_faxin_att.id = @id

		DECLARE @sub varchar(255)
		DECLARE @msg varchar(MAX)
		SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + '@fil' + ' ---- '
		SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 
		'You can view the fax by following this link: https://www.example.com/ibfax/' + '@fil' 

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'tcg',
			@recipients='tcg@example.com; tester@example.com',
			--	@recipients=N'tester@example.com', 
			@body= @msg,  
			@subject = @sub 
	END
END

Open in new window

Author

Commented:
When I comment out 38-41:

Msg 207, Level 16, State 1, Procedure trgFaxinTest, Line 27
Invalid column name 'filename'.

That line is the SET @fil statement
HainKurtSr. System Analyst

Commented:
also good to add these lines

      DECLARE @ins_rec_count int
      select @ins_rec_count = count(1) from inserted
      if @ins_rec_count>1 return;
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for trigger here
	DECLARE @date datetime
	DECLARE @routed_to nvarchar(255)
	DECLARE @remote_id nvarchar(255)
	DECLARE @result nvarchar(255)
	DECLARE @fil nvarchar(255)
	DECLARE @pages_transferred int
	DECLARE @id int

	DECLARE @ins_rec_count int
	select @ins_rec_count = count(1) from inserted
	if @ins_rec_count>1 return;

	select @id =id, @date = date, @routed_to = routed_to, @fil = filename, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted 

	WAITFOR DELAY '00:00:02'
	IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
	BEGIN
		SELECT fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
		  FROM fm_faxin INNER JOIN
		       fm_faxin_att ON fm_faxin.id = fm_faxin_att.id
		 WHERE fm_faxin_att.id = @id

		DECLARE @sub varchar(255)
		DECLARE @msg varchar(MAX)
		SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + '@fil' + ' ---- '
		SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 
		'You can view the fax by following this link: https://www.example.com/ibfax/' + '@fil' 

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'tcg',
			@recipients='tcg@example.com; tester@example.com',
			--	@recipients=N'tester@example.com', 
			@body= @msg,  
			@subject = @sub 
	END
END

Open in new window

HainKurtSr. System Analyst

Commented:
another problem with @fil (line 37 above)
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for trigger here
	DECLARE @date datetime
	DECLARE @routed_to nvarchar(255)
	DECLARE @remote_id nvarchar(255)
	DECLARE @result nvarchar(255)
	DECLARE @fil nvarchar(255)
	DECLARE @pages_transferred int
	DECLARE @id int

	DECLARE @ins_rec_count int
	select @ins_rec_count = count(1) from inserted
	if @ins_rec_count>1 return;

	select @id =id, @date = date, @routed_to = routed_to, @fil = filename, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted 

	WAITFOR DELAY '00:00:02'
	IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
	BEGIN
		SELECT fm_faxin_att.filename, fm_faxin.date, fm_faxin.remote_id, fm_faxin.result
		  FROM fm_faxin INNER JOIN
		       fm_faxin_att ON fm_faxin.id = fm_faxin_att.id
		 WHERE fm_faxin_att.id = @id

		DECLARE @sub varchar(255)
		DECLARE @msg varchar(MAX)
		SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + '@fil' + ' ---- '
		SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 
		'You can view the fax by following this link: https://www.example.com/ibfax/' + @fil 

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'tcg',
			@recipients='tcg@example.com; tester@example.com',
			--	@recipients=N'tester@example.com', 
			@body= @msg,  
			@subject = @sub 
	END
END

Open in new window

HainKurtSr. System Analyst

Commented:
do you have filename column in fm_faxin?
did you test my last post, with some fixes?
HainKurtSr. System Analyst

Commented:
run these:

select top 1 filename from fm_faxin

select top 1 * from fm_faxin

what do you get? what is table structure fm_faxin? probably you dont have filename here

Author

Commented:
line 37 not a problem here but a feature...
HainKurtSr. System Analyst

Commented:
we are talking about different codes and diffrent lines all the time, please use last code ZI posted and refer to those line numbers so we can understand each other...
HainKurtSr. System Analyst

Commented:
I know, but putting '@fil' on subject line is not good instead of actual file name, right :)

Author

Commented:
filename is in the attachment table.
select top 1 * from fm_faxin won't work as it should be fm_faxin_att

filename
-----------
pdfC2FC8.pdf

(1 row(s) affected)

Author

Commented:
"know, but putting '@fil' on subject line is not good instead of actual file name, right :)"

Yes, I sorta want the filename to show up there and the web address in the body.
HainKurtSr. System Analyst

Commented:
your trigger

ALTER TRIGGER [trgFaxinTest] ON  [dbo].[fm_faxin]

says you insert record into this table
and you are trying to select filename from inserted one, which is identical to this table!
HainKurtSr. System Analyst

Commented:
your original code

SET @fil = (SELECT filename FROM inserted)

??? also thats why I used

select @id =id, @date = date, @routed_to = routed_to, @fil = filename, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted

if filename is in fm_faxin_att

then you should do

select @id =id, @date = date, @routed_to = routed_to, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted
select @fil=filename from  fm_faxin_att where id=@id

or something like this to get filename from this table


Author

Commented:
Here's a visual:

 query designer for select statement
HainKurtSr. System Analyst

Commented:
then this code should work
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for trigger here
	DECLARE @date datetime
	DECLARE @routed_to nvarchar(255)
	DECLARE @remote_id nvarchar(255)
	DECLARE @result nvarchar(255)
	DECLARE @fil nvarchar(255)
	DECLARE @pages_transferred int
	DECLARE @id int

	DECLARE @ins_rec_count int
	select @ins_rec_count = count(1) from inserted
	if @ins_rec_count>1 return;

	select @id =id, @date = date, @routed_to = routed_to, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted
	select @fil=filename from  fm_faxin_att where id=@id

	WAITFOR DELAY '00:00:02'
	IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
	BEGIN
		DECLARE @sub varchar(255)
		DECLARE @msg varchar(MAX)
		SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + '@fil' + ' ---- '
		SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 
		'You can view the fax by following this link: https://www.example.com/ibfax/' + @fil 

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'tcg',
			@recipients='tcg@example.com; tester@example.com',
			--	@recipients=N'tester@example.com', 
			@body= @msg,  
			@subject = @sub 
	END
END

Open in new window

HainKurtSr. System Analyst

Commented:
and another fix for @fil

aloso removed the select statement, I guess you dont need that part...
ALTER TRIGGER [trgFaxinTest] 
   ON  [dbo].[fm_faxin] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for trigger here
	DECLARE @date datetime
	DECLARE @routed_to nvarchar(255)
	DECLARE @remote_id nvarchar(255)
	DECLARE @result nvarchar(255)
	DECLARE @fil nvarchar(255)
	DECLARE @pages_transferred int
	DECLARE @id int

	DECLARE @ins_rec_count int
	select @ins_rec_count = count(1) from inserted
	if @ins_rec_count>1 return;

	select @id =id, @date = date, @routed_to = routed_to, @remote_id = remote_id, @result = result, @pages_transferred = pages_transferred FROM inserted
	select @fil=filename from  fm_faxin_att where id=@id

	WAITFOR DELAY '00:00:02'
	IF @routed_to = 'scanning@example.com' --AND @remote_id = '555-555-1234'
	BEGIN
		DECLARE @sub varchar(255)
		DECLARE @msg varchar(MAX)
		SET @sub = 'New VIEW FAX notification --- '  + @remote_id + ' '   + @fil + ' ---- '
		SET @msg = 'NEW  -- We received your fax at ' + Cast(@date as varchar (35)) + '. It was ' + CAST(@pages_transferred as varchar(10)) + ' pages' + char(10) + char(13)+ 'You can view the fax by following this link: https://www.example.com/ibfax/' + @fil 

		EXEC msdb.dbo.sp_send_dbmail 
			@profile_name = 'tcg',
			@recipients='tcg@example.com; tester@example.com',
			--	@recipients=N'tester@example.com', 
			@body= @msg,  
			@subject = @sub 
	END
END

Open in new window

Author

Commented:
Thanks for sticking with this. I really appreciate it.

Author

Commented:
Both the above resolved so far and looks like it'll work -- reply#s ID: 36311915 & 36311923
.
So basically, you made it have 2 select statements and the join is gone. super. didn't know you could stack them like that in a trigger.
Thanks.

Author

Commented:
Trigger not firing on those two though. Tested it with 2 incoming faxes so far. Running both versions at once.
The original will run but no filename as before.
HainKurtSr. System Analyst

Commented:
did you check this line

DECLARE @id int

is it int or maybe it should be bigint...
HainKurtSr. System Analyst

Commented:
or varchar :)

Author

Commented:
ID is float
HainKurtSr. System Analyst

Commented:
So change itto float ;)
HainKurtSr. System Analyst

Commented:
float? what kind of data are you storing here? float does not seem right, but anyway, Line 17

DECLARE @id int

-->

DECLARE @id float

Author

Commented:
I did, my original way still works but poorly (without the filename). Both of your examples parse & alter they just don't send the email.

New direction?  I suppose I need to create a table, write an insert trigger to combine the data I need and then put a trigger on the new new table to send the email. I'm pretty sure you can't trigger on a view.

Thanks again

Author

Commented:
Float is what the faxmaker database uses.
HainKurtSr. System Analyst

Commented:
I dont see any issue with code :)

select top 3 id from  fm_faxin_att

what does this return?
HainKurtSr. System Analyst

Commented:
is the datacolumn id @ fm_faxin_att float?
HainKurtSr. System Analyst

Commented:
if you cannot make this trigger work, nothing will work :)

Author

Commented:
select top 3 id from  fm_faxin_att =

130237376900104
130237403700106
130237497000108
HainKurtSr. System Analyst

Commented:
so it is not float :) they look bigint to me :)  what is the datatype of id columns in both tables?
fm_faxin_att & fm_faxin
HainKurtSr. System Analyst

Commented:
Line 17

DECLARE @id int
-->
DECLARE @id bigint

does this solve the issue?

Author

Commented:
both float.

Author

Commented:
So I did try bigint but nothing yet...

select top 3 id from  fm_faxin_att
Select top 3 id from fm_faxin

doing this adds a clue as to a possibility. fm_fax_att stores one IMG file and filename linked to id on fm_faxin.

fm_faxin stores a record each for all receiving mailboxes (company users who receive a copy of the fax) so for every incoming I generate 5 rows in faxin and 1 associated row in faxin_att . That is why there's the "If routed_to = scanning..."

My intention is to email the sender of the fax a receipt of the fax they just sent along with the link to view it online. If I can't get this to work as is, I guess I'll figger something out...
Top Expert 2012

Commented:
When you can post the schema for the fm_faxin table, I will be glad to help out.  I am just lousy at guessing.
HainKurtSr. System Analyst

Commented:
so you insert 5 record into fm_faxin for each fax
and there is one record for those inserted 5 records associated with the id
and you want to send email only once, thats why you use "IF @routed_to = 'scanning@example.com' "
and the id column in both table is float

are these correct?

and when you comment out

select @fil=filename from  fm_faxin_att where id=@id

it works without filename

are these correct? if yes, I suspect you have multiple records in fm_faxin_att

run this

select id, count(1) from fm_faxin_att
group by id having count(1) >1

do you get any records? if yes, then dont know what to do :) you have multiple record per id and you should investigate why!

try this

select @fil=filename from  fm_faxin_att where id=@id
-->
select top 1 @fil=filename from  fm_faxin_att where id=@id

does it work this way?

Author

Commented:
acperkins. I attached the two tables schema. There's a screenshot of thee schema way back too.

I suspect in my original trigger filename column does not exist. There must be a problem with joins in a trigger that I don't understand.

I think I just figured it out. I changed;
SET @fil = (SELECT filename FROM inserted)
to
SET @fil = (SELECT filename FROM fm_faxin_att)
 and it parsed and altered. The filename column does not exist in the inserted table as it is in the other table...

 
FAXmakerArchive	dbo	fm_faxin	date	1	NULL	NO	datetime	NULL	NULL	NULL	NULL	NULL	3	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	routed_to	2	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	full_subject	3	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	clean_subject	4	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	remote_id	5	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	result	6	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	info	7	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	dtmf_did	8	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	call_duration	9	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	pages_transferred	10	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	line	11	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	attach_count	12	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin	id	13	NULL	NO	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

++++++++++++++++++++++++++++++++++++++++++++++
FAXmakerArchive	dbo	fm_faxin_att	id	1	NULL	NO	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin_att	filename	2	NULL	YES	nvarchar	255	510	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin_att	attdata	3	NULL	YES	image	2147483647	2147483647	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin_att	attsize	4	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
FAXmakerArchive	dbo	fm_faxin_att	attid	5	NULL	NO	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Open in new window

Top Expert 2012

Commented:
Correct. The schema for INSERTED table matches the table with the TRIGGER (fm_faxin).  In this case filename does not exist in the fm_faxin, hence the error.

This is what we have all been trying to tell you from the start.  As I stated yesterday http:#a36311570, simply posting the schema would have gone a long way to solve this.

Author

Commented:
I suppose the screenshot in ID: 36311638 wasn't good enough?

Also, and once again, you do understand that this TRIGGER as posted does not support any INSERT that affects more than one row, right?

One row on the inserted yes, your post says nothing about the other table.
HainKurtSr. System Analyst

Commented:
I posted many codes (@ 36311923, 36311915, 36311864) that have:

select @fil=filename from  fm_faxin_att where id=@id

and now you say

"
I think I just figured it out. I changed;
SET @fil = (SELECT filename FROM inserted)
to
SET @fil = (SELECT filename FROM fm_faxin_att)
 and it parsed and altered. The filename column does not exist in the inserted table as it is in the other table...
"

did we waste lots of time here?
Top Expert 2012

Commented:
>>I suppose the screenshot in ID: 36311638 wasn't good enough?<<
As you can tell, it was not.

>>One row on the inserted yes, your post says nothing about the other table. <<
Correct.  The TRIGGER is not on the other table.

Author

Commented:
Timing issue with insert was from delay caused by writing binary file data. I ended up putting an inserted trigger on the attachment table instead

Author

Commented:
Thank you for helping,

HainKurt,  It wasn't a waste of time at all and it ended up being a timing issue, the insert for the fm_faxin table took place first and then from there it writes the binary file data then the filename is generated last.
Turns out that the filename was not even written yet when the trigger was firing thus no filename string  was even being returned.
I put the trigger on the fm_faxin_att table and all is well.
HainKurtSr. System Analyst

Commented:
ok, now got the issue :) nothing to do with code then... good to know it is solved eventually lol...