Run BCP on the client QA - 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)

Run BCP on the client QA

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-18 : 11:31:09

I was able to run the below BCP on the server without any problems,but when I try to run it on the client computer ( in QA ),
,i am getting the below error

Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

Any idea why I am getting this error?

---BCP
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 <= 20000 and @nextunit is not null
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

if @nextunit = @lastunit set @nextunit = null
end

select

@sql = '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 Finance_Apps.dbo.GL_Export where unit_abbr between ''' + RTRIM( @unit) + ''' and ''' + RTRIM(@lastunit) + '''" queryout "c:\GL_Export\' + RTRIM(@unit) + '.txt" -c -t "," -Sraiint -UTest -Pghfu789
exec

master..xp_cmdshell @sql
end

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-18 : 11:32:54

No folder c:\GL_Export\ on the server?

==========================================
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.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-18 : 11:50:46

That's it.. I thought the outputs files are going to go in the c:\GL_Export\ folder on the client computer..

Thank you very much..

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-18 : 11:59:37

xp_cmdshell will run the command on the server.
If you want it on the client then run the bcp command in a dos window or create a bat file.

==========================================
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.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-18 : 14:12:15

"create a bat file."

How can I create a batch file, so i can run it on the client? can you show me some examples?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources