Avatar of Phosphor
Phosphor
 asked on

Invalid column name 'filename' in trigger

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

Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
HainKurt

8/22/2022 - Mon
Ron Malmstead

filename is a reserved word.

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

[filename]
Haver Ramirez

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

Haver Ramirez

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Phosphor

ASKER
Same issue, will parse but not alter when run.

Any other ideas?
HainKurt

did you try [filename]?
HainKurt

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

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

ASKER
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

Haver Ramirez

I agree with HainKurt
Your help has saved me hundreds of hours of internet surfing.
fblack61
HainKurt

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 Malmstead

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

ASKER
"are you sure you have column with a name "filename"?"

Yes it's the only column being used from the attachment table.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Haver Ramirez

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 Malmstead

Change fm_faxin_att.filename

to

fm_faxin.fil
HainKurt

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...
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
Anthony Perkins

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.
HainKurt

when you run

SELECT top 1 filename from  fm_faxin_att

or

SELECT top 1 * from  fm_faxin_att

do you see filename column?

Phosphor

ASKER
"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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Anthony Perkins

>>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.

HainKurt

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

Also, and once again, you do understand that this TRIGGER as posted does not support any INSERT that affects more than one row, right?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Phosphor

ASKER
"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
Phosphor

ASKER
"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.
HainKurt

I guess you did not comment out those lines: 38,39,40, 41, four lines...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

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
Phosphor

ASKER
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
Phosphor

ASKER
"I mesan these lines"

I did comment out those lines.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
HainKurt

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.
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
Phosphor

ASKER
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
HainKurt

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

HainKurt

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

do you have filename column in fm_faxin?
did you test my last post, with some fixes?
HainKurt

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

Phosphor

ASKER
line 37 not a problem here but a feature...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
HainKurt

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...
HainKurt

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

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

ASKER
"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.
HainKurt

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!
HainKurt

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


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Phosphor

ASKER
Here's a visual:

 query designer for select statement
HainKurt

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

HainKurt

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Phosphor

ASKER
Thanks for sticking with this. I really appreciate it.
Phosphor

ASKER
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.
Phosphor

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
HainKurt

did you check this line

DECLARE @id int

is it int or maybe it should be bigint...
HainKurt

or varchar :)
Phosphor

ASKER
ID is float
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

So change itto float ;)
HainKurt

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

DECLARE @id int

-->

DECLARE @id float
Phosphor

ASKER
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
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
Phosphor

ASKER
Float is what the faxmaker database uses.
HainKurt

I dont see any issue with code :)

select top 3 id from  fm_faxin_att

what does this return?
HainKurt

is the datacolumn id @ fm_faxin_att float?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

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

ASKER
select top 3 id from  fm_faxin_att =

130237376900104
130237403700106
130237497000108
HainKurt

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
HainKurt

Line 17

DECLARE @id int
-->
DECLARE @id bigint

does this solve the issue?
Phosphor

ASKER
both float.
Phosphor

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

When you can post the schema for the fm_faxin table, I will be glad to help out.  I am just lousy at guessing.
HainKurt

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?
Phosphor

ASKER
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Anthony Perkins

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.
Phosphor

ASKER
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.
HainKurt

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

>>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.
Phosphor

ASKER
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
Phosphor

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
HainKurt

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