| Dan Guzman 2005-04-13, 7:23 am |
| The error thrown by the application doesn't provide much help. It the
underlying error is security related, it may be because the invoking user
doesn't have permissions to access database Y or the user doesn't have
SELECT permissions on the Lics table.
For debugging, you might try a profiler trace Profiler trace including the
Exception event. Another method is to test security is execute an dummy
update from Query Analyzer using the problem userid:
SETUSER 'MyUser'
GO
UPDATE [X].[dbo].[Customer_MSTR]
SET [COMMENT2] = [COMMENT2]
WHERE 0 = 1
GO
SETUSER
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick Billings" <rick@dptechnology.com> wrote in message
news:ONKkje4PFHA.2652@TK2MSFTNGP10.phx.gbl...
> Can someone help me understand what I am doing wrong?
>
> I'm having trouble creating a trigger which references a table in a
> different database. Here is the message I get back (from MS Great
> Plains):
>
> Unhandled database exception:
> A save operation on table 'Customer_MSTR' failed accessing SQL data.
>
> EXCEPTION_CLASS_DB
> DB_ERR_SQL_DATA_ACCE
SS_ERR
>
> Here is the trigger's syntax:
>
> CREATE TRIGGER ModCusX ON [X].[dbo].[Customer_MSTR]
> AFTER UPDATE
> AS
> BEGIN
> IF @@ROWCOUNT = 0 RETURN
> UPDATE [dbo].[Customer_MSTR]
> SET [COMMENT2] = (SELECT TOP 1 [SerialNumber] FROM [Y].[dbo].[Lics]
> JOIN INSERTED i ON LTRIM(RTRIM(i.CUSTNMBR)) = [CustomerCode])
> END
>
|