October 9, 2009 at 12:22 am
I cannot get xp_cmdshell to execute this command. This one is driving me crazy!! I print the command out before I try to execute it, and it sure looks good to me. In fact, if I paste the command into a DOS shell it works fine. What am I missing!?
DECLARE @SqlCmd varchar(max)
SET @SqlCmd = 'select PropertyCode from DailyData.dbo.Properties'
SET @SqlCmd = 'bcp "' + @SqlCmd + '" queryout ' + '"c:\TestFile.txt" -t, -c -T'
PRINT @SqlCmd
EXEC master..xp_cmdshell @SqlCmd
The error is:
Procedure expects parameter 'command_string' of type 'varchar'.
.
October 9, 2009 at 12:31 am
Why don't you change from varchar(max) to varchar(8000).. I think that'll do the trick..
CEWII
October 9, 2009 at 12:37 am
You are right my friend. THANK YOU!
That makes NO sense to me though. I've got in the habit of using varchar(max) for building sql strings since I never know how long they'll end up being. Why does varchar(max) not work correctly? I thought max really was a synonym for 8000.
.
October 9, 2009 at 12:59 am
varchar(max) is more a synonym for TEXT, which is included for backward compatibility. I rarely build command strings up to 8000 characters, I usually use varchar(8000).. If you need a larger string use nvarchar(max) and use sp_executesql.. It should handle it.. I have a process that builds some commands 10-20,000 characters, it is a trigger generator to handle change detection on each and every field.. The command generated is the entire trigger definition to create it..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply