Problems using varchar(max) in Query

Posted on 2009-12-23
Last Modified: 2012-05-08
Hi Experts,

I am using the following sql query below :

declare @xml as xml

use [ops-20]
set @xml = (

select *
from dbo.test

for xml
path ('R'),root('D')

declare @cmd as varchar(max)
set @cmd =
'C:\tofile.vbs C:\data.xml "'
+ convert(varchar(MAX),@xml) + '" '
exec master..xp_cmdshell @cmd

where tofile.vbs is :

Dim ArgObj
Set ArgObj = WScript.Arguments
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set ObjFile = ObjFSO.CreateTextFile(ArgObj(0))

Where Varchar() is set to 8000, the code woks perfectly.  When Varchar() is set to MAX, the query errors with:
"Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'."

Is there a way of modifying the code to use Varchar(MAX)?
Question by:chrislindsay
    LVL 57

    Accepted Solution

    You are getting that error because master..xp_cmdshell requires/ supports @cmd input with size either Varchar(8000) or varchar(4000)..

    It will not allow more thatn 8000 characters..
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    Small mistake..

    Max value supported by xp_cmdshell as input was varchar(8000) or nvarchar(4000)..
    Reference to support the information above:
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Its the limit of the system / built-in stored procedure..
    No workarounds for xp_cmdshell

    But workaround for your problem:

    * Instead of passing the xml as varchar to your vbs file, save the xml in a file and pass that as an input to your vbs script..

    That should work..
    LVL 57

    Expert Comment

    by:Raja Jegan R
    And kindly let me know any specific reason why a B grade for this one since I pointed out the exact reason for the error..

    Author Comment

    No you are right should have been an A
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Thanks for the confirmation..
    I have clicked on Request Attention so that Moderators can do it on behalf of you..

    Author Closing Comment

    Can you suggest an alternative to the problem?
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Thanks SouthMod and chrislindsay..
    Merry Christmas and Advanced Happy New Year wishes..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now