BCP with where clause - 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

General SQL Server Forums

New to SQL Server Programming

BCP with where clause

Author

Topic

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-05-09 : 07:12:57

i have the following stored procedure

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\armagh_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "select *,substring(stafflog,15,11) as test from Marketserv..Customers" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -S marketserv1 -U sa -P sas -c'

EXEC master..xp_cmdshell @bcpCommand

the above works fine however i need to include a where clause in the sql statment but i am getting and error. i want to add

where substring(stafflog,8,6) = 'armagh'

however i get an error saying its an invalid column. is it possible to do this in the bcp part and also can i add multiple queries in the bcp part

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-09 : 08:55:50

SET @bcpCommand = 'bcp "select *,substring(stafflog,15,11) as test from Marketserv..Customers where substring(stafflog,8,6) = ''armagh''" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -S marketserv1 -U sa -P sas -c'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-05-09 : 09:13:42

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)

SET @FileName = REPLACE('c:\armagh_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "select *,substring(stafflog,15,11) as test from Marketserv..Customers where substring(stafflog,8,6) = "armagh"" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -S marketserv1 -U sa -P sas -c'

EXEC master..xp_cmdshell @bcpCommand
I tried that and it still gives the error

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'armagh'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 09:16:22

It's not a double quote around armagh , but two consecutive single quotes.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-05-09 : 09:22:31

Thank you thank you thank you.

one more quick question can you do multiple sql queries within the BCP

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-05-09 : 09:35:45

basically this is the sql that runs to produce what i need

select *,substring(stafflog,15,11) as test into #t1 from dbo.Customers
where stafflog like '%armagh%'
go
select left(stafflog,4) as Staff,count(left(stafflog,4)) as Total from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
group by left(stafflog,4)
go

select Title,Address1,Address2,Town,County,Postcode,TelephoneDay,TelephoneWork,TelephoneEvening,
MobileTelephoneNo,Contact,Mail,Telephone,Terms,StaffLog
from #t1
where cast(left(test,charindex(' ', test))as smalldatetime) = cast(convert(varchar(8),getdate()-1,1) as datetime)
go

drop table #t1

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-09 : 09:58:48

Yes you can run multiple queries but it's a command line utility so separate them by a space rather than crlf and get rid of the "go"'s (and correct the sql).
It would be better if you could put it all in an SP.

You have two resultsets being returned which you won't be able to do with bcp.
You could use a derived table and a single query rather than a temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources