Error updating a table from ACCESS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Error updating a table from ACCESS

Author

Topic

kusumjain
Starting Member

21 Posts

Posted - 2009-03-06 : 15:04:34

I have a audit trigger on a table. If the trigger is enabled, I get an ODBC call failed when trying to update, insert or delete from ACCESS. If I disable the trigger than I can insert data from ACCESS.

I need to audit when the users update the records in the table.

Any help is appreciated.

Thanks,
Kusum Jain

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-09 : 16:02:06

show us your vba or vb.net code

kusumjain
Starting Member

21 Posts

Posted - 2009-03-10 : 10:36:44

There is no VBA or .Net code. It is just using Likned tables in ACCESS

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-10 : 12:43:10

ok show us the trigger

kusumjain
Starting Member

21 Posts

Posted - 2009-03-16 : 10:51:29

Sorry was on vacation.....Here is the trigger...Any help is appreciated
ALTER TRIGGER [dbo].[trgAuditVolAdj]
ON [dbo].[Volume_Adjustments]
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON

DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)

SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'

INSERT INTO #inputbuffer
EXEC (@ExecStr)

SET @Qry = (SELECT EventInfo FROM #inputbuffer)

insert into Audit
Values (CURRENT_TIMESTAMP,Host_name() ,suser_sname(), App_Name(), @Qry )

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-18 : 11:33:56

best to test things out in SSMS itself using print statement before deploying it and testing it in front end only. I think one problem is that
1. EventInfo is too short so make it max, because i kept getting following error in SSMS
'String or binary data would be truncated.'

CREATE TRIGGER trgAuditVolAdj
ON [dbo].[Volume_Adjustments]
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON

DECLARE @ExecStr varchar(50), @Qry nvarchar(255)

CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(max)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + CAST(@@SPID AS VARCHAR(MAX)) + ')'

INSERT INTO #inputbuffer
EXEC (@ExecStr)

insert into Audit
SELECT CURRENT_TIMESTAMP,
Host_name(),
suser_sname(),
App_Name(),
EventInfo AS Qry
FROM #inputbuffer
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources