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 Utility login failure

What am I doing wrong to cause this error
C:\>bcp bookshop..bookcondition in bookcondition.txt -c -T -Ujack -Ppasswor
SQLState = 28000, NativeError = 1845
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'J
CK_WACHTLER\Jack Wachtler'
Thanks for your help
JackJack,
you'll need to remove the -T argument which selects trusted security and
therefore ignores the last 2 arguments. Have a look in BOL : bcp utility,
overview for a listing of all the individual arguments.
HTH,
Paul Ibison|||Using the trusted connection it should assume I am already logged in as a authenticated user. So why am I getting this error? I don't get it
C:\>bcp BookShopDB..BookCondition in BookCondition.txt -c -
SQLState = 28000, NativeError = 1845
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'J
CK_WACHTLER\Jack Wachtler'
Jack|||Jack,
this will create a separate connection to sql server and will only work if
'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
instance of sql server on the local computer where you are running bcp, or
alternatively if this windows user exists in a group added as a windows
login.
HTH,
Paul Ibison|||Hi Jack,
Dont you require the -S <Server_name> along with your bcp command? Try to
include this option and verify.
THanks
Hari
MCDBA
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OL6HON4OEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Jack,
> this will create a separate connection to sql server and will only work if
> 'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
> instance of sql server on the local computer where you are running bcp, or
> alternatively if this windows user exists in a group added as a windows
> login.
> HTH,
> Paul Ibison
>sql

bcp Utility login failure

What am I doing wrong to cause this error?
C:\>bcp bookshop..bookcondition in bookcondition.txt -c -T -Ujack -Ppassword
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login fai
led for user 'JA
CK_WACHTLER\Jack Wachtler'.
Thanks for your help!
JackJack,
you'll need to remove the -T argument which selects trusted security and
therefore ignores the last 2 arguments. Have a look in BOL : bcp utility,
overview for a listing of all the individual arguments.
HTH,
Paul Ibison|||Using the trusted connection it should assume I am already logged in as a au
thenticated user. So why am I getting this error? I don't get it.
C:\>bcp BookShopDB..BookCondition in BookCondition.txt -c -T
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login fai
led for user 'JA
CK_WACHTLER\Jack Wachtler'.
Jack|||Jack,
this will create a separate connection to sql server and will only work if
'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
instance of sql server on the local computer where you are running bcp, or
alternatively if this windows user exists in a group added as a windows
login.
HTH,
Paul Ibison|||Hi Jack,
Dont you require the -S <Server_name> along with your bcp command? Try to
include this option and verify.
THanks
Hari
MCDBA
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OL6HON4OEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Jack,
> this will create a separate connection to sql server and will only work if
> 'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
> instance of sql server on the local computer where you are running bcp, or
> alternatively if this windows user exists in a group added as a windows
> login.
> HTH,
> Paul Ibison
>

bcp Utility login failure

What am I doing wrong to cause this error?
C:\>bcp bookshop..bookcondition in bookcondition.txt -c -T -Ujack -Ppassword
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'JA
CK_WACHTLER\Jack Wachtler'.
Thanks for your help!
Jack
Jack,
you'll need to remove the -T argument which selects trusted security and
therefore ignores the last 2 arguments. Have a look in BOL : bcp utility,
overview for a listing of all the individual arguments.
HTH,
Paul Ibison
|||Using the trusted connection it should assume I am already logged in as a authenticated user. So why am I getting this error? I don't get it.
C:\>bcp BookShopDB..BookCondition in BookCondition.txt -c -T
SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'JA
CK_WACHTLER\Jack Wachtler'.
Jack
|||Jack,
this will create a separate connection to sql server and will only work if
'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
instance of sql server on the local computer where you are running bcp, or
alternatively if this windows user exists in a group added as a windows
login.
HTH,
Paul Ibison
|||Hi Jack,
Dont you require the -S <Server_name> along with your bcp command? Try to
include this option and verify.
THanks
Hari
MCDBA
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OL6HON4OEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Jack,
> this will create a separate connection to sql server and will only work if
> 'JACK_WACHTLER\Jack Wachtler' exist as a windows login on the default
> instance of sql server on the local computer where you are running bcp, or
> alternatively if this windows user exists in a group added as a windows
> login.
> HTH,
> Paul Ibison
>

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 utility

I am trying to create a flat file from SQL server and here is
what my query looks like:
bcp "select intakeid from tblintake" queryout d:\out.txt -c
I got this error: "Line 1: Incorrect syntax near 'queryout'."
when I run it in query analyzer. Can anyone please tell me what
I did wrong? I
Thanks.
MLPMLP,
> I am trying to create a flat file from SQL server and here is
> what my query looks like:
> bcp "select intakeid from tblintake" queryout d:\out.txt -c
> I got this error: "Line 1: Incorrect syntax near 'queryout'."
> when I run it in query analyzer. Can anyone please tell me what
> I did wrong? I
Bcp is a command line utility. You need to run it from a command
prompt (aka DOS window). It cannot be run in Query Analyzer.
Linda|||If you want to run BCP from QA, you need to use
xp_cmdshell.
>--Original Message--
>I am trying to create a flat file from SQL server and
here is
>what my query looks like:
>bcp "select intakeid from tblintake" queryout
d:\out.txt -c
>I got this error: "Line 1: Incorrect syntax
near 'queryout'."
>when I run it in query analyzer. Can anyone please tell
me what
>I did wrong? I
>Thanks.
>MLP
>.
>

BCP to txt file

I am trying the following but it only works on a local database I want to ru
n
this on a remote server and have the file sent to my PC.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'You have to create a shared folder in your pc and give write access to sql
agent services account. Then use that shared folder in the bcp command.
EXEC master..xp_cmdshell
'bcp "SELECT * FROM TestDB_data..titles" queryout
"\\your_pc_name\shared_folder\test_08-25-05.txt" -S
server1 -U sa -P sapwd -c'
AMB
"Lontae Jones" wrote:

> I am trying the following but it only works on a local database I want to
run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout "c:\test_08-25-05.txt" -
S
> server1 -U sa -P sapwd -c'
>|||It works only if your servers are linked.
Try using tabe name directly (instead of using query) it should work
bcp "TestDB_data..titles" OUT c:\test_08-25-05.txt ....
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:80C1C9DA-2C3F-4048-9B55-7F849FD6B662@.microsoft.com...
>I am trying the following but it only works on a local database I want to
>run
> this on a remote server and have the file sent to my PC.
> EXEC master..xp_cmdshell
> 'bcp "SELECT * FROM TestDB_data..titles" queryout
> "c:\test_08-25-05.txt" -S
> server1 -U sa -P sapwd -c'
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table?
Thanks
Steffan
Temporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
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
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegr oups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table?
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid o
bject
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
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
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
--
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
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

BCP syntax

is there anything wrong with my syntax ?
bcp AGENCY out E:\Spreadsheets\Agency.txt -c -Svicbranch1 -Usqlsvc -Pabc123
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.I can't see anything obviously wrong.
I always specify database.owner.table in the first parameter to bcp, though.|||BCP is run from a command line, rather than from Query Analyzer.|||I would suggest fully qualifing the table eg. dbname.dbo.tablename

Or the problem may be that the table you are trying to access is in a different database to the default database of the username you are using..

Just a thoughysql

BCP question

Hello All
I am trying to use the BCP utility. Here is what I did. From the dos prompt
I ran
bcp HPDELGt out C:\eldorado\elbcp.txt -SMyserver -Uusername -Ppassword
which generated a bcp.fmt file
then I ran
bcp HPDELGt in C:\elbcp.txt -fC:\bcp.fmt -SMyserver -Uusername -Ppassword
But it gives me a message that 0 rows copied - below is .fmt file - the
input file is a comma separated file. I would like to do it with a fixed
length file which would have no commas. how would I do that
8.0
68
1 SQLCHAR 0 3 "," 1 PUNBR
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "," 2 GRNBR
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 9 "," 3 ESSN
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "," 4 SEQ
SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 0 8 "," 5
EFFDATE ""
6 SQLCHAR 0 1 "," 6 STATUS
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "," 7
SELECT1 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "," 8
SELECT2 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "," 9
SELECT3 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "," 10
SELECT4 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "," 11
SELECT5 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "," 12
SELECT6 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "," 13
SELECT7 SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 1 "," 14
SELECT8 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 1 "," 15
SELECT9 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "," 16
SELECT10 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "," 17
SELECT11 SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "," 18
SELECT12 SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 1 "," 19
SELECT13 SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 1 "," 20
SELECT14 SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 1 "," 21
SELECT15 SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 10 "," 22 BEN1
SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 10 "," 23 BEN2
SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 10 "," 24 BEN3
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 10 "," 25 BEN4
SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 10 "," 26 BEN5
SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 10 "," 27 BEN6
SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 10 "," 28 BEN7
SQL_Latin1_General_CP1_CI_AS
29 SQLDATETIME 1 8 "," 29
TRMDATE ""
30 SQLCHAR 0 1 "," 30
PRODUCT1 SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 1 "," 31
PRODUCT2 SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 1 "," 32
PRODUCT3 SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 1 "," 33
PRODUCT4 SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 1 "," 34
PRODUCT5 SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 1 "," 35
PRODUCT6 SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 1 "," 36
PRODUCT7 SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 1 "," 37
PRODUCT8 SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 1 "," 38
PRODUCT9 SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 1 "," 39
PRODUCT10 SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 1 "," 40
PRODUCT11 SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 1 "," 41
PRODUCT12 SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 1 "," 42
PRODUCT13 SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 1 "," 43
PRODUCT14 SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 1 "," 44
PRODUCT15 SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 1 "," 45
COBFLAG1 SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 1 "," 46
COBFLAG2 SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 1 "," 47
COBFLAG3 SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 1 "," 48
COBFLAG4 SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 1 "," 49
COBFLAG5 SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 1 "," 50 PEND
SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 3 "," 51 PNDRS
SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 1 "," 52
COBTYPE1 SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 1 "," 53
COBTYPE2 SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 1 "," 54
COBTYPE3 SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 1 "," 55
COBTYPE4 SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 1 "," 56
COBTYPE5 SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 8 "," 57 PCP1
SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 8 "," 58 PCP2
SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 3 "," 59 COBCR1
SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 3 "," 60 COBCR2
SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 3 "," 61 COBCR3
SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 3 "," 62 COBCR4
SQL_Latin1_General_CP1_CI_AS
63 SQLCHAR 0 3 "," 63 COBCR5
SQL_Latin1_General_CP1_CI_AS
64 SQLCHAR 0 3 "," 64 COBLT1
SQL_Latin1_General_CP1_CI_AS
65 SQLCHAR 0 3 "," 65 COBLT2
SQL_Latin1_General_CP1_CI_AS
66 SQLCHAR 0 3 "," 66 COBLT3
SQL_Latin1_General_CP1_CI_AS
67 SQLCHAR 0 3 "," 67 COBLT4
SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 0 3 "\n" 68 COBLT5
SQL_Latin1_General_CP1_CI_ASRahul Chatterjee (rahul@.benesysinc.com) writes:
> I am trying to use the BCP utility. Here is what I did. From the dos
> prompt I ran
> bcp HPDELGt out C:\eldorado\elbcp.txt -SMyserver -Uusername -Ppassword
> which generated a bcp.fmt file
> then I ran
> bcp HPDELGt in C:\elbcp.txt -fC:\bcp.fmt -SMyserver -Uusername -Ppassword
> But it gives me a message that 0 rows copied
Maybe I'm pointing out something irrelevant, but the BCP IN does not have
the same directory for the file as the BCP out command.
> - below is .fmt file - the
> input file is a comma separated file. I would like to do it with a fixed
> length file which would have no commas. how would I do that
You replace "," with "". There are already length indicators in your
format file.
Look BCP in Books Online. At the end of that page there is a link to
a page about format files.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

BCP problem

Exec Master..xp_CmdShell 'bcp "exec mydb..SP_test ''200603''" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'

when i run this code in java , the file is created , but data not pump in , anyone know why is it so ?

it can run in query analyzer and no problemsorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data|||sorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data out

BCP or BULK INSERT, which one is better to use.

Hi,
I have some data in ANSI txt file, i want to know which one would be
better and recommended way to import this into table. bcp or BULK
INSERT
Appreciate your inputs on this.
Thanks,
ManojPersonally, I use bcp , only because of the added flexibility that I can
also output into a data file
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Manoj" <manoj_poonia@.dell.com> wrote in message
news:1139581478.079428.98540@.g47g2000cwa.googlegroups.com...
> Hi,
> I have some data in ANSI txt file, i want to know which one would be
> better and recommended way to import this into table. bcp or BULK
> INSERT
> Appreciate your inputs on this.
> Thanks,
> Manoj
>|||Manoj (manoj_poonia@.dell.com) writes:
> I have some data in ANSI txt file, i want to know which one would be
> better and recommended way to import this into table. bcp or BULK
> INSERT
I seem to recall that I saw some numbers that indicated that BULK
INSERT is considerably faster than BCP.
However, with moderate volumes simplicity may be more important.
BULK INSERT requires that the file is visible from SQL Server. That
is, on the same machine as SQL Server, or a network drive that
SQL Server can see. For BCP there is no such requirement.
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

Bcp import - how?

I've exported one table using DTS to *.txt file:
"id","name","calories"
{F5F781EF-4270-4D8C-B5E0-A968F45FF771},"Ananas frisch",60
{CA0E96D8-9017-4DDD-BF29-B78D3DC8180D},"Ananas Fruchtnektar",70
{A1777D54-9303-4AF5-A721-250A39BA731C},"Ananas Fruchtsaft",60
{DACC2063-0985-41EB-9A33-B4D8A10F1C79},"Ananas getrocknet",300
{04E462EC-57B1-4882-A8DF-A1EFC1B9C4F3},"Ananas kandiert",250
How can I import this data using bcp? I've tried many combination of
parameters (-q etc.), but the result ist always wrong (that means, I've
imported but fields are filled with no or partial data). Where can I find a
good description of bcp utility with examples (msdn is very poor)?
Thanx in advance,
Adam
Adam Boczek
adam.boczek@.cs-consulting.de
Hi,
Use BULK Insert.
1. I have saved the contents as ccc.txt in drive.
2. Created a table with below script :-
create table zz(id uniqueidentifier,name varchar(30),calories int)
3. Executed the below in query analyzer
BULK INSERT master.dbo.zz
FROM 'c:\ccc.txt'
WITH
(
FIELDTERMINATOR = ','
)
4. select * from zz
id name calories
-- -- --
F5F781EF-4270-4D8C-B5E0-A968F45FF771 "Ananas frisch" 60
CA0E96D8-9017-4DDD-BF29-B78D3DC8180D "Ananas Fruchtnektar" 70
A1777D54-9303-4AF5-A721-250A39BA731C "Ananas Fruchtsaft" 60
DACC2063-0985-41EB-9A33-B4D8A10F1C79 "Ananas getrocknet" 300
04E462EC-57B1-4882-A8DF-A1EFC1B9C4F3 "Ananas kandiert" 250
(5 row(s) affected)
Thanks
Hari
MCDBA
"Adam Boczek" <adam.boczek@.NO_SPAM.cs-consulting.de> wrote in message
news:1086160529.62750@.proxy.ham.cs-consulting.de...
> I've exported one table using DTS to *.txt file:
> "id","name","calories"
> {F5F781EF-4270-4D8C-B5E0-A968F45FF771},"Ananas frisch",60
> {CA0E96D8-9017-4DDD-BF29-B78D3DC8180D},"Ananas Fruchtnektar",70
> {A1777D54-9303-4AF5-A721-250A39BA731C},"Ananas Fruchtsaft",60
> {DACC2063-0985-41EB-9A33-B4D8A10F1C79},"Ananas getrocknet",300
> {04E462EC-57B1-4882-A8DF-A1EFC1B9C4F3},"Ananas kandiert",250
> How can I import this data using bcp? I've tried many combination of
> parameters (-q etc.), but the result ist always wrong (that means, I've
> imported but fields are filled with no or partial data). Where can I find
a
> good description of bcp utility with examples (msdn is very poor)?
> Thanx in advance,
> Adam
> --
> ----
> Adam Boczek
> adam.boczek@.cs-consulting.de
> ----
>

bcp help

Hi!
Trying to run this command:
bcp Risk.dbo.tbl_Open in C:\temp2\2006.05.11-09.20.11.11\Risk\Open.txt
-T -n
getting the followin error message:
SQLState = 37000, Native Error = 156
Incorrect Syntax near the keyword 'Open'.
I can't not rename a table. Is there a work around?
Thanks,Are you executing this in Query Analyzer or from an operating system command prompt. BCP is not a
TSQL command. If you want to run a TSQL command, use BULK INSERT instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tolcis" <a.liberchuk@.verizon.net> wrote in message
news:1147370866.716078.250460@.i40g2000cwc.googlegroups.com...
> Hi!
> Trying to run this command:
> bcp Risk.dbo.tbl_Open in C:\temp2\2006.05.11-09.20.11.11\Risk\Open.txt
> -T -n
> getting the followin error message:
> SQLState = 37000, Native Error = 156
> Incorrect Syntax near the keyword 'Open'.
> I can't not rename a table. Is there a work around?
> Thanks,
>|||I am executing from DOS command.|||That is strange since the error complain on the keyword Open where your code refers to a table named
tbl_Open. Anyhow, check out the -q option of bcp.exe.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:OmydNdSdGHA.3484@.TK2MSFTNGP04.phx.gbl...
> Are you executing this in Query Analyzer or from an operating system command prompt. BCP is not a
> TSQL command. If you want to run a TSQL command, use BULK INSERT instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tolcis" <a.liberchuk@.verizon.net> wrote in message
> news:1147370866.716078.250460@.i40g2000cwc.googlegroups.com...
>> Hi!
>> Trying to run this command:
>> bcp Risk.dbo.tbl_Open in C:\temp2\2006.05.11-09.20.11.11\Risk\Open.txt
>> -T -n
>> getting the followin error message:
>> SQLState = 37000, Native Error = 156
>> Incorrect Syntax near the keyword 'Open'.
>> I can't not rename a table. Is there a work around?
>> Thanks,
>

BCp ERROR?

Getting the following error when loading txt file to a table
All rows appear to be loaded

What can be done to avoid this error?

Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

bcp "table" in "textfile" -f"Format.fmt" -e"error.txt" -SServername -Tis that the entire contents of the error.txt file? Usually the error file will tell you what row/column had the problem.