Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

xp_cmdshell - Procedure expects parameter 'command_string' of type 'varchar' Expand / Collapse
Author
Message
Posted Tuesday, March 1, 2011 1:01 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 8, 2015 10:43 AM
Points: 71, Visits: 261
Here are the contents of @Cmd (varchar(max)):

bcp "SELECT [Data] FROM myserver..dbo.PEC_Citrus_RptData where [Data] is not null order by [Data]" queryout "C:\Documents and Settings\All Users\Documents\Shared\OutputFile.txt" -c -t -T -S "MYMACHINE\MYINSTANCE"

Here's the code:

EXEC master..xp_cmdshell @Cmd ;

Here is the error:
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.

?


*******************
What I lack in youth, I make up for in immaturity!
Post #1071530
Posted Tuesday, March 1, 2011 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:38 PM
Points: 13,962, Visits: 9,682
You can't use varchar(max) with xp_cmdshell. Found that out the hard way. Try a finite number (I usually just go with either 1000 or 8000).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1071610
Posted Wednesday, March 2, 2011 6:23 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 8, 2015 10:43 AM
Points: 71, Visits: 261
Well good grief, that was the problem! Kinda misleading error message! Thanks.

*******************
What I lack in youth, I make up for in immaturity!
Post #1071868
Posted Wednesday, March 2, 2011 6:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:38 PM
Points: 13,962, Visits: 9,682
You're welcome.

It's probably because they haven't rewritten xp_cmdshell since SQL 2000, and that didn't have the max option available.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1071875
Posted Friday, September 7, 2012 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 15, 2015 7:54 PM
Points: 4, Visits: 31
Thank Odin for the internet -- and GSquared for this post! You just saved me a lot of time.
Post #1356022
Posted Monday, September 10, 2012 4:33 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 20, 2015 6:48 AM
Points: 8, Visits: 83
I experienced this error, I now make sure I make nvarchar types with 4000 rather than using the max function.

Also just a note on BCP. Ensure it's always on one line, else it won't work, won't even print, very annoying!
Post #1356636
Posted Thursday, November 1, 2012 3:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 1, 2012 8:12 AM
Points: 1, Visits: 6
Hi,

I am having the same problem. My query string is greater than 8000 characters. I am using varchar(max).

Can someone assist urgently with a result, please.
Post #1379696
Posted Thursday, November 1, 2012 7:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 8, 2015 10:43 AM
Points: 71, Visits: 261
Maybe switch to a Powershell script.

*******************
What I lack in youth, I make up for in immaturity!
Post #1379800
Posted Thursday, November 1, 2012 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:31 PM
Points: 14,407, Visits: 37,678
riswana (11/1/2012)
Hi,

I am having the same problem. My query string is greater than 8000 characters. I am using varchar(max).

Can someone assist urgently with a result, please.


On computers running Microsoft Windows XP or later, the maximum length of the string that you can use at the command prompt is 8191 characters.
so you need to shorten up whatever is being passed as a query to your bcp.

use the query string to insert the data into a view, and use bcp to select from the view instead.

see this very similar post, where the Original poster needed to change his bcp query to select from a view(or global temp table):
http://www.sqlservercentral.com/Forums/FindPost1378950.aspx


Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1379802
Posted Thursday, November 1, 2012 7:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 40,752, Visits: 37,997
riswana (11/1/2012)
Hi,

I am having the same problem. My query string is greater than 8000 characters. I am using varchar(max).

Can someone assist urgently with a result, please.

It's easy. Write a stored procedure and use BCP to call the stored procedure.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1379808
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse