none
eventdata function

    Question

  • Anyone know where in BOL or another way to find the max size returned of the below parameters for the eventdata function?
      
     <EventType>type</EventType>
        <PostTime>date-time</PostTime>
        <SPID>spid</SPID>
        <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
    Monday, February 15, 2010 9:08 PM

Answers

  • Following is the info from BOL 2008:

    "EVENTDATA returns a value of type xml. By default, the schema definition for all events is installed in the following directory: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd."


    Let us know if helpful.


    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Monday, February 22, 2010 6:40 AM
    Saturday, February 20, 2010 9:05 PM
    Moderator

All replies

  • I'm not sure I understand your question... Are you trying to figure out what datatype to use for a .value() function to retrieve the information from the EVENTDATA XML?

    Why not just use NVARCHAR(MAX)?  Then you don't have to worry about it.

    --Brad (My Blog)
    Monday, February 15, 2010 9:20 PM
    Moderator
  • I guess theoretically I could do that.  I was assuming there was a maximum value less than nvarchar for any parameter returned.  For example, to my understanding 256 characters is the max size for a login.  Therefore, I was thinking the loginname parameter would only store this amount. 

    In addition to the above problem, I read on http://msdn.microsoft.com/en-us/library/ms173781%28SQL.90%29.aspx that "The schema returned for each event is comprised of elements nested inside an <EVENT_INSTANCE> element."  How do I find the elements for each event?  This web page shows me the events for the alter_table event.  Is this different for events such as create_view?  How can I find the properties for each ddl event?

    This web page says "
    to obtain the schema returned for an event, use Index or Search to locate the topic for the event in SQL Server Books Online", but I must be searching incorrectly.  I am unable to find anything except for alter_table.

    Thanks


    Monday, February 15, 2010 9:37 PM
  • Try googling instead. Few links

    http://msdn.microsoft.com/en-us/library/ms187909.aspx

    http://www.developer.com/db/article.php/3552096/Using-DDL-Triggers-to-Manage-SQL-Server-2005.htm
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, February 15, 2010 9:40 PM
    Moderator
  • Following is the info from BOL 2008:

    "EVENTDATA returns a value of type xml. By default, the schema definition for all events is installed in the following directory: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd."


    Let us know if helpful.


    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Monday, February 22, 2010 6:40 AM
    Saturday, February 20, 2010 9:05 PM
    Moderator