SQL Server Performance Forum – Threads Archive
Source Control in SQL Server 2005
Hi, Is there any other way to track and log database changes in sql server 2005 other than using DDL triggers. Thanks,Arijit
Yes, by using your own logic or a third-party tool. What exactly are you looking for? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
I prefer them rather than using third party tool,http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ddl-trigger-workbench/ fyi. Any reason why you don’t want to use that route? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi Frank/Satya, I am looking for something that would log all db changes( mostly changes to table structure, sps, UDFs and most importantly index changes). I have gone through the article mentioned by you for DDL Triggers before, but my question is Creating a DDL Trigger for DATABASE_LEVEL_EVENTS throws an exception when I try to create an index on any table. The same exception occurs when I try to drop an index.
Is there any way to log index changes? Thanks Arijit
What is that exception error?
I presume you are on latest SP. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi, I get the following exception. Create failed for index IX_USER_ID (Microsoft.SQLServer.Smo) Additional Information An exception occured while executing a transact sql statement or batch
(Microsoft.SQLServer.ConnectionInfo) Conditional Failed because the followng SET operations has incorrect settings: ‘ARITHABORT’
Verify that SET operations are correct for use with indexed views and /or indexes on computed columns and/or query notifications and/or xml data type methods (Microsoft SQL Server Error 1934)
Have you defined any SET operations within your code, executing a USE statement within a request to change the current database results in all subsequent requests executing under the new context. Similarly, changing the value for a SET option within a batch would imply that all subsequent executions would run under the newly set value. The error message shown (Msg 1934) is returned when the table is created with the right ANSI_NULLS values and the index is created with a wrong one. If the table is created with a wrong setting, and you try to then create an index on a computed column in the table, you will get such errors. Post your code for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi, When I created the index using the script it was successfully created. USE [Northwind]
GO
/****** Object: Index [iii] Script Date: 07/15/2007 20:15:41 ******/
CREATE NONCLUSTERED INDEX [ix_fk_user_id] ON [dbo].[testing_purpose]
(
[fk_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] But the problem comes when i create the index using the New Index Wizard. Again when I went to the Option section of the Database properties and set the "Arithmetic Abort Enabled" to true the index was successfully created. Is there any problem if I set this to true and is there in other way to solve this problem without changing the db properties. Thanks,
Arijit
There is a workaround to keep the procedure as a wrapper and use SET ARITHABORT ON at the beginning or if you are controlling the task from a client’s connection then issue SET ARITHABORT ON from the client when you connect. As a whole you could apply to the database itself by running
ALTER DATABASE db SET ARITHABORT ON Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>