xp_cmdshell passing more than 8000 chars

Question

xp_cmdshell passing more than 8000 chars

petey20

SSCommitted

Points: 1741
More actions
August 1, 2006 at 10:40 am

#113911

I have an SP that basically builds a large string (using "max") I can actually gather more than 8000 characters in the varchar variable named @cmd
When I call xp_cmdshell
EXEC master..xp_cmdshell @cmd , NO_OUTPUT
I get an error stating:
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.
Basically saying there's no @cmd parameter b/c it contains more than 8000 chars. It seems that xp_cmdshell will only allow 8000 and no more. I'm not in a position to make 2 calls since I'm actually writing to a file with the cmd code and the 8000+ chars is the contents of the file.
Any ideas around this issue? Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Since you're writing out a file anyway, why not use DTS? You can call your DTS Package through DTSRUN, since you're already using XP command shell I figure this shouldn't be an issue for security. When you call you DTS package from youre sproc you can specifiy global variables and such to make it execute however you may need it to.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 2

To write files people actually use bcp.

_____________
Code for TallyGenerator

petey20 SSCommitted Points: 1741 More actions · Answer 3

Actually I'm using a vbscript to write the file. The SP is massive and does a lot of checking and data validation. If one thing is missing I don't want to write the file. I store the XML contents into a variable then pass those using a xp_cmdshell call to the vbscript to write the XML file on the fly.

Only in the case that I have more than 8000 characters in the xml doc does it choke. It is rare, but I'm generating on average 300 per day and normally end up with a couple over 8000 chars.

I can write this file using an DOS echo for each indvidual line but that seems like a lot of unnessecary load, especially when one of the required elements is missing from the data. I suppose I can populate a table column with the file contents and use bcp to generate the files. What other options do I have? I'm sure 2005 has some built in processes to generate xml files?

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Sergiy

SSC Guru

Points: 110208

August 2, 2006 at 6:03 am

#652780

EEEEW!

_____________
Code for TallyGenerator