Problem with BCP command

I am trying to execute following command using xp_cmdshell

EXEC master..xp_cmdshell 'bcp "select nc_value from fileade.dbo.nms_command (nolock) order by nc_pk desc" queryout \\filw\ExternalTools\NMS3\reports\NMS3_20070525_051507.txt -c -S"FILW2K" -Uabc -Pabc'

but the error message returned is

Copy direction must be either 'in' or 'out'.

Syntax Error in 'queryout'.

usage: bcp [[database_name.]owner.]table_name[lice_number] {in | out} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n] [-c] [-t field_terminator] [-r row_terminator]

[-U username] [-P password] [-I interfaces_file] [-S server]

[-a display_charset] [-q datafile_charset] [-z language] [-v]

[-A packet size] [-J client character set]

[-T text or image size] [-E] [-g id_start_value] [-N] [-X]

[-M LabelName LabelValue] [-labeled]

[-K keytab_file] [-R remote_server_principal]

[-V [security_options]] [-Z security_mechanism] [-Q]

NULL

Further the same command is running successfully in my DEV environment.

That sounds like you have a SQL 7 bcp.exe in your path. Run "bcp -v" on both machines and make sure the versions match.

|||Wrong forum. Moving to Transact-SQL.|||

When the path for the output file contains spaces or other 'non-acceptable' characters, you 'should' enclose it in double quotes.

The Server name does not need to be in double quotes.

|||

Hi Tom,

I have checked the version on all the three plateform

DEV : 8.00.382

PROD : 8.00.382

TestPROD : 8.00.382

and version are same.

|||

Hi Arnie,

The command is giving problem on Production environment only while in DEV & staging server working perfectly wheather to export file in local drive or in a network drive.

|||On the production server, does the SQL Agent account have permissions for the file locations?|||

Not Sure how to check this,

BUt we have checkred that other scheduled JOBS which also export some file from PROD DB server to other server are working OK, Further the BCP command is executed via windows service

Windows Service

Batch File

Stored Procedure

BCP Command.

|||Somewhere you have an old bcp.exe which is being picked up. The error message you posted above is from the SQL 7 bcp.exe program, not 8.00.382. The "queryout" option was added in SQL 2000 bcp.exe.

Search your hard drive for bcp.exe and remove anything not in C:\Program Files\Microsoft SQL Server\90 (or 80)\tools\binn.

|||

Hi Tom,

Your check point really help me to found the the problem although Production Hard Drive was not having the bcp.exe of version 7 instead the server was having Sybase BCP.EXE also , so whenever the window service try to execute the BCP command instead of picking up the SQL Server BCP path it was picking the Syabse BCP due to which the error was coming.

Thanks again for your help.

|||Good. I am glad you found it.

sql

problem with "select all" on 2 multi-value cascading parameters

Hello,

since some hours I'm struggling with 2 multi-value cascading parameters which default values should be always "Select ALL"

First Parameter:

available vlaues: From query

dataSetsGetCountry (defined as select from tblCountries)

ValueFied:ContryName

LabelField:CountryName

DefaultValues: from Query

dataSetsGetCountry

ValueFied:ContryName

SecondParameter:

available vlaues: From query

dataSetsGetAreas (stored procedure which parameter is the list of the selected countries)

ValueFied:AreaName

LabelField:AreaName

DefaultValues: from Query

dataSetsGetAreas

ValueFied:AreaName

First time I open the report

the first and the second parameters are properly filled and for both parameters "select all" is checked.

I select one option from the first parameter, the second parameter's content change dinamically and "Select All" option is still selected !Cool

Now I change the selection on the first Parameter, by checking AN ANOTHER item from the list , the second parameter list refresh dinamically but "Select all" IS NOT selected and only the item that were previously checked kept the selection !!!NO!!

Is this a bug in Reporting services and I have to say to ther user that is not possible to develop what they would like to have or there is , even programmatically, a way to solve it?

Thankx for any helps!

Marina B

Hello,

I notice that your second parameter is populated by a stored procedure. Is it possible for you to perform that query from within SRS rather than as a stored procedure?

I'm fairly new at this, so I hesitate to make an absolute statement, but multi-value parameters don't work well with stored procedures.

Also, the second query, the one accepting the first parameter must use an IN clause, rather than a WHERE clause, to filter the results.

I bow to any contradictory experience others may have.

Respectfully,

David Emigh

|||

Hi David,

I changed the definition of my parameters based on your advice

dataSet: dsGetCountry (defined as select from tblCountries)

ValueFied: ContryID

LabelField: CountryName

DefaultValues: from Query

dataSet: dsGetCountry

ValueFied: ContryName

Now the Second Parameter is setup in this ways

I created a new DataSet named: dsgetAreaByCountryID

select distinct(AreaName)
from [dbo].[tblArea] a
INNER JOIN [dbo].[tblcountry] c on a.CountryID = c.CountryID
where not AreaName is null and c.CountryID in (@.cc)
group by AreaName
order by AreaName asc

@.cc is a new Parameter which value is =Parameters!CountryID.Value

I tried to copy write directly c.CountryID in (Parameters!CountryID.Value)
but I get an error ( the ! is not welcome)

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

LabelField: AreaName

DefaultValues: from Query

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

Anyway I have the same problem as with the Stored Procedure!!

Did I follow exactly your advice?

Thank you very much
Marina B

|||

Have you adjusted the settings in the report parameters?

From the data or layout view:

Report menu > Parameters

|||

mmhhh ... your last reply is not completely clear to me.

what do you mean with that...

I have changed the parameter setting as I wrote in my last message...

Did I forgot something?

Thankx

Marina B.

|||I have had the same results. Don't think it's a bug. It looses track if you start going back and forth. If you want to start the parameter selection over you might be better off hitting refresh and selecting the parameters in the correct order. Be sure to explain the same to your end users. I do agree that it would be nice if it always keep track of them.|||

How is possible to hit the refresh?

Programmatically?

Thankx very much

MArina B.

problem with "select all" on 2 multi-value cascading parameters

Hello,

since some hours I'm struggling with 2 multi-value cascading parameters which default values should be always "Select ALL"

First Parameter:

available vlaues: From query

dataSetsGetCountry (defined as select from tblCountries)

ValueFied:ContryName

LabelField:CountryName

DefaultValues: from Query

dataSetsGetCountry

ValueFied:ContryName

SecondParameter:

available vlaues: From query

dataSetsGetAreas (stored procedure which parameter is the list of the selected countries)

ValueFied:AreaName

LabelField:AreaName

DefaultValues: from Query

dataSetsGetAreas

ValueFied:AreaName

First time I open the report

the first and the second parameters are properly filled and for both parameters "select all" is checked.

I select one option from the first parameter, the second parameter's content change dinamically and "Select All" option is still selected !Cool

Now I change the selection on the first Parameter, by checking AN ANOTHER item from the list , the second parameter list refresh dinamically but "Select all" IS NOT selected and only the item that were previously checked kept the selection !!!NO!!

Is this a bug in Reporting services and I have to say to ther user that is not possible to develop what they would like to have or there is , even programmatically, a way to solve it?

Thankx for any helps!

Marina B

Hello,

I notice that your second parameter is populated by a stored procedure. Is it possible for you to perform that query from within SRS rather than as a stored procedure?

I'm fairly new at this, so I hesitate to make an absolute statement, but multi-value parameters don't work well with stored procedures.

Also, the second query, the one accepting the first parameter must use an IN clause, rather than a WHERE clause, to filter the results.

I bow to any contradictory experience others may have.

Respectfully,

David Emigh

|||

Hi David,

I changed the definition of my parameters based on your advice

dataSet: dsGetCountry (defined as select from tblCountries)

ValueFied: ContryID

LabelField: CountryName

DefaultValues: from Query

dataSet: dsGetCountry

ValueFied: ContryName

Now the Second Parameter is setup in this ways

I created a new DataSet named: dsgetAreaByCountryID

select distinct(AreaName)
from [dbo].[tblArea] a
INNER JOIN [dbo].[tblcountry] c on a.CountryID = c.CountryID
where not AreaName is null and c.CountryID in (@.cc)
group by AreaName
order by AreaName asc

@.cc is a new Parameter which value is =Parameters!CountryID.Value

I tried to copy write directly c.CountryID in (Parameters!CountryID.Value)
but I get an error ( the ! is not welcome)

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

LabelField: AreaName

DefaultValues: from Query

dataSet: dsgetAreaByCountryID

ValueFied: AreaName

Anyway I have the same problem as with the Stored Procedure!!

Did I follow exactly your advice?

Thank you very much
Marina B

|||

Have you adjusted the settings in the report parameters?

From the data or layout view:

Report menu > Parameters

|||

mmhhh ... your last reply is not completely clear to me.

what do you mean with that...

I have changed the parameter setting as I wrote in my last message...

Did I forgot something?

Thankx

Marina B.

|||I have had the same results. Don't think it's a bug. It looses track if you start going back and forth. If you want to start the parameter selection over you might be better off hitting refresh and selecting the parameters in the correct order. Be sure to explain the same to your end users. I do agree that it would be nice if it always keep track of them.|||

How is possible to hit the refresh?

Programmatically?

Thankx very much

MArina B.

sql