• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2594
  • Last Modified:

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)?
0
chrislindsay
Asked:
chrislindsay
  • 6
  • 2
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Small mistake..

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

http://msdn.microsoft.com/en-us/library/ms175046.aspx
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
chrislindsayAuthor Commented:
No you are right should have been an A
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Thanks for the confirmation..
I have clicked on Request Attention so that Moderators can do it on behalf of you..
0
 
chrislindsayAuthor Commented:
Can you suggest an alternative to the problem?
Thanks.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Thanks SouthMod and chrislindsay..
Merry Christmas and Advanced Happy New Year wishes..
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now