January 22, 2015 at 6:34 pm
Is anyone trying to make the tool look at system objects (registry/file) as well? Are their complimentary projects to do that?
January 22, 2015 at 7:39 pm
I do have some code to watch for and log changes to SQLAgent job definitions: that works well, though it requires creation of a sqlver schema in the system database msdb and adding triggers there...something that not everyone would be comfortable with. For this reason I have kept that code separate from the main SQLver code. Let me know if you are interested in SQLAgent change tracking: I would be happy to share that.
For monitoring changes on the server outside the database, you really would need an external service or application to do that sort of monitoring. Theoretically it would be possible (but not advisable) to inspect files or registry settings from inside TSQL or from a CLR assembly...but this would not be the same thing as monitoring files or registry hives for changes. There are windows API calls that do make this kind of service possible to write, however.
For simple monitoring of files on the server, check out the stand-alone utility watchDirectory at http://www.watchdirectory.net [/url] This would let you monitor and react to file changes without coding. They might have suggestions on monitoring registry changes too. (I am not associated with watchDirectory in any way, other than I have had success using the tool in the past.)
February 17, 2015 at 12:58 pm
Thank you for sharing.
I am testing this on a SQL 2008 instance. I commented out the s.total_rows and s.last_rows columns in the spShowSlowQueries proc and it works fine after a little bit of troubleshooting.
In case anyone else runs into an issue I am listing the steps to reproduce the problem, in SQL 2008, and the solution. I haven't traced the underlying issue though.
Setup:
1. Run the script as is
2. Get Error
Msg 207, Level 16, State 1, Procedure spShowSlowQueries, Line 25
Invalid column name 'total_rows'.
Msg 207, Level 16, State 1, Procedure spShowSlowQueries, Line 26
Invalid column name 'last_rows'.
3. Comment out the columns and run the script again.
4. Get a bunch of creation failures due to the procs already existing but the spShowSlowQueries proc is created this time.
Testing:
1. Create Stored Proc
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
SELECT 'TEST';
END;
2. Receive the following error.
Msg 2801, Level 16, State 1, Procedure dtgLogSchemaChanges, Line 165
The definition of object 'dtgLogSchemaChanges' has changed since it was compiled.
The statement has been terminated."
3. Try to create, drop, or alter any procs. Get same error.
4. Try to drop the dtgLogSchemaChanges trigger from the database. Get same error.
5. Try to run the spUninstall proc. Get same error.
6. Disable the trigger.
7. Test DDL statements. They should work now.
8. Enable the trigger.
8. Test DDL statements. They get errors again.
Solution:
1. Script the dtgLogSchemaChanges trigger out as a create to new window.
2. Changed the create statement to an alter.
3. Execute the statement.
Test again:
1. Create Stored Proc
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
SELECT 'TEST';
END;
This should work now.
I haven't traced out why the script puts it self in a bad state but if you get an error while running the script it is best to run the spUninstall proc to clear out everything and then start from scratch. The create script should be wrapped in a try catch to handle rollback on error.
January 17, 2017 at 11:30 am
FYI, I have set up a GitHub repository for SQLVer at https://github.com/DavidRueter/SQLVer
Please use the GitHub repository, as the SourceForge repository is being deprecated.<br
March 10, 2017 at 8:52 am
I am getting the below error when a step in a nightly job runs. Of course I also get it when randomly dropping tables. In the job I used a workaround: Is there a permanent fix for this?DISABLE TRIGGER [dtgLogSchemaChanges] ON DATABASE;
IF OBJECT_ID('tempPermHearings') IS NOT NULL
BEGIN
DROP TABLE dbo.tempPermHearings; --This fails
END
SELECT * INTO tempPermHearings FROM dbo.vw_OC_PermHearings3;
ENABLE TRIGGER [dtgLogSchemaChanges] ON DATABASE;
Error logging DDL changes in database trigger dtgLogSchemaChanges: Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Your DDL statement may have been successfully processed, but changes were not logged by the version tracking system.
Msg 3616, Level 16, State 2, Line 7
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply