I get this error message:
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
if I try to run
EXEC master..xp_cmdshell
'bcp TestDB.dbo.Employees out \\Ioana\Export\Employees.txt -c -t"," '
from Query Analyzer (or a store procedure).
If run the same command in cmd or run window (of course without EXEC and xp_cmdshell) I do not get any error and the content of the table is exported in the file I want...
If the exported file is on a local folder on the server everything is working fine even in Query Analyzer...
Thanking in AdvanceMy guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.|||Originally posted by MCrowley
My guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.
SQL Server is install on SQLSERVER machine and Ioana is my machine.
I want to run this script on SQLSERVER machine (in Query Analyzer) and to export the file in Export directory on my machine. The Export directory on my machine is shared with all the rights.
If run the bcp from cmd window on SQLSERVER machine is working fine. From Query Analyzer on the same machine (SQLSERVER) is not working even if I am logged in as system administrator with windows authentication. The administrator of SQLSERVER have full permision on my shared directory.
Thanks again
2012年3月6日星期二
2012年2月18日星期六
bcp easy for you, hard for me
I'm trying to use bcp to load a textfile of data into a db table. With my
real data, I always get "Unexpected EOF encountered in BCP data-file". So I
created a very simple test -- now I don't get the error, but I don't get any
results, either. Am I missing something obvious?
Here's the simple case:
1. Create a table
CREATE TABLE test (
foo int
);
2. Create a text file of data in xemacs, test.txt. Contents look like this:
1
2
3
4
5
3. Load it up
C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
Enter the file storage type of field foo [int-null]:
Enter prefix-length of field foo [1]:
Enter field terminator [none]:
Do you want to save this format information in a file? [Y/n] n
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
What happened? Why were no rows copied? I tried again, this time with field
terminator = \n. And again with \r\n. No change.
TIA!Try using the data formats listed in BOL
Data format bcp utility switch BULK INSERT clause
Native -n DATAFILETYPE = 'native'
Character -c DATAFILETYPE = 'char'
Unicode character -w DATAFILETYPE = 'widechar'
Unicode native -N DATAFILETYPE = 'widenative'
"Jesse" wrote:
> I'm trying to use bcp to load a textfile of data into a db table. With my
> real data, I always get "Unexpected EOF encountered in BCP data-file". So
I
> created a very simple test -- now I don't get the error, but I don't get a
ny
> results, either. Am I missing something obvious?
> Here's the simple case:
> 1. Create a table
> CREATE TABLE test (
> foo int
> );
> 2. Create a text file of data in xemacs, test.txt. Contents look like this
:
> 1
> 2
> 3
> 4
> 5
> 3. Load it up
> C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
> Enter the file storage type of field foo [int-null]:
> Enter prefix-length of field foo [1]:
> Enter field terminator [none]:
> Do you want to save this format information in a file? [Y/n] n
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
> What happened? Why were no rows copied? I tried again, this time with fiel
d
> terminator = \n. And again with \r\n. No change.
>
> TIA!
>
>
Try using the|||Jesse (nospam@.nospam.com) writes:
> C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
> Enter the file storage type of field foo [int-null]:
> Enter prefix-length of field foo [1]:
> Enter field terminator [none]:
> Do you want to save this format information in a file? [Y/n] n
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
> What happened? Why were no rows copied? I tried again, this time with
> field terminator = \n. And again with \r\n. No change.
I've never tried this interactive form of BCP, but int-null is a
binary type. I don't really know what you are suppsed to answer there.
Likewise, prefix-length should be 0; prefix formats is for binary
formats.
The normal way to use BCP is to specify a format on the command line,
or use a format file. Formats on the command line makes use of -c
to specify character format, and you can combine this with -t and -r
to specify field and row terminators. Or you can use -n to specify
native formats. There are also Unicode variations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
real data, I always get "Unexpected EOF encountered in BCP data-file". So I
created a very simple test -- now I don't get the error, but I don't get any
results, either. Am I missing something obvious?
Here's the simple case:
1. Create a table
CREATE TABLE test (
foo int
);
2. Create a text file of data in xemacs, test.txt. Contents look like this:
1
2
3
4
5
3. Load it up
C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
Enter the file storage type of field foo [int-null]:
Enter prefix-length of field foo [1]:
Enter field terminator [none]:
Do you want to save this format information in a file? [Y/n] n
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
What happened? Why were no rows copied? I tried again, this time with field
terminator = \n. And again with \r\n. No change.
TIA!Try using the data formats listed in BOL
Data format bcp utility switch BULK INSERT clause
Native -n DATAFILETYPE = 'native'
Character -c DATAFILETYPE = 'char'
Unicode character -w DATAFILETYPE = 'widechar'
Unicode native -N DATAFILETYPE = 'widenative'
"Jesse" wrote:
> I'm trying to use bcp to load a textfile of data into a db table. With my
> real data, I always get "Unexpected EOF encountered in BCP data-file". So
I
> created a very simple test -- now I don't get the error, but I don't get a
ny
> results, either. Am I missing something obvious?
> Here's the simple case:
> 1. Create a table
> CREATE TABLE test (
> foo int
> );
> 2. Create a text file of data in xemacs, test.txt. Contents look like this
:
> 1
> 2
> 3
> 4
> 5
> 3. Load it up
> C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
> Enter the file storage type of field foo [int-null]:
> Enter prefix-length of field foo [1]:
> Enter field terminator [none]:
> Do you want to save this format information in a file? [Y/n] n
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
> What happened? Why were no rows copied? I tried again, this time with fiel
d
> terminator = \n. And again with \r\n. No change.
>
> TIA!
>
>
Try using the|||Jesse (nospam@.nospam.com) writes:
> C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
> Enter the file storage type of field foo [int-null]:
> Enter prefix-length of field foo [1]:
> Enter field terminator [none]:
> Do you want to save this format information in a file? [Y/n] n
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
> What happened? Why were no rows copied? I tried again, this time with
> field terminator = \n. And again with \r\n. No change.
I've never tried this interactive form of BCP, but int-null is a
binary type. I don't really know what you are suppsed to answer there.
Likewise, prefix-length should be 0; prefix formats is for binary
formats.
The normal way to use BCP is to specify a format on the command line,
or use a format file. Formats on the command line makes use of -c
to specify character format, and you can combine this with -t and -r
to specify field and row terminators. Or you can use -n to specify
native formats. There are also Unicode variations.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
订阅:
博文 (Atom)