Error when Creating a View

Question

Error when Creating a View

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

How can you tell from looking is sys.triggers which triggers are DDL?

Will it be under the type_Desc?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You can find your Database Triggers under progammability in the object explorer.

ItzMe Ten Centuries Points: 1233 More actions · Answer 3

I fired the following query connected to AdventureWorks.....

Select * from sys.triggers

It displayed 'ddllogger' in the results.

When i fired the same query connected to Master

It returns nothing.

But whats the role of trigger 'ddllogger'?...and how does it impact the creation of view? I dont understand.

Regards

ItzSam

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok to see the definition, this works fine:

select object_definition(object_id) ,*

from sys.triggers

where parent_class=0

i see the type ="TR" And Type_desc = SQL_TRIGGER

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Christopher Stobbs (12/12/2008)
How can you tell from looking is sys.triggers which triggers are DDL?
Will it be under the type_Desc?

The column parent_class_desc will say DATABASE for a DDL trigger.

ItzMe Ten Centuries Points: 1233 More actions · Answer 6

I executed the following query....

"select object_definition(object_id) ,*

from sys.triggers

where parent_class=0"

and this is what it says about ddllogger.....

create trigger ddllogger on database for ddl_database_level_events as declare @xmldata xml set @xmldata = eventdata() insert ddlog values ( newid(), @xmldata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'), @xmldata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)'), @xmldata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)'), getdate()) ddllogger5161968890DATABASE0TRSQL_TRIGGER2008-12-04 12:59:36.4372008-12-04 12:59:36.4370000

Regards

ItzSam

Lowell SSC Guru Points: 323518 More actions · Answer 7

the issue is the audit table's definition;

can you

sp_help ddlog

one of the columns is too small for the desitnation data, causing the trigger to error and rollback, which prevents your view from being created.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

OK cool now I think you need to look at the columns in this table:

DDLog which is the table that this DDL trigger is inserting into 🙂

I must I am very glad you had this problem, cause I have just learn't so much over the last 30minutes it's amazing 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Lowell SSC Guru Points: 323518 More actions · Answer 9

I'm with Chris; I'm hitting refresh every 5 seconds to see what we find out next!

good learning day today!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 10

Most the teaching has been from you Lowell and Lynn 😉

Thanks

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

You can disable the database trigger with the following sql statement (change the name of the trigger, as this is the name of the DDL trigger in my version of AdventureWorks):

DISABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE

You can reenable with the following:

ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE

ItzMe Ten Centuries Points: 1233 More actions · Answer 12

Sorry for the delayed reply....was away for sometime

when i executer sp_help ddlog

this is part of what it has to say.....

DDLEventIDuniqueidentifierno16 yes(n/a)(n/a)NULL

DDLEventnvarcharno200 yes(n/a)(n/a)Latin1_General_CI_AS

TSQLnvarcharno200 yes(n/a)(n/a)Latin1_General_CI_AS

EventUsernvarcharno200 yes(n/a)(n/a)Latin1_General_CI_AS

EventDatadatetimeno8 yes(n/a)(n/a)NULL

Regards

ItzSam

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 13

Looks like the TSQL column is too small you either need to do as Lynn said and disable the triggers or simply alter the table and extend the length of the TSQL field in the log table 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Lowell SSC Guru Points: 323518 More actions · Answer 14

Christopher Stobbs (12/12/2008)
Looks like the TSQL column is too small you either need to do as Lynn said and disable the triggers or simply alter the table and extend the length of the TSQL field in the log table 🙂

yep chris is right...see the definition for the third column, "TSQL" is varchar(200), but the trigger is passing varchar(max):

[font="Courier New"]CREATE TRIGGER ddllogger

ON DATABASE

FOR ddl_database_level_events

AS

DECLARE @xmldata XML

SET @xmldata = eventdata()

INSERT ddlog VALUES

(

NEWID(),

@xmldata.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),

@xmldata.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)'),

@xmldata.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)'),

GETDATE()

) [/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ItzMe Ten Centuries Points: 1233 More actions · Answer 15

Yeah Chris....I agree with you.....

I cant disable the trigger as of now.....but can alter the table DDlog.

Even i learned a lot today from all you guys........really grateful to you.

Its just a simple concept of creation of view....but when you are not aware of the other objects that influence the creation, you start plucking your hair......:)

Thanks a Ton my friends.

Regards

ItzSam