invalid object name on trigger

[bustercoder]

Hello,
I've done everything I can think of to try and make this work the way Microsoft says it's supposed to, maybe I'm just overlooking some mundane detail:


The following makes no difference:
- used the object owner name and naming in general
- checked permissions on all the objects involved
- exec the sp on its own, it fires ok
- the text file is created ok
- the data is written to the auditlog table ok
- tested trigger with stored proc and with only selecting from a table (code commented below), made no difference, same error

/*** Here is my data table ***/
create table test1 (
Field1 char(5),
Field2 char(5),
Field3 char(5)
)

/*** Here is my static audit table ***/
create table auditlog (
FieldId int identity,
Field1 char(5),
Field2 char(5),
Field3 char(5)
)

/*** I use this procedure to grab the data out ***/
create procedure testsp1 @FieldId int as
set nocount on
select * from auditlog WITH (NOLOCK) where Fieldid = @FieldId
go

/*** Here is my trigger ***/
alter trigger testtr1 on test1 for insert as
set nocount on
declare @msg varchar(255), @IdentValue varchar(8)
insert into AuditLog (Field1,Field2,Field3)
select * from inserted
select @IdentValue = convert(varchar,@@identity)
--select @msg = 'isql -Q"exec testsp1 '+@IdentValue+'" -E -w255 -oc:\temp\audit'+@IdentValue+'.txt'
select @msg = 'isql -Q"select * from auditlog " -E -w255 -oc:\temp\audit'+@IdentValue+'.txt'
exec master..xp_cmdshell @msg
go

/*** Here is my test data ***/
insert into test1 values ('123','123','123')

/*** Here is my error: ***/
Msg 208, Level 16, State 1, Server DRDEV2, Line 1
Invalid object name 'auditlog'.

Any help would be so GREATLY appreciated.

Thanks,
Buster

 

[vbkris]

the user who is doing the insert has all permissions on the auditlog object???

Known is handfull, Unknown is worldfull

 

[SQLDenis]

what happens when you change AuditLog to auditlog in the trigger? everywhere else its lowercase, is your db case sensitive?


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[SQLSister]

Incidentally you don't want to use @@identity - if there is ever another trigger that fires before this one, you will pull the wrong indentity value. Scope_identity () is a better choice. It may work fine now, but eventually it won't and you may not even realize that it isn't working for some time. And then you have a horrible mess to clean up.

Your isql part will only pull the data from the last record. If a multiple record insert was done you will not get a txt file for each one. Triggers must always plan for multiple record inserts even if you don't think there will be any. Experience shows me that sooner or later there will be.

I created and ran your proc just fine with no error message so I suspect your problem is either permissions or case sensitivity as stated above.


Questions about posting. See faq183-874

 

[bustercoder]

Thanks everyone! It had to do with permissions!