BCP: Unable to open host data file

We need to run the BCP command in the SQL server but the .txt file from which
it should be imported or exported is in the client machine (the folder is
shared).
exec XP_cmdshell 'BCP [table name] in "[file path- in client machine]" -U
[login id] -P [password]-S [Server Name]-c -t\t -r\n '
Throws an error.
"Unable to open BCP Host data file.".Geeta
Permissions?
"Geeta" <Geeta@.discussions.microsoft.com> wrote in message
news:6D15F4FD-3BC6-47D9-859E-45C7104A634A@.microsoft.com...
> We need to run the BCP command in the SQL server but the .txt file from
which
> it should be imported or exported is in the client machine (the folder is
> shared).
> exec XP_cmdshell 'BCP [table name] in "[file path- in client machine]" -U
> [login id] -P [password]-S [Server Name]-c -t\t -r\n '
> Throws an error.
> "Unable to open BCP Host data file.".
>
>|||SQL server login is sa .
the system login has full permission on the shared folder.
"Uri Dimant" wrote:
> Geeta
> Permissions?
> "Geeta" <Geeta@.discussions.microsoft.com> wrote in message
> news:6D15F4FD-3BC6-47D9-859E-45C7104A634A@.microsoft.com...
> > We need to run the BCP command in the SQL server but the .txt file from
> which
> > it should be imported or exported is in the client machine (the folder is
> > shared).
> >
> > exec XP_cmdshell 'BCP [table name] in "[file path- in client machine]" -U
> > [login id] -P [password]-S [Server Name]-c -t\t -r\n '
> >
> > Throws an error.
> > "Unable to open BCP Host data file.".
> >
> >
> >
> >
>
>|||Hi
> > > exec XP_cmdshell 'BCP [table name] in "[file path- in client
machine]" -U
> > > [login id] -P [password]-S [Server Name]-c -t\t -r\n '
DataFile.txt:
"Data1","Data22","Data333"
"Data1","Data22","Data333"
"Data1","Data22","Data333"
FormatFile.fmt:
7.0
4
1 SQLCHAR 0 1 "\"" 0 Quote1
2 SQLCHAR 0 10 "\",\"" 1 Column1
3 SQLCHAR 0 10 "\",\"" 2 Column2
4 SQLCHAR 0 10 "\"\r\n" 3 Column3
USE tempdb
GO
CREATE TABLE TestTable(
Column1 varchar(10) NOT NULL,
Column2 varchar(10) NOT NULL,
Column3 varchar(10) NOT NULL
)
GO
BCP tempdb..TestTable in c:\temp\DataFile.txt /T /fc:\temp\FormatFile.fmt
"Geeta" <Geeta@.discussions.microsoft.com> wrote in message
news:323D8765-7636-49C3-87BE-A43746F82C7F@.microsoft.com...
> SQL server login is sa .
> the system login has full permission on the shared folder.
> "Uri Dimant" wrote:
> > Geeta
> > Permissions?
> > "Geeta" <Geeta@.discussions.microsoft.com> wrote in message
> > news:6D15F4FD-3BC6-47D9-859E-45C7104A634A@.microsoft.com...
> > > We need to run the BCP command in the SQL server but the .txt file
from
> > which
> > > it should be imported or exported is in the client machine (the folder
is
> > > shared).
> > >
> > > exec XP_cmdshell 'BCP [table name] in "[file path- in client
machine]" -U
> > > [login id] -P [password]-S [Server Name]-c -t\t -r\n '
> > >
> > > Throws an error.
> > > "Unable to open BCP Host data file.".
> > >
> > >
> > >
> > >
> >
> >
> >

BCP/BULK INSERT

Hi everyone,
I have to load data from a .txt file into a database table, I've decided to
use the BULK INSERT command because of the speed it has. At the first phase,
all data from the text file inserted to a temporaly table, which has only
varchar(x) fields, the second phase will process the data.
I have problems with the first phase, some records of the text file are not
well-formed, some fields are missing in several rows (this by design,
unfortunatly).
If the last field is missing, it will be null, as I excepted it.
But, if the the last two (or more) fields are missing, the it seems the
whole line shifted, and BCP starts to read the next row. And, of course, it
produces an error ("String or binary data would be truncated"). If I turn off
the ANSI_WARNINGS, I'll have a lot of false data - because of the "shift".
I'm using format files, all the fields are SQLCHAR by default, all of them
has a correct field length.
You could reproduce the error of course, with the following test script:
if exists(select 1 from sysobjects where name='table1')
begin
drop table table1;
end;
create table table1(
field1 varchar(2),
field2 varchar(2),
field3 varchar(2)
);
go
truncate table table1;
bulk insert table1 from 'table1.txt' with (formatfile='table1.fmt');
select * from table1;
go
The format file:
8.0
3
1 SQLCHAR 0 2 "" 1 field1
Hungarian_CI_AS
2 SQLCHAR 0 2 "" 2 field2
Hungarian_CI_AS
3 SQLCHAR 0 2 "\r\n" 3 field3
Hungarian_CI_AS
The data file:
01AAaa
02BBbb
03CCcc
04DD
05EE
06
07
08HH
09IIii
Has anyone a help or suggestion to resolve this problem? I do not want to
hardcode this process .
Thanks,
Tamas Beri
Hi
Modify it for your needs
1)
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=D:\myfolder;','select * from data1.txt')
--Text file structure
col1
01AAaa
02BBbb
03CCcc
04DD
05EE
06
07
08HH
09IIii
2)
CREATE TABLE [tt] (
[ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
[field1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[field2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
BULK INSERT tt
FROM 'd:\dat1.txt'
WITH
(
FIRSTROW = 3,
FORMATFILE = 'd:\fmt1.fmt'
)
select * from tt
--Text file structure
field1,field2
01,AAaa
02,BBbb
03,CCcc
04,DD
05,EE
06,
07,
08,HH
09,IIii
--fmt file structure
8.0
2
1 SQLCHAR 0 100 "," 2 field1
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "\r\n" 3 field2
SQL_Latin1_General_CP1_CI_AS
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:2E818698-6ABB-4820-BB41-57BA89477D11@.microsoft.com...
> Hi everyone,
> I have to load data from a .txt file into a database table, I've decided
to
> use the BULK INSERT command because of the speed it has. At the first
phase,
> all data from the text file inserted to a temporaly table, which has only
> varchar(x) fields, the second phase will process the data.
> I have problems with the first phase, some records of the text file are
not
> well-formed, some fields are missing in several rows (this by design,
> unfortunatly).
> If the last field is missing, it will be null, as I excepted it.
> But, if the the last two (or more) fields are missing, the it seems the
> whole line shifted, and BCP starts to read the next row. And, of course,
it
> produces an error ("String or binary data would be truncated"). If I turn
off
> the ANSI_WARNINGS, I'll have a lot of false data - because of the "shift".
> I'm using format files, all the fields are SQLCHAR by default, all of
them
> has a correct field length.
> You could reproduce the error of course, with the following test script:
> if exists(select 1 from sysobjects where name='table1')
> begin
> drop table table1;
> end;
> create table table1(
> field1 varchar(2),
> field2 varchar(2),
> field3 varchar(2)
> );
> go
> truncate table table1;
> bulk insert table1 from 'table1.txt' with (formatfile='table1.fmt');
> select * from table1;
> go
> The format file:
> 8.0
> 3
> 1 SQLCHAR 0 2 "" 1
field1
> Hungarian_CI_AS
> 2 SQLCHAR 0 2 "" 2
field2
> Hungarian_CI_AS
> 3 SQLCHAR 0 2 "\r\n" 3
field3
> Hungarian_CI_AS
> The data file:
> 01AAaa
> 02BBbb
> 03CCcc
> 04DD
> 05EE
> 06
> 07
> 08HH
> 09IIii
> Has anyone a help or suggestion to resolve this problem? I do not want to
> hardcode this process .
> Thanks,
> Tamas Beri
>
|||Thanks,
finally I've decided to read the data in two steps, at first in a temp
table which has only one row, and then the second phase is an insert into
select from with a massive using of substring, cast and case .
Another strange thing, I've tried to create a procedure:
create procedure some_procedure(@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
go
And the creation fails, it says, "Incorrect syntax near '@.filename'.".
?
It is possible to pass the bulk insert command a variable?
Regards,
Tamas Beri
"Uri Dimant" wrote:

> Hi
> Modify it for your needs
> 1)
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=D:\myfolder;','select * from data1.txt')
> --Text file structure
> col1
> 01AAaa
> 02BBbb
> 03CCcc
> 04DD
> 05EE
> 06
> 07
> 08HH
> 09IIii
> 2)
> CREATE TABLE [tt] (
> [ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
> [field1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [field2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
> GO
>
> BULK INSERT tt
> FROM 'd:\dat1.txt'
> WITH
> (
> FIRSTROW = 3,
> FORMATFILE = 'd:\fmt1.fmt'
> )
> select * from tt
> --Text file structure
> field1,field2
> 01,AAaa
> 02,BBbb
> 03,CCcc
> 04,DD
> 05,EE
> 06,
> 07,
> 08,HH
> 09,IIii
> --fmt file structure
> 8.0
> 2
> 1 SQLCHAR 0 100 "," 2 field1
> SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 10 "\r\n" 3 field2
> SQL_Latin1_General_CP1_CI_AS

BCP/BULK INSERT

Hi everyone,
I have to load data from a .txt file into a database table, I've decided to
use the BULK INSERT command because of the speed it has. At the first phase,
all data from the text file inserted to a temporaly table, which has only
varchar(x) fields, the second phase will process the data.
I have problems with the first phase, some records of the text file are not
well-formed, some fields are missing in several rows (this by design,
unfortunatly).
If the last field is missing, it will be null, as I excepted it.
But, if the the last two (or more) fields are missing, the it seems the
whole line shifted, and BCP starts to read the next row. And, of course, it
produces an error ("String or binary data would be truncated"). If I turn off
the ANSI_WARNINGS, I'll have a lot of false data - because of the "shift".
I'm using format files, all the fields are SQLCHAR by default, all of them
has a correct field length.
You could reproduce the error of course, with the following test script:
if exists(select 1 from sysobjects where name='table1')
begin
drop table table1;
end;
create table table1(
field1 varchar(2),
field2 varchar(2),
field3 varchar(2)
);
go
truncate table table1;
bulk insert table1 from 'table1.txt' with (formatfile='table1.fmt');
select * from table1;
go
The format file:
8.0
3
1 SQLCHAR 0 2 "" 1 field1
Hungarian_CI_AS
2 SQLCHAR 0 2 "" 2 field2
Hungarian_CI_AS
3 SQLCHAR 0 2 "\r\n" 3 field3
Hungarian_CI_AS
The data file:
01AAaa
02BBbb
03CCcc
04DD
05EE
06
07
08HH
09IIii
Has anyone a help or suggestion to resolve this problem? I do not want to
hardcode this process :).
Thanks,
Tamas BeriHi
Modify it for your needs
1)
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=D:\myfolder;','select * from data1.txt')
--Text file structure
col1
01AAaa
02BBbb
03CCcc
04DD
05EE
06
07
08HH
09IIii
2)
CREATE TABLE [tt] (
[ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
[field1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[field2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
BULK INSERT tt
FROM 'd:\dat1.txt'
WITH
(
FIRSTROW = 3,
FORMATFILE = 'd:\fmt1.fmt'
)
select * from tt
--Text file structure
field1,field2
01,AAaa
02,BBbb
03,CCcc
04,DD
05,EE
06,
07,
08,HH
09,IIii
--fmt file structure
8.0
2
1 SQLCHAR 0 100 "," 2 field1
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "\r\n" 3 field2
SQL_Latin1_General_CP1_CI_AS
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:2E818698-6ABB-4820-BB41-57BA89477D11@.microsoft.com...
> Hi everyone,
> I have to load data from a .txt file into a database table, I've decided
to
> use the BULK INSERT command because of the speed it has. At the first
phase,
> all data from the text file inserted to a temporaly table, which has only
> varchar(x) fields, the second phase will process the data.
> I have problems with the first phase, some records of the text file are
not
> well-formed, some fields are missing in several rows (this by design,
> unfortunatly).
> If the last field is missing, it will be null, as I excepted it.
> But, if the the last two (or more) fields are missing, the it seems the
> whole line shifted, and BCP starts to read the next row. And, of course,
it
> produces an error ("String or binary data would be truncated"). If I turn
off
> the ANSI_WARNINGS, I'll have a lot of false data - because of the "shift".
> I'm using format files, all the fields are SQLCHAR by default, all of
them
> has a correct field length.
> You could reproduce the error of course, with the following test script:
> if exists(select 1 from sysobjects where name='table1')
> begin
> drop table table1;
> end;
> create table table1(
> field1 varchar(2),
> field2 varchar(2),
> field3 varchar(2)
> );
> go
> truncate table table1;
> bulk insert table1 from 'table1.txt' with (formatfile='table1.fmt');
> select * from table1;
> go
> The format file:
> 8.0
> 3
> 1 SQLCHAR 0 2 "" 1
field1
> Hungarian_CI_AS
> 2 SQLCHAR 0 2 "" 2
field2
> Hungarian_CI_AS
> 3 SQLCHAR 0 2 "\r\n" 3
field3
> Hungarian_CI_AS
> The data file:
> 01AAaa
> 02BBbb
> 03CCcc
> 04DD
> 05EE
> 06
> 07
> 08HH
> 09IIii
> Has anyone a help or suggestion to resolve this problem? I do not want to
> hardcode this process :).
> Thanks,
> Tamas Beri
>|||Thanks,
finally I've decided to read the data in two steps, at first in a temp
table which has only one row, and then the second phase is an insert into
select from with a massive using of substring, cast and case :).
Another strange thing, I've tried to create a procedure:
create procedure some_procedure(@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
go
And the creation fails, it says, "Incorrect syntax near '@.filename'.".
?
It is possible to pass the bulk insert command a variable?
Regards,
Tamas Beri
"Uri Dimant" wrote:
> Hi
> Modify it for your needs
> 1)
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=D:\myfolder;','select * from data1.txt')
> --Text file structure
> col1
> 01AAaa
> 02BBbb
> 03CCcc
> 04DD
> 05EE
> 06
> 07
> 08HH
> 09IIii
> 2)
> CREATE TABLE [tt] (
> [ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
> [field1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [field2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
> GO
>
> BULK INSERT tt
> FROM 'd:\dat1.txt'
> WITH
> (
> FIRSTROW = 3,
> FORMATFILE = 'd:\fmt1.fmt'
> )
> select * from tt
> --Text file structure
> field1,field2
> 01,AAaa
> 02,BBbb
> 03,CCcc
> 04,DD
> 05,EE
> 06,
> 07,
> 08,HH
> 09,IIii
> --fmt file structure
> 8.0
> 2
> 1 SQLCHAR 0 100 "," 2 field1
> SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 10 "\r\n" 3 field2
> SQL_Latin1_General_CP1_CI_AS

BCP/BULK INSERT

Hi everyone,
I have to load data from a .txt file into a database table, I've decided to
use the BULK INSERT command because of the speed it has. At the first phase,
all data from the text file inserted to a temporaly table, which has only
varchar(x) fields, the second phase will process the data.
I have problems with the first phase, some records of the text file are not
well-formed, some fields are missing in several rows (this by design,
unfortunatly).
If the last field is missing, it will be null, as I excepted it.
But, if the the last two (or more) fields are missing, the it seems the
whole line shifted, and BCP starts to read the next row. And, of course, it
produces an error ("String or binary data would be truncated"). If I turn of
f
the ANSI_WARNINGS, I'll have a lot of false data - because of the "shift".
I'm using format files, all the fields are SQLCHAR by default, all of them
has a correct field length.
You could reproduce the error of course, with the following test script:
if exists(select 1 from sysobjects where name='table1')
begin
drop table table1;
end;
create table table1(
field1 varchar(2),
field2 varchar(2),
field3 varchar(2)
);
go
truncate table table1;
bulk insert table1 from 'table1.txt' with (formatfile='table1.fmt');
select * from table1;
go
The format file:
8.0
3
1 SQLCHAR 0 2 "" 1 field1
Hungarian_CI_AS
2 SQLCHAR 0 2 "" 2 field2
Hungarian_CI_AS
3 SQLCHAR 0 2 "\r\n" 3 field3
Hungarian_CI_AS
The data file:
01AAaa
02BBbb
03CCcc
04DD
05EE
06
07
08HH
09IIii
Has anyone a help or suggestion to resolve this problem? I do not want to
hardcode this process .
Thanks,
Tamas BeriHi
Modify it for your needs
1)
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.t
xt;
*.csv)};
DefaultDir=D:\myfolder;','select * from data1.txt')
--Text file structure
col1
01AAaa
02BBbb
03CCcc
04DD
05EE
06
07
08HH
09IIii
2)
CREATE TABLE [tt] (
[ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
[field1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL
,
[field2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
BULK INSERT tt
FROM 'd:\dat1.txt'
WITH
(
FIRSTROW = 3,
FORMATFILE = 'd:\fmt1.fmt'
)
select * from tt
--Text file structure
field1,field2
01,AAaa
02,BBbb
03,CCcc
04,DD
05,EE
06,
07,
08,HH
09,IIii
--fmt file structure
8.0
2
1 SQLCHAR 0 100 "," 2 field1
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 10 "\r\n" 3 field2
SQL_Latin1_General_CP1_CI_AS
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:2E818698-6ABB-4820-BB41-57BA89477D11@.microsoft.com...
> Hi everyone,
> I have to load data from a .txt file into a database table, I've decided
to
> use the BULK INSERT command because of the speed it has. At the first
phase,
> all data from the text file inserted to a temporaly table, which has only
> varchar(x) fields, the second phase will process the data.
> I have problems with the first phase, some records of the text file are
not
> well-formed, some fields are missing in several rows (this by design,
> unfortunatly).
> If the last field is missing, it will be null, as I excepted it.
> But, if the the last two (or more) fields are missing, the it seems the
> whole line shifted, and BCP starts to read the next row. And, of course,
it
> produces an error ("String or binary data would be truncated"). If I turn
off
> the ANSI_WARNINGS, I'll have a lot of false data - because of the "shift".
> I'm using format files, all the fields are SQLCHAR by default, all of
them
> has a correct field length.
> You could reproduce the error of course, with the following test script:
> if exists(select 1 from sysobjects where name='table1')
> begin
> drop table table1;
> end;
> create table table1(
> field1 varchar(2),
> field2 varchar(2),
> field3 varchar(2)
> );
> go
> truncate table table1;
> bulk insert table1 from 'table1.txt' with (formatfile='table1.fmt');
> select * from table1;
> go
> The format file:
> 8.0
> 3
> 1 SQLCHAR 0 2 "" 1
field1
> Hungarian_CI_AS
> 2 SQLCHAR 0 2 "" 2
field2
> Hungarian_CI_AS
> 3 SQLCHAR 0 2 "\r\n" 3
field3
> Hungarian_CI_AS
> The data file:
> 01AAaa
> 02BBbb
> 03CCcc
> 04DD
> 05EE
> 06
> 07
> 08HH
> 09IIii
> Has anyone a help or suggestion to resolve this problem? I do not want to
> hardcode this process .
> Thanks,
> Tamas Beri
>|||Thanks,
finally I've decided to read the data in two steps, at first in a temp
table which has only one row, and then the second phase is an insert into
select from with a massive using of substring, cast and case .
Another strange thing, I've tried to create a procedure:
create procedure some_procedure(@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
go
And the creation fails, it says, "Incorrect syntax near '@.filename'.".
?
It is possible to pass the bulk insert command a variable?
Regards,
Tamas Beri
"Uri Dimant" wrote:

> Hi
> Modify it for your needs
> 1)
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver
(*.txt;
> *.csv)};
> DefaultDir=D:\myfolder;','select * from data1.txt')
> --Text file structure
> col1
> 01AAaa
> 02BBbb
> 03CCcc
> 04DD
> 05EE
> 06
> 07
> 08HH
> 09IIii
> 2)
> CREATE TABLE [tt] (
> [ModuleID] [int] IDENTITY (1, 1) NOT NULL ,
> [field1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [field2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ) ON [PRIMARY]
> GO
>
> BULK INSERT tt
> FROM 'd:\dat1.txt'
> WITH
> (
> FIRSTROW = 3,
> FORMATFILE = 'd:\fmt1.fmt'
> )
> select * from tt
> --Text file structure
> field1,field2
> 01,AAaa
> 02,BBbb
> 03,CCcc
> 04,DD
> 05,EE
> 06,
> 07,
> 08,HH
> 09,IIii
> --fmt file structure
> 8.0
> 2
> 1 SQLCHAR 0 100 "," 2 field1
> SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 10 "\r\n" 3 field2
> SQL_Latin1_General_CP1_CI_ASsql

bcp won't run

I'm having trouble running bcp from a command line. It fails without an erro
r
message and closes the DOS window.
Does anyone know what I might be missing here?
-Nik JohnsonAre you running from a .BAT file, or typing in the BCP command in Windows, S
tart, Rum? If from a BAT file, out
PAUSE after the BCP command. If start, don't. Open a DOS windows so you get
a proper command prompt and run
from there.
I don't think I've ever heard of a command line app closing a command window
,,,
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> I'm having trouble running bcp from a command line. It fails without an er
ror
> message and closes the DOS window.
> Does anyone know what I might be missing here?
> -Nik Johnson|||I think the window closing happened because I tried running from a shortcut.
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows,
Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you ge
t a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command wind
ow,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>|||C>A little more experimentation shows that if I RUN the whole path
(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
get the following:
Parameter format not correct
Specified COMMAND search directory bad
Too many parameters
Too many parameters
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-1999.
If I move everything to a directory immediately under the root (c:bcp) I get
a message: "Unable to load BCP resource DLL. BCP cannot continue."
-Nik
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows,
Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you ge
t a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command wind
ow,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>|||Nik,
Type command.com /? (at a command prompt) for a summary of how to use
command.com.
Type bcp (at a command prompt for a summary of how to use bcp.
Running bcp.exe with no parameters does not cause an error. It
displays the various switches that can be used with bcp and returns
control to the caller. That's what you're seeing when you run a batch
containing nothing but one line saying bcp or bcp.exe. What are you
expecting? As far as I know, there is no interactive mode for bcp, so
you can't just "start bcp" and then enter commands.
Steve Kass
Drew University
Nik Johnson wrote:
[vbcol=seagreen]
>C>A little more experimentation shows that if I RUN the whole path
>(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
>get the following:
>Parameter format not correct
>Specified COMMAND search directory bad
>Too many parameters
>Too many parameters
>Microsoft(R) Windows DOS
>(C)Copyright Microsoft Corp 1990-1999.
>If I move everything to a directory immediately under the root (c:bcp) I ge
t
>a message: "Unable to load BCP resource DLL. BCP cannot continue."
>-Nik
>
>
>"Tibor Karaszi" wrote:
>
>|||Steve-
I'm expecting exactly the behavior that you describe, i.e., a summary of how
to use bcp. But instead I get a message that a dll can't be loaded.
I have a feeling that the necessary dll is either missing or not in the
path. But I have no idea what that dll is.
-Nik
"Steve Kass" wrote:

> Nik,
> Type command.com /? (at a command prompt) for a summary of how to use
> command.com.
> Type bcp (at a command prompt for a summary of how to use bcp.
> Running bcp.exe with no parameters does not cause an error. It
> displays the various switches that can be used with bcp and returns
> control to the caller. That's what you're seeing when you run a batch
> containing nothing but one line saying bcp or bcp.exe. What are you
> expecting? As far as I know, there is no interactive mode for bcp, so
> you can't just "start bcp" and then enter commands.
> Steve Kass
> Drew University
> Nik Johnson wrote:
>
>

bcp won't run

I'm having trouble running bcp from a command line. It fails without an error
message and closes the DOS window.
Does anyone know what I might be missing here?
-Nik Johnson
Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
from there.
I don't think I've ever heard of a command line app closing a command window,,,
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> I'm having trouble running bcp from a command line. It fails without an error
> message and closes the DOS window.
> Does anyone know what I might be missing here?
> -Nik Johnson
|||I think the window closing happened because I tried running from a shortcut.
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>
|||C>A little more experimentation shows that if I RUN the whole path
(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
get the following:
Parameter format not correct
Specified COMMAND search directory bad
Too many parameters
Too many parameters
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-1999.
If I move everything to a directory immediately under the root (c:bcp) I get
a message: "Unable to load BCP resource DLL. BCP cannot continue."
-Nik
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>
|||Nik,
Type command.com /? (at a command prompt) for a summary of how to use
command.com.
Type bcp (at a command prompt for a summary of how to use bcp.
Running bcp.exe with no parameters does not cause an error. It
displays the various switches that can be used with bcp and returns
control to the caller. That's what you're seeing when you run a batch
containing nothing but one line saying bcp or bcp.exe. What are you
expecting? As far as I know, there is no interactive mode for bcp, so
you can't just "start bcp" and then enter commands.
Steve Kass
Drew University
Nik Johnson wrote:
[vbcol=seagreen]
>C>A little more experimentation shows that if I RUN the whole path
>(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
>get the following:
>Parameter format not correct
>Specified COMMAND search directory bad
>Too many parameters
>Too many parameters
>Microsoft(R) Windows DOS
>(C)Copyright Microsoft Corp 1990-1999.
>If I move everything to a directory immediately under the root (c:bcp) I get
>a message: "Unable to load BCP resource DLL. BCP cannot continue."
>-Nik
>
>
>"Tibor Karaszi" wrote:
>
|||Steve-
I'm expecting exactly the behavior that you describe, i.e., a summary of how
to use bcp. But instead I get a message that a dll can't be loaded.
I have a feeling that the necessary dll is either missing or not in the
path. But I have no idea what that dll is.
-Nik
"Steve Kass" wrote:

> Nik,
> Type command.com /? (at a command prompt) for a summary of how to use
> command.com.
> Type bcp (at a command prompt for a summary of how to use bcp.
> Running bcp.exe with no parameters does not cause an error. It
> displays the various switches that can be used with bcp and returns
> control to the caller. That's what you're seeing when you run a batch
> containing nothing but one line saying bcp or bcp.exe. What are you
> expecting? As far as I know, there is no interactive mode for bcp, so
> you can't just "start bcp" and then enter commands.
> Steve Kass
> Drew University
> Nik Johnson wrote:
>
sql

bcp won't run

I'm having trouble running bcp from a command line. It fails without an error
message and closes the DOS window.
Does anyone know what I might be missing here?
-Nik JohnsonAre you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
from there.
I don't think I've ever heard of a command line app closing a command window,,,
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> I'm having trouble running bcp from a command line. It fails without an error
> message and closes the DOS window.
> Does anyone know what I might be missing here?
> -Nik Johnson|||I think the window closing happened because I tried running from a shortcut.
"Tibor Karaszi" wrote:
> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> > I'm having trouble running bcp from a command line. It fails without an error
> > message and closes the DOS window.
> >
> > Does anyone know what I might be missing here?
> >
> > -Nik Johnson
>
>|||C>A little more experimentation shows that if I RUN the whole path
(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
get the following:
Parameter format not correct
Specified COMMAND search directory bad
Too many parameters
Too many parameters
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-1999.
If I move everything to a directory immediately under the root (c:bcp) I get
a message: "Unable to load BCP resource DLL. BCP cannot continue."
-Nik
"Tibor Karaszi" wrote:
> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> > I'm having trouble running bcp from a command line. It fails without an error
> > message and closes the DOS window.
> >
> > Does anyone know what I might be missing here?
> >
> > -Nik Johnson
>
>|||Nik,
Type command.com /? (at a command prompt) for a summary of how to use
command.com.
Type bcp (at a command prompt for a summary of how to use bcp.
Running bcp.exe with no parameters does not cause an error. It
displays the various switches that can be used with bcp and returns
control to the caller. That's what you're seeing when you run a batch
containing nothing but one line saying bcp or bcp.exe. What are you
expecting? As far as I know, there is no interactive mode for bcp, so
you can't just "start bcp" and then enter commands.
Steve Kass
Drew University
Nik Johnson wrote:
>C>A little more experimentation shows that if I RUN the whole path
>(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
>get the following:
>Parameter format not correct
>Specified COMMAND search directory bad
>Too many parameters
>Too many parameters
>Microsoft(R) Windows DOS
>(C)Copyright Microsoft Corp 1990-1999.
>If I move everything to a directory immediately under the root (c:bcp) I get
>a message: "Unable to load BCP resource DLL. BCP cannot continue."
>-Nik
>
>
>"Tibor Karaszi" wrote:
>
>>Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
>>PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
>>from there.
>>I don't think I've ever heard of a command line app closing a command window,,,
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
>>news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>>
>>I'm having trouble running bcp from a command line. It fails without an error
>>message and closes the DOS window.
>>Does anyone know what I might be missing here?
>>-Nik Johnson
>>
>>|||Steve-
I'm expecting exactly the behavior that you describe, i.e., a summary of how
to use bcp. But instead I get a message that a dll can't be loaded.
I have a feeling that the necessary dll is either missing or not in the
path. But I have no idea what that dll is.
-Nik
"Steve Kass" wrote:
> Nik,
> Type command.com /? (at a command prompt) for a summary of how to use
> command.com.
> Type bcp (at a command prompt for a summary of how to use bcp.
> Running bcp.exe with no parameters does not cause an error. It
> displays the various switches that can be used with bcp and returns
> control to the caller. That's what you're seeing when you run a batch
> containing nothing but one line saying bcp or bcp.exe. What are you
> expecting? As far as I know, there is no interactive mode for bcp, so
> you can't just "start bcp" and then enter commands.
> Steve Kass
> Drew University
> Nik Johnson wrote:
> >C>A little more experimentation shows that if I RUN the whole path
> >(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
> >get the following:
> >
> >Parameter format not correct
> >Specified COMMAND search directory bad
> >Too many parameters
> >Too many parameters
> >Microsoft(R) Windows DOS
> >(C)Copyright Microsoft Corp 1990-1999.
> >
> >If I move everything to a directory immediately under the root (c:bcp) I get
> >a message: "Unable to load BCP resource DLL. BCP cannot continue."
> >
> >-Nik
> >
> >
> >
> >
> >"Tibor Karaszi" wrote:
> >
> >
> >
> >>Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> >>PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> >>from there.
> >>
> >>I don't think I've ever heard of a command line app closing a command window,,,
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://www.solidqualitylearning.com/
> >>
> >>
> >>"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> >>news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> >>
> >>
> >>I'm having trouble running bcp from a command line. It fails without an error
> >>message and closes the DOS window.
> >>
> >>Does anyone know what I might be missing here?
> >>
> >>-Nik Johnson
> >>
> >>
> >>
> >>
> >>
>

bcp within batch file

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

Thanks!DBA (kaylisse@.yahoo.com) writes:
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?

The return status for a program called from a batch file is in
%ERRORLEVEL%, so this is the variable you should check.

I seem to recall that BCP does not always set this variable as one
may desire. It does set it, if the password is wrong. But I believe
it does not set %errorlevel% if some rows does not load.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Looks like most interesting bcp errors will set %errorlevel% to 1. An empty
input file, however, doesn't set the errorlevel. You can take action in a
..CMD file like this:

bcp <table> [in|out] <filespec> [switches]
if %errorlevel% 1 goto <label>
<normal processing steps here
:<label> echo something BAD happened to your BCP!
<steps to do something about it here
I didn't test it but I believe if you set the maxerrors switch, you won't
get the non-zero errorlevel unless you actually exceed that threshold. You
might want to test this yourself.

FYI - in our .CMD scripts, if I want to simply fail the job after the error,
I usually do this:

bcp <stuff>
if %errorlevel% 1 goto BCP_FAILED

and I don't bother using a BCP_FAILED label anywhere. Searching for it, the
job runs right past the end and aborts. You'll see a message saying "Can't
find label BCP_FAILED" or something similar as part of the job status report
if you run this through SQL Executive and, by convention here, that's the
diagnostic for the job.

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!|||Hi

%ERRORLEVEL% will be 0 when a succesful import has been performed. If it
fails then it will return 1 (on my tests!).

John

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!

BCP utility replaces German with junk characters

Hi All,
I am using command line bcp utility of SQL Server to import data from a text
file to database.

I have some german words in the text file and after
import the German characters are lost.

see eg below.

Input : Khner, Klaus -> Text file value
OutPut: Khner, Klaus -> Table data, after import.

I am using unicode in the commad line.
The collation setting is LATIN

can anyone show some light.

Thanks in advance

Manoj.Manoj (mcmanoj_2000@.yahoo.com) writes:
> I am using command line bcp utility of SQL Server to import data from a
> text file to database.
> I have some german words in the text file and after
> import the German characters are lost.
> see eg below.
> Input : Khner, Klaus -> Text file value
> OutPut: K?hner, Klaus -> Table data, after import.
> I am using unicode in the commad line.
> The collation setting is LATIN

Since BCP is a command-line utility it's defaul code page is the OEM
code page. Therefore, if your server uses an ANSI page, and this is
the normal, there is an automatic conversion from OEM to ANSI. Problem
is if the file is in ANSI already - which it often is.

BCP offers the -C option to control this. The simplest is to use
-C RAW to turn off conversion.

See the description of BCP in Books Online for more details.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Bcp utility

command :
bcp [ad-ad].ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword
return error:
SqlState = 37000, NativeError=4060
error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open database
requested in login '[ad-ad]. Login fails.
Solutions ?Catalin
exec master..xp_cmdshell 'bcp northwind..orders out c:\cust2.csv -c -t
"," -S<server> -Usa -P'
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> command :
> bcp [ad-ad].ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword
> return error:
> SqlState = 37000, NativeError=4060
> error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
database
> requested in login '[ad-ad]. Login fails.
> Solutions ?
>|||Problem is name of database [ad-ad].
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> command :
> bcp [ad-ad].ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword
> return error:
> SqlState = 37000, NativeError=4060
> error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
database
> requested in login '[ad-ad]. Login fails.
> Solutions ?
>|||Catalin
> requested in login '[ad-ad]. Login fails.
Are you sure? The error message tells something else.
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:#s7WZuhnDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Problem is name of database [ad-ad].
> "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> > command :
> > bcp [ad-ad].ager.article out
c:\Article.txt -n -Sserver -Uuser -Ppassword
> > return error:
> > SqlState = 37000, NativeError=4060
> > error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
> database
> > requested in login '[ad-ad]. Login fails.
> > Solutions ?
> >
> >
>|||Catalin;
Use the -q command line switch. There seems to be some problem with square
brackets around the database name for the bcp utility. I've run into the
same error message, which doesn't seem to be relevant at all.
Anyway, here's the bcp command line that works:
bcp ad-ad.ager.article out c:\Article.txt -n -Sserver -Uuser -Ppassword -q
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OwMRkwhnDHA.2364@.TK2MSFTNGP11.phx.gbl...
> Catalin
> > requested in login '[ad-ad]. Login fails.
> Are you sure? The error message tells something else.
>
> "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> news:#s7WZuhnDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Problem is name of database [ad-ad].
> >
> > "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> > news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> > > command :
> > > bcp [ad-ad].ager.article out
> c:\Article.txt -n -Sserver -Uuser -Ppassword
> > > return error:
> > > SqlState = 37000, NativeError=4060
> > > error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
> > database
> > > requested in login '[ad-ad]. Login fails.
> > > Solutions ?
> > >
> > >
> >
> >
>|||Catalin,
try this:
bcp "ad-ad.ager.article" out c:\Article.txt -n -Sserver -Uuser -Ppassword
"Catalin Tudorescu" <catalint@.ager.ro> wrote in message
news:%23s7WZuhnDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Problem is name of database [ad-ad].
> "Catalin Tudorescu" <catalint@.ager.ro> wrote in message
> news:ezP6amhnDHA.744@.tk2msftngp13.phx.gbl...
> > command :
> > bcp [ad-ad].ager.article out
c:\Article.txt -n -Sserver -Uuser -Ppassword
> > return error:
> > SqlState = 37000, NativeError=4060
> > error= [Microsoft][odbc SQL Server Driver][Sql Server] Cannot open
> database
> > requested in login '[ad-ad]. Login fails.
> > Solutions ?
> >
> >
>

BCP using Win Authentication

Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
Thanks
Hi,

> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
TRY Task Scheduler.
(It protect your password!)
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2005
|||> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
The '-T' parameter works with all BCP modes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
> Hi
> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
> Thanks
>
|||You CAN NOT provide an Integrated Login. To work with Windows
Authentication, your "Batch" must be "Logged In" to the network. Now, there
are several ways to do this:
Create an application service.
Write your batch as DCOM or COM+ component.
Use Windows Task Scheduler.
Sincerely,
Anthony Thomas

"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
Thanks

BCP using Win Authentication

Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
ThanksHi,
> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
TRY Task Scheduler.
(It protect your password!)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2005|||> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
The '-T' parameter works with all BCP modes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
> Hi
> I would like to do a BCP out command in an automated job using WIndows
> Authentication and would like to provide the id similar to providing a SQL
> authentication id using the -U switch
> I know there is a -T switch but that works in interactive mode and i want
> something that can work in batch mode
> Thanks
>|||You CAN NOT provide an Integrated Login. To work with Windows
Authentication, your "Batch" must be "Logged In" to the network. Now, there
are several ways to do this:
Create an application service.
Write your batch as DCOM or COM+ component.
Use Windows Task Scheduler.
Sincerely,
Anthony Thomas
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:60DC798B-E724-444B-A0DB-15296824127C@.microsoft.com...
Hi
I would like to do a BCP out command in an automated job using WIndows
Authentication and would like to provide the id similar to providing a SQL
authentication id using the -U switch
I know there is a -T switch but that works in interactive mode and i want
something that can work in batch mode
Thankssql

BCP Transaction Does not roll back

Hi All,
I'm trying to import some data using the BCP command line utility.
I've set the maxerrors switch to 0. In case of an exception such as a
cast exception the import fails leaving the table state dirtied.
ie...with partial data imported.
From what I understand from a few other posts the transaction logs
only store the space alocated and not the actual data.
How does one ensure that the import is done in a transaction ?
Any suggestions/ideas will be great.
Regards,
Avinash
Can you post the command you are running
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Avinash" <avinashraj@.gmail.com> wrote in message
news:f22f61b0.0412050250.3d6ef381@.posting.google.c om...
> Hi All,
> I'm trying to import some data using the BCP command line utility.
> I've set the maxerrors switch to 0. In case of an exception such as a
> cast exception the import fails leaving the table state dirtied.
> ie...with partial data imported.
> From what I understand from a few other posts the transaction logs
> only store the space alocated and not the actual data.
> How does one ensure that the import is done in a transaction ?
> Any suggestions/ideas will be great.
> Regards,
> Avinash

bcp syntax

When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.sqlmonster.com
Bcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.c om...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com

bcp syntax

When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
--
Message posted via http://www.sqlmonster.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
--
-oj
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQLMonster.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} datafile
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide character
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row terminator]
> [-i inputfile] [-o outfile] [-a packetsize]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.sqlmonster.com

bcp syntax

When I run the following from the command line (referencing SQL 2K):
bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
archive.bcp -n -eD:\SQLData\TEST\error.txt
I get the following message:
Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafi
le
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide charact
er
type]
[-N keep non-text native] [-6 6x file format] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row terminat
or]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional ena
ble]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
Please help.
Message posted via http://www.droptable.comBcp uses parsename() to parse for object name. It seems to have problem
parsing back bracket.
Do this instead.
bcp "cms user messaging archive"."dbo"."archivepurge" out
"D:\SQLData\TEST\archive.bcp" -n -e"D:\SQLData\TEST\error.txt" -q
-oj
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e4ec7d6406fa4ad9955a3a244ea8b1f7@.SQ
droptable.com...
> When I run the following from the command line (referencing SQL 2K):
> bcp [cms user messaging archive].dbo.archivepurge out D:\SQLData\TEST\
> archive.bcp -n -eD:\SQLData\TEST\error.txt
> I get the following message:
> Copy direction must be either 'in', 'out' or 'format'.
> usage: bcp {dbtable | query} {in | out | queryout | format} data
file
> [-m maxerrors] [-f formatfile] [-e errfile]
> [-F firstrow] [-L lastrow] [-b batchsize]
> [-n native type] [-c character type] [-w wide chara
cter
> type]
> [-N keep non-text native] [-6 6x file format] [-q quoted
> identifier]
> [-C code page specifier] [-t field terminator] [-r row termin
ator]
> [-i inputfile] [-o outfile] [-a packetsize
]
> [-S server name] [-U username] [-P password]
> [-T trusted connection] [-v version] [-R regional e
nable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
> Please help.
> --
> Message posted via http://www.droptable.comsql

BCP support in SQL Server 2005 - Doubts

Hi friends,

I am currently using SQL Server 2000 server. I have run some BCP command to import data to SQL from CSV and Text files with the help of FORMAT files through Windows Scheduler.

My doubt is, if I swich to SQL Server 2005, whether the same BCP commands will supported? If no, What are the things I need to do to run the BCP commands in 2005 as in 2000?

I heard that the BCP utility is no longer support in 2005 and this can be done thru SSIS utility. Is it right...?

Is SQL Server 2005 support to use the BCP commands without going to SSIS utility?

Please help. Thanks in advance.

Regards,

Sethu.

BCP should still work for you -it is a backwards compatibility issue.

The SQL 2005 utility is SQLCommand.exe. It has almost the identical command set as BCP. Refer to Books Online, Topic: 'SQLCommand utility'

BCP queryout

Hello,
How can I include the columns titles in a bcp SELECT QUERYOUT command? (I
wish I could obtain the same result than in Query Analyzer)
Is there an other way to export the result of a select statement?
Best regardswhy don't use a view to bcp out!
Edwar Bishara
www.sqlcare.com
edwarb@.sqlcare.com|||BCP will not output the column headings. Only the data. You can get the
column headings by using command line osql with the -h parameter.
Rand
This posting is provided "as is" with no warranties and confers no rights.

BCP problems

I'm having problems using the BCP tool in MS SQL Server 6.5 Enterprise Manager. At the moment the tool or command prompt for BCP does not load correctly. As soon as I click on BCP in the Tools menu a black window apear for a fraction of a second an then disapears.
The task i want to use bcp for is to export 2 tables from a database to ASCIIfiles to be archived. Any other tools that I could use for that?

As you have probably guessed I'm a total newbie with SQL Server so any help or ideas would be really appreciated.

Thanks!
/aI've never used 6.5 so I'm not sure of it's DTS capabilities, but in 7.0 and on exporting tables can be accomplished fairly easy in EM by right clicking on the table -> All tasks -> Export Data into a variety of formats.

Also, you can open a command window outside of EM and enter BCP statements directly. Entering BCP at the prompt should give you the format and parameters.|||Wohoo it works! I was so focused that bcp was only used in EM, but now it works. Thanks a bunch. Now I only have to figure out how to format it the way I want. :-o|||Ever look at xp_cmdshell and bcp?|||Originally posted by Brett Kaiser
Ever look at xp_cmdshell and bcp?

Only brief, is it good? Or the question I should be asking, can I use it on this crappy old NTserver?

Hmm.. it seems like bcp does not like . How can that be solved?|||If you can live with a non-standard (binary) file format, you can use -n. If you want to stay pure ASCII, use -w but be prepared for Unicode (16 bit) characters.

-PatP

BCP problem on different servers

I=B4m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebasti=E1n1) can you bcp manually from a command prompt?
2) can you connect using sqlcmd and/or SSMS?
Likely issue is that appropriate network/connectivity settings are not there
for SQL2K5.
TheSQLGuru
President
Indicium Resources, Inc.
<spiccolotto@.gmail.com> wrote in message
news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
Im using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebastin|||Yes, I can bcp manually and connect using SSMS.
I will explain more my scenario:
Server A have SQL Server 2000 / Windows 2000.
Here, I have a VB6 DLL and CSV file. Inside this dll I execute BCP
command to send csv file data to Remote Database (On Server B).
BCP command executed from VB6 DLL doesn=B4t work. If I execute same BCP
command but now from command line (cmd) all works ok.
Server B have SQL Server 2005 / Windows 2003.
My questions are:
=BFAny incompatibility between BCP Versions (80 and 90)?
=BFWhy bcp works from command line (cmd)?
=BFI need to modify any permission?
Regards
Sebasti=E1n.
On Apr 18, 4:29 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) can youbcpmanually from a command prompt?
> 2) can you connect using sqlcmd and/or SSMS?
> Likely issue is that appropriate network/connectivity settings are not th=
ere
> for SQL2K5.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <spiccolo...@.gmail.com> wrote in message
> news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
> I=B4m usingBCPcommand inside VB6 DLL to import data from csv file to
> database.
> Normally it works fine, but when my database is installed in a server
> with Windows 2003 and SQL Server 2005 I had problems...bcpnot
> works... In this scenario, If I execute the samebcpcommand but now
> from command line all is ok... what is theproblem?
> Thanks
> Sebasti=E1nsql