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

SQL Server Administration (2000)

Bcp Error

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-02-05 : 11:38:22

hi guys

i am writing the query results to txt file. works fine on my local system when deployed the same SP on server i get error

output
----------------------------------------------------------------------
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL

the command used is

bcp "select * from Server..Table1 ,queryout "D:\Data\test.txt" -w -T -x

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-05 : 11:39:47

Your bcp command is not correct, but I suspect what you posted is not what you are actually running since that won't even compile. Please show us the actual command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2009-02-10 : 03:50:35

basically i was generating dynamic sql. and then execute it. it works fine for me.. i fiqure out the issue is it was writting the data to remote drive which it does not have permission. gave permission and issue resolved.

i have a new question

i need to execute the bcp command if the qry return any rows.

declare @TSQL varchar(max)
declare @SQL varchar(max)
declare @rowcnt int

set @sql='select * from table1 where ' + condition

set @TSQL ="select @rowcnt =count(sysid) from ( + @sql + )

exec(@TSql)

if (@rowcnt>0 )
begin
exec(@sql)
..do some thing..
end

also how can i avoid dynamic SQL

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 04:21:07

Do you have a D: drive on your server?
All paths run on server are relative to server position.

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-10 : 05:16:28

You'll need to store the count in a variable,, something like



    DECLARE @i INT, @sql NVARCHAR(512) 
 
    SET @sql = N'SELECT @i = COUNT(*) FROM table1 ' 
   
 
    EXEC sp_executesql 
        @query = @sql, 
        @params = N'@i INT OUTPUT', 
        @i = @i OUTPUT 
 
    IF( @i =0)....
        --ur bcp command
	else
	.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources