Error when Creating a View

Question

Post reply

Error when Creating a View

ItzMe

Ten Centuries

Points: 1233
More actions
December 12, 2008 at 9:14 am

#195026

When Creating a View................i am facing the error as..............
" Msg 8152, Level 16, State 10, Procedure ddllogger, Line 7
String or binary data would be truncated.
The statement has been terminated. "
Here is the query if am using....
CREATE VIEW HumanResources.vEmployeesTime
AS
SELECT
hre.EmployeeID,
pc.LastName,
pc.FirstName,
hre.Title,
hre.VacationHours,
hre.SickLeaveHours
FROM
HumanResources.Employee hre
JOIN
Person.Contact pc ON hre.ContactID = pc.ContactID
WHERE
SalariedFlag = 1
AND
CurrentFlag = 1
Not sure where the problem is.....but when i double click the error it highlights "hre.Title".
Regards
ItzSam

Viewing 15 posts - 1 through 15 (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

mmm

Is that the only code that is on the screen when you hit F5 or run?

Because the error seeems to be in a procedure ddllogger and not in the view?

Thanks

Chris

----------------------------------------------
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

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

could you run this for me please :

sp_Helptext 'ddllogger'

----------------------------------------------
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

ItzMe Ten Centuries Points: 1233 More actions · Answer 3

Hello Chris

Its the only code i am executing...nothing else on the query window.

No procedure......nothing

Regards

ItzSam

Lowell SSC Guru Points: 323518 More actions · Answer 4

Good catch Christopher; i missed that when i read his error;

obviously he's got something in place for DDL auditing, and it's fixed size instead of varchar(max) for the data captured.

i show his command is 552 bytes; the destination table for his ddllogger audit is probably varchar(500)

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 5

OK Open a new query window

Run your create code again.

If you get the same error then in the same window run this only:

sp_HelpText 'ddllogger'

and let us know what it says?

----------------------------------------------
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

ItzMe Ten Centuries Points: 1233 More actions · Answer 6

i executed sp_Helptext 'ddllogger'......which basically gives the content of stored procedure.

But it says no stored procedure with name ddllogger in the Database.

Regards

ItzSam

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

HI Lowell

Are you saying that is has flicked a switch to turn on some built in DDL Logging?

Meaning anytime he creates stuff it writes to a table?

mmm very interesting how do turn this on? Or would this be a custom implimentation?

Thanks

Chris

----------------------------------------------
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

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

Hi ItzSam

Have tried sp_Help 'ddllogger' ?

----------------------------------------------
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

ItzMe Ten Centuries Points: 1233 More actions · Answer 9

Hi

executed sp_Help 'ddllogger'.

It says...."Msg 15009, Level 16, State 1, Procedure sp_help, Line 66

The object 'ddllogger' does not exist in database 'AdventureWorks' or is invalid for this operation."

Regards

ItzSam

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

Is Adventure works the DB that you tried to create your view on?

----------------------------------------------
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 11

Christopher Stobbs (12/12/2008)
HI Lowell
Are you saying that is has flicked a switch to turn on some built in DDL Logging?
Meaning anytime he creates stuff it writes to a table?
mmm very interesting how do turn this on? Or would this be a custom implimentation?
Thanks
Chris

yeah Chris;

I've got a suite of DDL audit triggers I play with as well; you know, logging every change create or change to a proc(and separately, every table/view and function)

here's my proc example and it's destination logging table:

[font="Courier New"]

CREATE TABLE [dbo].[DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[objectName] [sysname] NOT NULL,

[CommandText] [varchar](MAX) NOT NULL,

[EventType] [nvarchar](100) NULL

)

--

GO

CREATE TRIGGER [ReturnPREventData]

ON DATABASE

FOR

CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE

AS

DECLARE @eventData XML,

@uname NVARCHAR(50),

@oname NVARCHAR(100),

@otext VARCHAR(MAX),

@etype NVARCHAR(100),

@edate DATETIME

SET @eventData = eventdata()

SELECT

@edate=GETDATE(),

@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)'),

@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

IF @uname IS NOT NULL

BEGIN

INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES

(@edate,@uname,@oname,@otext,@etype)

END

GO

ENABLE TRIGGER [ReturnPREventData] ON DATABASE

GO[/font]

I'm absolutely sure he has something similar, but it is failing when inserting the EventData...this the error in trigger ddllogger

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 12

yes........AdventureWorks is the database i am working on.

I never got this error ever before....its quite strange.

Regards

ItzSam

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

Start in your current database with the following query and then move to the master database. You may find that there is a DDL trigger that is firing when you run the CREATE VIEW.

I have a DDL trigger on my SandBox database for testing purposes.

select * from sys.triggers

Lowell SSC Guru Points: 323518 More actions · Answer 14

also, i added my audit just now to a clean database,a nd even though i KNOW the table exists, when i try to sp_helptext the trigger on the database, it fails...

so how do you see a Database trigger? i'm googling now. I can find my logging table and sp_help it, but not the trigger that fills it so far.

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!