xp_cmdshell - Procedure expects parameter 'command

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » xp_cmdshell - Procedure expects parameter...

xp_cmdshell - Procedure expects parameter 'command_string' of type 'varchar'

Rate Topic

Display Mode

Topic Options

Author

Message

brosspaxedi

Posted Tuesday, March 01, 2011 1:01 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 2:41 PM
Points: 67, Visits: 212

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

GSquared

Posted Tuesday, March 01, 2011 2:45 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

brosspaxedi

Posted Wednesday, March 02, 2011 6:23 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 2:41 PM
Points: 67, Visits: 212

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

GSquared

Posted Wednesday, March 02, 2011 6:36 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

haleya

Posted Friday, September 07, 2012 8:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 7:06 AM
Points: 1, Visits: 11

Thank Odin for the internet -- and GSquared for this post! You just saved me a lot of time.

Post #1356022

Nesuke

Posted Monday, September 10, 2012 4:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:31 AM
Points: 8, Visits: 26

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

riswana

Posted Thursday, November 01, 2012 3:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 01, 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

brosspaxedi

Posted Thursday, November 01, 2012 7:25 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 2:41 PM
Points: 67, Visits: 212

Maybe switch to a Powershell script.

*******************
What I lack in youth, I make up for in immaturity!

Post #1379800

Lowell

Posted Thursday, November 01, 2012 7:32 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:23 AM
Points: 11,638, Visits: 27,715

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1379802

Jeff Moden

Posted Thursday, November 01, 2012 7:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1379808

« Prev Topic | Next Topic »

Permissions