bcp problem

this command:

bcp MyDatabase..MyTable in Data\2005\MyFile.bcp.dat /f bcp.fmt /S MyServer /U sa

runs fine when run from its original location on a network drive.

When we copy the files referenced down to the C: drive and execute the command (with the path adjusted appropriately) and run the command I get an error:

Unable to open BCP host data-file

both MyFile.bcp.dat and bcp.fmt exist in the locations specified in the new command line. MyTable exists in MyDatabase. Etc. etc.

Why do we get this error? Are there other files secretly needed?

Thanks,

Jon

hi i 'm also facing the same problem could anybody reply if u have the solution...

i have tried bcp out command to a network drive which give the error

sql state = s1000

could not open host data file

please do help me.

jollyguyjolly@.hotmail.com

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

Batch query : NEED HELP!

Hi,

here's my situation...
first i will have to get the location from tblLocation...
there will be:
C:\
D:\
E:\

and then will have to search from these 3 locations one by one (is there a
loop or something like in VB??) starting with C:\ and then D:\ and then E:\.
I will have to do this as i search each drive:
I will have to match the filename that i've searched with another primary
key field of another table tblFiles...
i will match the filename and the primary key, if it match, then i will
update the availability field in tblFiles to 1, else to 0...

I just don't know how to run this all in one shot, can someone teach me
pls...what database are you running? If your running Oracle then you can probably use the stored procedures to do this. Or can probably use perl as well to do something like this.

Originally posted by jacky_cheecheo
Hi,

here's my situation...
first i will have to get the location from tblLocation...
there will be:
C:\
D:\
E:\

and then will have to search from these 3 locations one by one (is there a
loop or something like in VB??) starting with C:\ and then D:\ and then E:\.
I will have to do this as i search each drive:
I will have to match the filename that i've searched with another primary
key field of another table tblFiles...
i will match the filename and the primary key, if it match, then i will
update the availability field in tblFiles to 1, else to 0...

I just don't know how to run this all in one shot, can someone teach me
pls...|||Example of one query (QueryB) based on the results of another query (QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" & QueryA & ")"

But the following is even faster and allows for more than one field to be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not just the CustID field) for the records returned in QueryA (which was the customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build from the tblSQLA on the left to other tables that have fields you want to return.|||thank you all, i've got the solution...!|||wanna put up the solution ? =)

Originally posted by jacky_cheecheo
thank you all, i've got the solution...!