Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2107
  • Last Modified:

bcp queryout

I cannot seem to get a the correct version to bcp out.  I am using query but I keep getting syntax error need queryout.  

Thanks  for your help

bcp 'select * from upload where chart# in (select chart# from nomatch1)'  queryout 'd:\export.txt' -Usa -Pxxxxx -Sxxxk -c
0
running32
Asked:
running32
  • 6
  • 6
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
You *must* use double-quotes (") around the query, not single quotes (').
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
bcp "select * from upload where chart# in (select chart# from nomatch1)"  queryout 'd:\export.txt' -Usa -Pxxxxx -Sxxxk -c
0
 
running32Author Commented:
Even when I use "" I get syntax error near query out.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Scott PletcherSenior DBACommented:
Are you running this from a batch file or from (T)SQL?
0
 
running32Author Commented:
TSQL
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
then you have to use master..cmdShell
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
exec master..xp_cmdShell 'bcp "select * from upload where chart# in (select chart# from nomatch1)"  queryout ''d:\export.txt'' -Usa -Pxxxxx -Sxxxk -c'
0
 
running32Author Commented:
I get the error
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'upload'.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'nomatch1'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
use  urdbName..upload  and urdbName..nomatch1     instead of those two ; r
0
 
running32Author Commented:
these are both views not databases.  They are populated though?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
just put the prefix of your databasename followed by object owner name and Onject name
like this

DatabaseName.ObjectOwner.Object

or

DatabaseName..ObjectName
0
 
running32Author Commented:
Nearly there.  Now I get.  Thanks

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL
0
 
running32Author Commented:
exec master..xp_cmdShell 'bcp "select * from HealthDistrictTest..upload where chart# in (select chart# from HealthDistrictTest..nomatch1)"  queryout "c:\export.txt" -Usa -Pxxxxx -Sxxxxx -c'

got it thanks
0
 
Scott PletcherSenior DBACommented:
You're welcome.  Nice steal aneesh!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ScottPletcher,
I didn't C ur post at all..
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now