bcp with WHERE statment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

bcp with WHERE statment

Author

Topic

jj6849
Starting Member

8 Posts

Posted - 2003-05-15 : 14:44:10

I can't get a where statement to work in my query that's in the bcp line. This is the first time I've used bcp, so I'm new at this. I've tried several different ways to get this to work. The problem is around the WHERE Center = 'KS' part of the query. I'll include all of them and the errors I'm getting in Query Analyzer. Any help would be greatly appreciated! --Josh
Try #1
DECLARE @query varchar(100)
set @query = 'SELECT * FROM ABC..vw_getLocate WHERE Center = "IL"'
EXECUTE master..xp_cmdshell 'bcp "@query" queryout REPLACE("f:\ftp\ABCftp\locate\KS"+CONVERT(char(8),GETDATE(),1)+".txt","/","")'

Error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@query'.

Try #2:
EXECUTE master..xp_cmdshell 'bcp "SELECT * FROM ABC..vw_getLocate WHERE Center = KS" queryout REPLACE("f:\ftp\ABCftp\locate\KS"+CONVERT(char(8),GETDATE(),1)+".txt","/","")'

Error (which makes sense):
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'KS'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 15:34:03

Have you tried running bcp at the command prompt instead of in Query Analyzer?

The problem with the first one is that what you are trying to do needs to be done through dynamic sql.

The problem with the second one is that you need quotes around KS. If you run this one through bcp at the command prompt, you should be able to figure this out easier.

Also, you might want to try bcp with an easier example, such as SELECT * FROM Northwind.dbo.<pickatable>

Tara

jj6849
Starting Member

8 Posts

Posted - 2003-05-15 : 15:46:09

I've never done anything at the command prompt. Do you think you could point me in the right direction on how to do it?

Also, on try #2, I put quotations around KS and it gives the error of "Invalid column name 'KS'....which doesn't make sense to me because I at least have quotations around it.

Thanks for the help-Josh

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 15:52:46

Go to start..run..and type cmd. Now you're at the command prompt. Type in your bcp command there. bcp is an executable. You will need to pass it some switches, such as -S, -U and -P or -E, just type in bcp /? at the command prompt and you'll see which switches are available.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 16:01:09

Or....

Why not create a view and bcp that out.

Brett

8-)

pmr
Starting Member

37 Posts

Posted - 2003-05-15 : 16:45:28

Here is what you need:

DECLARE @query varchar(100), @BcpCmd varChar(255)
set @query = 'SELECT * FROM ABC..vw_getLocate WHERE Center = ''IL''' 
set @BcpCmd = 'bcp ' + @query + ' queryout REPLACE("f:\ftp\ABCftp\locate\KS"+CONVERT(char(8),GETDATE(),1)+".txt","/","")'
EXECUTE master..xp_cmdshell @BcpCmd

The best way to understand why is to think of xp_cmdshell as a virtual machine within which components can't see other run-time
processes (your other SQL).

Peter

Edited by - pmr on 05/15/2003 16:46:17

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 16:46:55

But why use xp_cmdshell? Will this be done from a stored procedure? If not, then just create a batch file to do the work so that you don't have to give anyone permissions to xp_cmdshell, which is a VERY BAD (security being the reason) thing to do.

Tara

pmr
Starting Member

37 Posts

Posted - 2003-05-15 : 17:33:05

Tara,

You are right again. It can be run from within a stored procedure.

Josh didn't tell us the context of what he needed so finalizing an answer to which method to use is difficult at best. He did leave a couple of clues though:
His output directory path looks a lot like something an FTP site would serve files from.
He is a field called "Center" with values like "KS" and "IL" which are the state abbreviations for Illinois and Kansas.
Putting these together, this looks a lot like a "find a store location nearest you" web application. If this is approximately what is happening, then you would probably agree that performing all of the processing with the DB engine is best.

Again, we may never know.

Peter

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-16 : 14:43:33

Tara,