How to get the whole DDL command from EVENT_INSTANCE

Hello,
I try to save the current DDL in a table using the trigger on database ddl
events.
As usual,
DECLARE @.data XML
SET @.data = EVENTDATA()
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
How can I extract more then 2000 chars? Should I use a system table or
function to retrieve all the DDL command? I have SPs whith tons of chars...
Thanks,
CatalinHow about, for instance:
@.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
> Hello,
> I try to save the current DDL in a table using the trigger on database ddl
> events.
> As usual,
> DECLARE @.data XML
> SET @.data = EVENTDATA()
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
> How can I extract more then 2000 chars? Should I use a system table or
> function to retrieve all the DDL command? I have SPs whith tons of chars..
.
> Thanks,
> Catalin|||Thanks, but i have SPs with probably 40k chars or more... Neither varchar
(8000) is enough...
"Tibor Karaszi" wrote:

> How about, for instance:
> @.data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in messag
e
> news:46BD42B9-FA1B-4697-BD7D-2E54F52F4B93@.microsoft.com...
>|||Did you try nvarchar(max)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:989EED5F-D2F0-44EB-A43E-A2FB23BB9B5B@.microsoft.com...
> Thanks, but i have SPs with probably 40k chars or more... Neither varchar
> (8000) is enough...
> "Tibor Karaszi" wrote:
>|||Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'
t
tell me that it was also available on SQL 2000...)
I am so deceived about me... After 8 years of SQL I will have to start again
from ABC... Sometimes I am so busy to find complex solutions and I am not
able to see the simplest one.
Thanks again|||> Thank you, I had no ideea about (max) implementation on 2K5... (Please, don'ted">
> tell me that it was also available on SQL 2000...)
The max datatypes are indeed new to 2005. Consider them as replacements for
the less than user
friendly text, ntext and image datatypes.

> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
This happens to all of us. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Catalin NASTAC" <CatalinNASTAC@.discussions.microsoft.com> wrote in message
news:01DD8998-1C93-43E2-AA9A-F82694049866@.microsoft.com...
> Thank you, I had no ideea about (max) implementation on 2K5... (Please, do
n't
> tell me that it was also available on SQL 2000...)
> I am so deceived about me... After 8 years of SQL I will have to start aga
in
> from ABC... Sometimes I am so busy to find complex solutions and I am not
> able to see the simplest one.
> Thanks again