Link to home
Start Free TrialLog in
Avatar of chrislindsay
chrislindsayFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Problems using varchar(max) in Query

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))
ObjFile.WriteLine(ArgObj(1))
ObjFile.Close

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)?
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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..
Welcome..
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..
Avatar of chrislindsay

ASKER

No you are right should have been an A
Thanks for the confirmation..
I have clicked on Request Attention so that Moderators can do it on behalf of you..
Can you suggest an alternative to the problem?
Thanks.
Thanks SouthMod and chrislindsay..
Merry Christmas and Advanced Happy New Year wishes..