After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:
CREATE TRIGGER [ddl_log]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
DECLARE @data XML
SET @data = EVENTDATA()
EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
GETDATE(),
CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(NVARCHAR(100), HOST_NAME()),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
) ;
REVERT
END
As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.
Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.
Since Access can successfully connect to the SQL Server and execute queries against it, the connection string the Access is using must be correct. The connection made through ADO, on the other hand is relying on CurrentProject.Connection property, which we don't know what it is, and fails unexpectedly. Inside AccessDataBaseEngine_X64.exe seems to have no ODBC drivers, and Access works with or without AccessDataBaseEngine_X64.exe installed. So the DSN the Access is using didn't change, therefore the problem must be the connection string used in ADO.
So if you use the same connection string as that of Access, the problem should go away.
As for why it started behaving erratically all of sudden I can't fully comment on that. But what Microsoft states about the CurrentProject.Connection property might give you a clue (the relevant section in bold).
Use the Connection property to refer to the Connection object of the current Microsoft Access project (.adp) or Access database object. You can use the Connection property to call methods on the Connection object such as BeginTrans and CommitTrans.
Note:
The Connection property actually returns a reference to a copy of the ActiveX Data Object (ADO) connection for the active database. Thus, applying the Close method or in anyway attempting to alter the connection through the Connection object’s methods or properties will have no affect on the actual connection object used by Microsoft Access to hold a live connection to the current database. Since the Connection property is the main Shape provider connection, the following information is necessary when using this property.
- MSDataShape uses Recordset.CursorLocation = adUseClient. Do not set CursorLocation prior to assigning a recordset to CurrentProject.Connect.
- MSDataShape uses Recordset.CursorType = adOpenStatic. Do not set CursorType prior to assinging a recordset to CurrentProject.Connection.
- MSDataShape accepts Recordset.LockType = adLockOptimistic, adLockBatchOptimistic, or adLockReadOnly (default). If set to adLockPessimistic, it is changed to adLockOptimistic.
- The shape connection does not support the all ADOX operation, specifically the Columns.Properties collection is not supported.
- In order to ensure that a shape connection will work correctly, the Command.CommandType must be set to adCmdTable.
Bonus:
How to know what files were involved with AccessDatabaseEngine_x64.exe? (I was curious!)
Use Orca
Best Answer
You can try to do this...
...but, since application name and other connection properties are easily spoofed, it is very easy for your users to bypass them - they can just tell SSMS, for example, that it should present itself as the same
APP_NAME(). So, it all depends on your definition of "in a reliable manner."When things go south
In order to fix a bad trigger, you can bypass logon triggers entirely by using the Dedicated Administrator Connection (DAC) to connect and modify / disable the trigger. You can also keep your initial connection established when you create / enable the trigger - existing connections won't go through the trigger again unless they get disconnected.