February 4, 2008 at 1:14 pm
I have created a DDL Trigger for ddl_table_view_events for one of our databases. The databaselog file that will capture these information was also created. However, I see that the databaselog generated produced 2 different results when for instance, a table 's column is modified through Management Studion Query (using Alter table statement) versus the one modified through the Management Studio itself (by right clicking table, right clicking modify)... I believe there is a big bug because the one modified through Management Studio is incorrectly logged!!! The sql server is sql server 2005 SE with SP 2 applied!!!
In Management Studio modificiation, the databaselog table will produce 2 rows - one for the Tsql statement will list as : Create Table with a Tmp_ (attach to the table name) and the 2nd row will produce TSql statement as: Drop table table_name. Thus 2 events is recorded: Create table and Drop table for a modification to a table column!?!!!????
If I do exactly the samething through Query, Alter Table table_name (...modified some columns), the databaselog will capture exactly as is: The TSQL statement will display as: Alter Table... with the Event as: Alter Table
I believe this is a Bug that Microsoft need to fix. Anyone can you please confirm this and let me know how I can contact microsoft about this.
I have SQL server 2005 with SP2 Standard Edition.
February 4, 2008 at 2:01 pm
Script your SSMS changes to the table and you'll see it does something
completely different than you think.
Using SSMS it will not perform alter table alter column ...
but it will use a temp-table, move the data , drop the original table, set all relations , indexes, triggers, ...
All within one transaction.
So it is normal your ddl-trigger captures something different for the two.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 4, 2008 at 11:36 pm
Not a bug.
Management studio has the same behaviour as enterprise manager did. No matter what change you make to a table, both of them drop and recreate the table, rather than altering it.
That's why you can do things like add columns in the middle of the table with the management studio gui, that you can't do with Alter Table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply