String concatentation in bcp

Question

Post reply

String concatentation in bcp

joe joe-152023

SSCommitted

Points: 1819
More actions
January 31, 2005 at 6:03 am

#87382

Hi there,
Please help me.
I'm messing up the string concatentation, in the set @bcp line. I have tried a lot of different things. I want one txt file for each area, and the txt file named by the area.
Create proc tester
@area_code int
as
declare @frm nvarchar(1000)
set @frm='(Select Area_code, years,Sex,Marital_Status
From Population)'
declare cc cursor
for select distinct Area_Code
from Population
declare @bcp as varchar (1000)
declare @i int
open cc
fetch next from cc into @i
while @@fetch_status=0
begin
set @bcp = 'bcp "' + @frm + '" queryout "'+ D:\Temp\ + @area_code + .txt + ' -c -SBMWEB01 -T'
exec tester @i
exec master..xp_cmdshell @bcp
fetch next from cc into @i
end
close cc
deallocate cc

Regrads Joejoe

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65144 More actions · Answer 1

Try using

set @frm='Select Area_code, years,Sex,Marital_Status From Population'

and

set @bcp = 'bcp "' + @frm +

' where Area_code = ' + CAST(@i as varchar) +

'" queryout "D:\Temp\' + CAST(@i as varchar) +

'.txt" -c -SBMWEB01 -T'

Far away is close at hand in the images of elsewhere.
Anon.

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 2

Thanks David

Now the contatention and the naming of the txt files is correct.

But I get a new error.

"Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'tester'. The stored procedure will still be created."

Does anyone have an explanation and perhaps a suggestion?

Regards joejoe

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

This is not really an error, more of an informational message. Your stored procedure was created. The message you refer to just means that the stored procedure sp_depends (which shows you dependencies among objects) will not have all of it's information correct. sp_depends is a notoriously inaccurate procedure, hence seldom used. For you history buffs, it's one of the more documented 'features' left over from the old Sybase base code.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

joe joe-152023 SSCommitted Points: 1819 More actions · Answer 4

hi rudy,

thanks

I'm an amature, don't really know enough about t-sql.

But I have learned the following.

1. run the create proc first

2. run the cusor part in QA thereafter

3. bcp does not allow stored procedure invocation with queryout. It only allows a query (i.e. select...) with queryout

So the correct bcp (which turned in to a osql) looks like this,

set @sql ='osql -Q"set fmtonly off; exec dbo.tester '+cast(@i as nvarchar)+'" -o"D:\Temp\' + cast(@i as varchar) + '.txt" -s ";" -n -w8000 -E -S"'+@@servername+'" -d"'+db_name()+'"'

And besides I only know this because of oj from RAC Team

Regards Joejoe