chrislindsay
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.Fi leSystemOb ject")
Set ObjFile = ObjFSO.CreateTextFile(ArgO bj(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)?
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.Fi
Set ObjFile = ObjFSO.CreateTextFile(ArgO
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..
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..
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..
I have clicked on Request Attention so that Moderators can do it on behalf of you..
ASKER
Can you suggest an alternative to the problem?
Thanks.
Thanks.
Thanks SouthMod and chrislindsay..
Merry Christmas and Advanced Happy New Year wishes..
Merry Christmas and Advanced Happy New Year wishes..
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..