ms access - SQL Server : create record fails when trigger is enabled -

i have old application must support. i'm pushing (hard) replace know management can like. front end in access 97 , has linked tables in sql server 2008 r2 data storage.

it has been working smoothly many years , refuse modify it.

i have been requested send automatic email whenever new record created in particular table. have been sending emails sql server agent jobs in past thought wouldn't hard send email trigger when record created, avoiding need make changes access97 front end.

i have created trigger lots of logging file built in can tell if falls on part way through, doesn't.

the error message i'm getting on client

"odbc--insert on linked table 'dbo_quote' failed."

this table being inserted , 1 trigger.

i'll post trigger code here if wants see don't think problem there. little long debugging code in there.

to prove works commented out lines this

set @masuburb = (select rtrim(masuburb) inserted) 

and set variables manually after declaring them code works when run directly.

after removing test changes ran application trigger disabled , used sql server profiler capture query executed , insert succeeded.

i deleted record created, enabled trigger again.

pasted captured query in management studio , ran end end , sent email. problem when query comes directly access.

i can't figure out why sql server sends failure message access if trigger enabled.

i have been looking better error messages nothing seems logged anywhere.

does have ideas how can locate problem?

(yeah yeah know rid of access)

thanks in advance david

update here trigger code. removed of debug logging.

use [quote] go  set ansi_nulls on go set quoted_identifier on go  alter trigger [dbo].[newmanagementquotationemail]     on  [quote].[dbo].[quote]      after insert  begin     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;      -- declare fields used     declare @dateentered        datetime     set @dateentered = ''     declare @contactname        nvarchar(50)     set @contactname = ''     declare @preparedfor        nvarchar(50)     set @preparedfor = ''     declare @mailaddress1       nvarchar(50)     set @mailaddress1 = ''     declare @mailaddress2       nvarchar(50)     set @mailaddress2 = ''     declare @masuburb           nvarchar(50)     set @masuburb = ''     declare @mastate            nvarchar(3)     set @mastate = ''     declare @mapcode            nvarchar(10)     set @mapcode = ''     declare @spnumber           nvarchar(10)     set @spnumber = ''     declare @spaddress1         nvarchar(50)     set @spaddress1 = ''     declare @spaddress2         nvarchar(50)     set @spaddress2 = ''     declare @spsuburb           nvarchar(50)     set @spsuburb = ''     declare @spstate            nvarchar(3)     set @spstate = ''     declare @sppcode            nvarchar(10)     set @sppcode = ''     declare @numberofunits      int     set @numberofunits = ''     declare @priceperunit       money     set @priceperunit = ''     declare @totalannualfee     money     set @totalannualfee = ''     declare @stationary         money     set @stationary = ''     declare @contactphonew      nvarchar(15)     set @contactphonew = ''     declare @contactphoneh      nvarchar(15)     set @contactphoneh = ''     declare @contactphonem      nvarchar(15)     set @contactphonem = ''     declare @fax                nvarchar(15)     set @fax = ''     declare @email              nvarchar(50)     set @email = ''     declare @whychooseus        nvarchar(50)     set @whychooseus = ''     declare @currentlymanagedby nvarchar(50)     set @currentlymanagedby = ''     declare @datesent           datetime     set @datesent = ''     declare @callbackdate       datetime     set @callbackdate =''     declare @enteredby          nvarchar(15)     set @enteredby = ''      -- setup debug logging      declare @debug int     -- 0=off >=1 on --     set @debug = 2      if @debug > 0     begin         declare @cmd varchar(255)         declare @var varchar(255)         declare @logfile char(50)         set @logfile =  '>> c:\temp\newmanagementquotationemaillog.txt'          set @var = cast(getdate()as varchar)+ ': ' + '======= trigger newmanagementquotation trigger executed ======= (quote database, quote table)'         set @cmd = 'echo ' + @var + @logfile         exec master..xp_cmdshell @cmd     end     return     --=======================================================================================     -- declare variable send email      declare @var_emailaddress varchar(100)     declare @var_subject varchar(100)     declare @var_body  varchar(4000)     --declare @var_filename varchar(50)     declare @newline varchar(10)     set @newline = char(13)+char(10)      set @var_emailaddress = '<my email goes here>'     set @dateentered = (select dateentered inserted)     set @contactname = (select rtrim(contactname) inserted)     set @preparedfor = (select rtrim(preparedfor) inserted)     set @mailaddress1 = (select rtrim(mailaddress1) inserted)     set @mailaddress2 = (select rtrim(mailaddress2) inserted)     set @masuburb = (select rtrim(masuburb) inserted)     set @mastate = (select rtrim(mastate) inserted)     set @mapcode = (select rtrim(mapcode) inserted)     set @spnumber = (select rtrim(spnumber) inserted)     set @spaddress1 = (select rtrim(spaddress1) inserted)     set @spaddress2 = (select rtrim(spaddress2) inserted)     set @spsuburb = (select rtrim(spsuburb) inserted)     set @spstate = (select rtrim(spstate) inserted)     set @sppcode = (select rtrim(sppcode) inserted)     set @numberofunits = (select numberofunits inserted)     set @priceperunit = (select priceperunit inserted)     set @totalannualfee = (select totalannualfee inserted)     set @stationary = (select stationary inserted)     set @contactphonew = (select rtrim(contactphonew) inserted)     set @contactphoneh = (select rtrim(contactphoneh) inserted)     set @contactphonem = (select rtrim(contactphonem) inserted)     set @fax = (select rtrim(fax) inserted)     set @email = (select rtrim(email) inserted)     set @whychooseus = (select rtrim(whychooseus) inserted)     set @currentlymanagedby = (select rtrim(currentlymanagedby) inserted)     set @datesent = (select datesent inserted)     set @callbackdate = (select callbackdate inserted)     set @enteredby = (select rtrim(enteredby) inserted)      --================================================================================================       if @debug > 0     begin             set @var = cast(getdate()as varchar)+ ' 2) generate email trello started'             set @cmd = 'echo ' + @var + @logfile             exec master..xp_cmdshell @cmd     end      if @debug > 0     begin             set @var = cast(getdate()as varchar)+ ' 3.0) query text string begin'             set @cmd = 'echo ' + @var + @logfile             exec master..xp_cmdshell @cmd     end      set @var_subject = @spnumber      set @var_body = 'date entered= ' + cast(@dateentered varchar(12)) + char(13)+ char(10) +                     'contact name= ' + @contactname + char(13)+ char(10)  +                     'prepared for= ' + @preparedfor + char(13)+ char(10)  +                     'mail address 1= ' + @mailaddress1 + char(13)+ char(10)  +                     'mail address 2= ' + @mailaddress2 + char(13)+ char(10)  +                     'mail address suburb= ' + @masuburb + char(13)+ char(10)  +                     'sp address 1= ' + @spaddress1 + char(13)+ char(10)  +                     'sp address 2= ' + @spaddress2 + char(13)+ char(10)  +                     'sp state= ' + @spstate + char(13)+ char(10)  +                          'sp suburb='     + @spsuburb + char(13)+ char(10)  +                     'sp post code= ' + @sppcode + char(13)+ char(10)  +                     'number of units= ' + cast(@numberofunits varchar(5)) + char(13)+ char(10)  +                     'price per unit= ' + cast(@priceperunit varchar(10)) + char(13)+ char(10)  +                     'total annual fee= ' + cast(@totalannualfee varchar(10)) + char(13)+ char(10)  +                     'stationary= ' + cast(@stationary varchar(10))  + char(13)+ char(10)  +                     'contact phone w= ' + @contactphonew + char(13)+ char(10)  +                     'contact phone h= ' + @contactphoneh + char(13)+ char(10)  +                     'contact phone m= ' + @contactphonem + char(13)+ char(10)  +                     'fax= ' + @fax + char(13)+ char(10)  +                     'email= ' + @email + char(13)+ char(10)  +                     'why choose us= ' + @whychooseus + char(13)+ char(10)  +                     'currently managed by= ' + @currentlymanagedby + char(13)+ char(10)  +                     'date sent= ' + cast(@datesent varchar(12)) + char(13)+ char(10)  +                     'call date= ' + cast(@callbackdate varchar(12)) + char(13)+ char(10)  +                     'entered by= ' + @enteredby      if @debug > 0     begin             set @var = cast(getdate()as varchar)+ ' 3.9) query text string complete'             set @cmd = 'echo ' + @var + @logfile             exec master..xp_cmdshell @cmd     end       exec  msdb.dbo.sp_send_dbmail         @recipients = @var_emailaddress,         @subject = @var_subject,          @profile_name = 'sql email profile',         @body= @var_body      if @debug > 0     begin             set @var = cast(getdate()as varchar)+ ' 4) generate email trello complete'             set @cmd = 'echo ' + @var + @logfile             exec master..xp_cmdshell @cmd     end      --================================================================================================      if @debug > 0     begin             set @var = cast(getdate()as varchar)+ '======= trigger newmanagementquotation trigger complete ======='             set @cmd = 'echo ' + @var + @logfile             exec master..xp_cmdshell @cmd     end end  

update 2 no change in error message after adding keyword "return" trigger log contains 1 line expected

apr  7 2017  5:15pm: ======= trigger newmanagementquotation trigger executed ======= (quote database, quote table)    

just make sure clear. can capture sql generated access trigger disabled , run again directly in microsoft management studio trigger enabled , works. seems me access misinterpreting return code sql server how or sql server returning wrong code when trigger enabled. need figure out.

update 3 sql generated access captured in sql profiler

    exec sp_executesql  n'insert  "dbo"."quote"   (     "dateentered",     "contactname",     "preparedfor",     "mailaddress1",     "masuburb",     "mastate",     "mapcode",     "spnumber",     "spaddress1",     "spsuburb",     "spstate",     "sppcode",     "numberofunits",     "priceperunit",     "stationary",     "contactphonew",     "contactphoneh",     "contactphonem",     "email",     "whychooseus",     "currentlymanagedby",     "datesent",     "callbackdate",     "statusid",     "enteredby" )  values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25)',n'     @p1 datetime,     @p2 char(50),     @p3 char(50),     @p4 char(50),     @p5 char(50),     @p6 char(3),     @p7 char(10),     @p8 char(10),     @p9 char(50),     @p10 char(50),     @p11 char(3),     @p12 char(10),     @p13 int,     @p14 decimal(19,4),     @p15 decimal(19,4),     @p16 char(15),     @p17 char(15),     @p18 char(15),     @p19 char(50),     @p20 char(50),     @p21 char(50),     @p22 datetime,     @p23 datetime,     @p24 int,     @p25 char(15)',  '2017-04-07 00:00:00', 'farley bainsworth                                 ', 'bob johnson                                       ', '15 boburgal ave                                   ', 'kirrawee                                          ', 'nsw','2232      ', '101010    ', '100 armdale rd                                  ', 'the land                                            ', 'nsw','2460      ', 100, 50.0000, 500.0000, '02 66493300    ', '02 66493693    ', '0418256742     ', 'bobpollard@gmail.com                             ', 'why not                                           ', 'bob builder                                   ', '2017-04-07 00:00:00', '2017-08-07 00:00:00',1, 'bpollard       ' go 

does after insert (assuming) trigger on table dbo_quote contains insertion on table has trigger? if so, might check insertion code variables getting used.