BCP error - 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

Transact-SQL (2000)

BCP error

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-17 : 18:37:08

Can you guys help me to fix the syntax errors that I am getting in BCP?

here is the errors that i am getting:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '+DETAIL+'.
Msg 103, Level 15, State 4, Line 3
The identifier that starts with '', coalesce(ltrim(rtrim(replace( convert(numeric(18,2),debit ), '.00', ''))),''), coalesce(ltrim(rtrim(replace( convert(numeric(' is too long. Maximum length is 128.
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@sql".

---Here the BCP command that Ia m tryingot run in QA

bcp "select 'Batch','','IC','PR','N' union all select '\"'+DETAIL+'\"', '\"'+coalesce(ltrim(rtrim(unit_abbr)),'') + '\"', '\"'+coalesce(ltrim(rtrim(transaction_gl_code)),'')+ '\"', coalesce(ltrim(rtrim(replace( convert(numeric(18,2),debit ), '.00', ''))),''), coalesce(ltrim(rtrim(replace( convert(numeric(18,2),credit ), '.00', ''))),'') from GL_Export where unit between 'A01' and 'A01'" queryout "c:\GL_Export\A01.txt" -c -STESTBOX -Usa -Psasa
--select @sql
exec master..xp_cmdshell @sql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 18:42:55

Put your query into a view then queryout that view.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-17 : 19:17:20

I don;t think i can't because the output is dynamically generated by the while loop( it needs to generate multiple files less than 20000 records per each file based onthe unit) .. I was trying to test the one of the outputs to see if the BCP command is working..but it seems it's not.. any idea?

select unit_abbr, num = count(*)
into #a
from GL_Export
group by unit_abbr
order by unit_abbr
create nonclustered index ix_A on #a(unit_abbr)

declare @sql varchar(8000)
declare @unit varchar(10), @lastunit varchar(10), @num int, @nextunit varchar(10)
select @lastunit = ''
while @lastunit < (select max(unit_abbr) from #a)
begin
select @unit = min(unit_abbr) from #a where unit_abbr > @lastunit
select @num = num from #a where unit_abbr = @unit
select @nextunit = @unit, @lastunit = @unit
while @num < 2000
begin
select @lastunit = @nextunit
select @nextunit = unit_abbr, @num = @num + num from (select top 1 * from #a where unit_abbr > @nextunit order by unit_abbr) a

end

bcp "select 'Batch','','IC','PR','N' union all select '\"'+DETAIL+'\"', '\"'+coalesce(ltrim(rtrim(unit_abbr)),'') + '\"', '\"'+coalesce(ltrim(rtrim(transaction_gl_code)),'')+ '\"', coalesce(ltrim(rtrim(replace( convert(numeric(18,2),debit ), '.00', ''))),''), coalesce(ltrim(rtrim(replace( convert(numeric(18,2),credit ), '.00', ''))),'') from GL_Export where unit between @unit and @lastunit" queryout "c:\GL_Export\@unit.txt" -c -STESTBOX -Usa -Psasa
--select @sql
exec master..xp_cmdshell @sql
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-17 : 19:24:45

First, it should say SET @sql = 'bcp ... You have to build the bcp string, which isn't being done in the query that you posted.

Second, I'm not sure that bcp can handle this type of query.

Tara Kizer

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-18 : 01:30:47

then how can get the result that I want without using BCP?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources