rbarryyoung (9/3/2008)
Here's what I get when I execute your script:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Msg 9421, Level 16, State 1, Line 5
XML parsing: line 149, character 30, illegal name character
Hm, I should have tested against more realistic data than simple SELECTs and PRINTs...
Using CDATA section around the nvarchar value seems to resolve the issue for up to 13 system stored procedures on my machine. If I use more, the output in the data grid is totally empty (I cannot click on the results anymore), while the text output still returns the cut-off text.
Besides that, encasing the procs in a CDATA section converts many special characters (eg. >) which would need to be replaced as well.
So overall my solution fails completely.
But here is something similar that might work out:
sqlcmd -S TheServerNameHere -o C:\Results.txt -Q "SET NOCOUNT ON;SET ROWCOUNT 200;DECLARE @a nvarchar(max);SELECT @a = ISNULL(@A,'') + ISNULL(OBJECT_DEFINITION(id), object_name(id) + ':N/A') + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sysobjects WHERE xtype = 'P';SELECT @a;" -y 0
The limit for output is 1 MB which is approximately reached with 200 system stored procedures on my machine.
If I set the ROWCOUNT to 0, SQLCMD seem to hang (maybe due to the excessive string handling? not sure).
EDIT:
Add SET NOCOUNT ON in script
Add GO after each PROC in script
Truncated quoted text
Best Regards,
Chris Büttner