bcp/BULK INSERT and blank lines

Does anyone know of a way to make BULK INSERT or bcp ignore blank lines in the file? I am having trouble with a bunch of data files coming back with 1 or 2 blank lines at the end, and it causes the entire bcp to fail.

I suppose I could write a utility to trim the files but that seems a bit overkill. Any thoughts?

You will need to trim the data 'cuz bcp/bulk insert is just a _dumb_ data loader.|||One option is to use the -L parameter of BCP to specify the last row. This will let you ignore the lines at the end that is not formatted correctly. However, you have to count the lines in the file and subtract the offending number of lines to specify the value. If this doesn't work for you then you will have to correct the data file before using it with BCP or BULK INSERT.|||ah, -L! Thanks for the correction. I've never used that flag. It seems much simpler to just trim the blank lines...

BCP.EXE for SQL Server

Is it possible/legal to place just the BCP.exe and it's required files on
a non-sql server machine? If not please let me know the best way to use bulk insert into SQL server from a remote machine?

-thanks
john

Yes, you can, but be aware that accessing SQL Server will need an additional licence if connected to a productional server.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||See my post in this similar thread.

sql

BCP,CTE,NTILE

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTEs are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

BCP,CTE,NTILE

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTEs are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

BCP,CTE and NTILE problem?

Hi, i am trying to create a bunch of flat files from a table after breaking it down to deciles. this is what i am trying to do:

DECLARE @.FileName varchar(50),

@.bcpCommand varchar(8000);

With temporary(name,Decile)

as

(

select name, NTILE(10) over (order by sales DESC) as 'Decile' from table1 where date=199205

)

update table1

SET @.FileName = REPLACE('D:\Test\9205'+'.txt','/','-')

SET @.bcpCommand = 'bcp "select name from temporary where decile=1" queryout "'

SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U -P -c'

EXEC master..xp_cmdshell @.bcpCommand

the error i get is :Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'temporary'.

If i use Declare after the CTE has been defined i get :

Incorrect syntax near the keyword 'DECLARE'.

Any suggestion. Thanks in advance.

CTE is only visible within a batch in a connection. When you execute BCP using xp_cmdshell, you are actually invoking an external process (bcp in this case) which makes a new connection to SQLServer. So it will not have any context about the declared CTE. Also, the way you are using the CTE doesn't make sense also. You declare a CTE but then you update the base table directly. Even though it is legal I am not sure what you are doing. CTE is also not a peristent object like view or table-valued function.

You could do one of the following:

1. Query from the base table directly

2. Specify CTE definition in the queryout parameter itself

3. Dump the results of the table into a global temporary table and export from there (this will prevent the BCP from being executed simultaneously by different connections though)

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?
I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David
|||Any recomended reading?
|||?
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegr oups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Serv.../dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-S...dp/0764584359/
David

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-Server-Integration-Services-Programmer/dp/0764584359/
David

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Ser...5
84359/
David

bcp utility

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

Thanks allYou can use the extended stored procedure xp_cmdshell to execute bcp from a
stored procedure. Please read in SQL Server Books Online the security
implications. Assuming you do not want to run it under an account that is
member of sysadmin, you may want to set up a proxy account via
sp_xp_cmdshell_proxy.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Mike (mckeyes@.gmail.com) writes:

Quote:

Originally Posted by

I'm using SQL 2005 to export data. I would like to use the bcp utility
to export data to an Excel file.
I have to generate quite a few files and the names are dynamic. The
ideal would be to loop through records in a stored procedure to create
a file name to use in the bcp. My question is how can I use the bcp
from a stored procedure? I know how to run it from the command prompt.
Is there a way to control the command prompt from a stored procedure?

As Plamen said, you can use xp_cmdshell, but xp_cmdshell is a security
risk and for this reason it is disabled by default. It may be better
to write a small application VBscript or whatever you fancy to run
the export.

--
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 using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

BCP using SMO

We have a SQLServer 2000 stored procedure, which imports data from files using SQL-DMO BulkCopy object(sp_OACreate is used to create the objects). We also use Format files to aide the import.

Now, we are planning to convert this stored procedure to .NET application using Visual Studio 2005. Books Online says I have to use SMO Transfer object. But I could not find any information how to BulkCopy using this object.

Any guidance in this regard would be helpful.

Thanks

Baskar

Please refer to the SMO Transfer BOL topic: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/smo9mref/html/T_Microsoft_SqlServer_Management_Smo_Transfer.htm|||Hi, SMO does not have support for BCP objects.

You have 2 options:

1. Use SQL-DMO BulkCopy
2. Use SqlBulkCopy: http://msdn2.microsoft.com/en-us/library/30c3y597(en-us,vs.80).aspx.|||

I tried to do BCP using SQL-DMO object. Our project requires FORMAT FILES to load data into work tables.

If I generate the format file according to SQL 2005 specification (with 9.0 as version number on top of format file), my C# application reports an error "Attempts to read unknown version of BCP Format file". If I change it to 8.0 it works fine.
I installed the backward compatibility utility for SQL-DMO (SQL2005_BC.msi), thinking that it might fix the problem. Nope again the same message.

Now with the SqlBulkCopy object of ADO.NET, can we import into a table from FILES? Can I use the Mappings collection to map columns in text file to Work Table?

Any help in this regard is greatly appreciated.

Thanks
Baskar

bcp unicode files using format files

I have a problem with bcp and format files.

We changed our databases from varchar to nvarchar to support unicode. No

problems so fare with that. It is working fine.

But now I need a format file for the customer table and and it is not
working. It is working fine with the old DB with varchar, but with
nvarchar I'm not able to copy the data. The biggest problem is, that I
got no error message. BCP starts copying to table and finished without
error message.

This is my table:

CREATE TABLE [dbo].[Customer] (
[ID] [int] NOT NULL ,
[CreationTime] [datetime] NULL ,
[ModificationTime] [datetime] NULL ,
[DiscoveryTime] [datetime] NULL ,
[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Class] [int] NULL ,
[Subclass] [int] NULL ,
[Capabilities] [int] NULL ,
[SnapshotID] [int] NOT NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL ,
[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NOT NULL ,
[LocationID] [int] NULL ,
[MirrorID] [binary] (16) NULL ,
[DeleteFlag] [bit] NULL ,
[AdminStatus] [bit] NULL
) ON [PRIMARY]
GO

and this is the format file:

8.0
13
1 SQLINT 1 12 "#~@.~#" 1 ID ""
2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
SQL_Latin1_General_CP1_CI_AS
6 SQLINT 1 12 "#~@.~#" 6 Class ""
7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
SQL_Latin1_General_CP1_CI_AS
12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""

"#~@.~#" is the field terminator. We have a lot of text files with all
kind of charachers in it. So we think this is a set that will never
occur in our files.

Thanks for your help!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Bernd Lambertz <virus@.securiy.com> wrote in message news:<3fb38797$0$196$75868355@.news.frii.net>...
> I have a problem with bcp and format files.
> We changed our databases from varchar to nvarchar to support unicode. No
> problems so fare with that. It is working fine.
> But now I need a format file for the customer table and and it is not
> working. It is working fine with the old DB with varchar, but with
> nvarchar I'm not able to copy the data. The biggest problem is, that I
> got no error message. BCP starts copying to table and finished without
> error message.
> This is my table:
> CREATE TABLE [dbo].[Customer] (
> [ID] [int] NOT NULL ,
> [CreationTime] [datetime] NULL ,
> [ModificationTime] [datetime] NULL ,
> [DiscoveryTime] [datetime] NULL ,
> [Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [Class] [int] NULL ,
> [Subclass] [int] NULL ,
> [Capabilities] [int] NULL ,
> [SnapshotID] [int] NOT NULL ,
> [CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
> NULL ,
> [TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
> NOT NULL ,
> [LocationID] [int] NULL ,
> [MirrorID] [binary] (16) NULL ,
> [DeleteFlag] [bit] NULL ,
> [AdminStatus] [bit] NULL
> ) ON [PRIMARY]
> GO
> and this is the format file:
> 8.0
> 13
> 1 SQLINT 1 12 "#~@.~#" 1 ID ""
> 2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
> 3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
> 4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
> 5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLINT 1 12 "#~@.~#" 6 Class ""
> 7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
> 8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
> 9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
> 10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
> SQL_Latin1_General_CP1_CI_AS
> 12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
> 13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""
> "#~@.~#" is the field terminator. We have a lot of text files with all
> kind of charachers in it. So we think this is a set that will never
> occur in our files.
> Thanks for your help!
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Your table has 15 columns, but the format file has 13 - according to
"Using a Data File with Fewer Fields" in Books Online you should still
have all the columns in the format file, but use 0 for the field
length and column number of the columns you don't want to load. You
should also not specify a terminator for those columns.

If doing that doesn't resolve the issue, perhaps you could post the
BCP command you're using, as well as the error and out files?

Simon|||I tried it with 15 columns but with the same result.

I'm wondering if you have a look at the Online Books page you mentioned they have only SQLCHAR typs for
everything. Okay it is a text file with char in it. But if I have a look at the description on the BCP
page the use SQLINT and SQLBINARY...

What is the right way here. I tried both without success.....

The bcp command is running as a batch job and I get no error. It just looks like a time out,
but no message. So I tried BULK COPY in the query analyzer now:
BULK INSERT unicode_db..customer FROM 'C:\inbox\csv_import\customer.csv'
WITH (FORMATFILE = 'C:\inbox\csv_import\formats\customer.fmt', DATAFILETYPE = 'widechar')

and this is the result:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider
did not give any information about the error.].
The statement has been terminated.

By the way, I'm using the same stuff for NON-UNICODE with a formatfile with 13 columns all SQLCHAR and
the same table (just VARCHAR instead of NVARCHAR)
and it works fine......

Is that the problem that I have a .csv file saved as unicode and the .fmt file saved as ANSI?
Is the field terminator '#~@.~#' a problem and not supported?
BCP only except ANSI format files??

Simon Hayes wrote:

> Bernd Lambertz <virus@.securiy.com> wrote in message news:<3fb38797$0$196$75868355@.news.frii.net>...
> > I have a problem with bcp and format files.
> > We changed our databases from varchar to nvarchar to support unicode. No
> > problems so fare with that. It is working fine.
> > But now I need a format file for the customer table and and it is not
> > working. It is working fine with the old DB with varchar, but with
> > nvarchar I'm not able to copy the data. The biggest problem is, that I
> > got no error message. BCP starts copying to table and finished without
> > error message.
> > This is my table:
> > CREATE TABLE [dbo].[Customer] (
> > [ID] [int] NOT NULL ,
> > [CreationTime] [datetime] NULL ,
> > [ModificationTime] [datetime] NULL ,
> > [DiscoveryTime] [datetime] NULL ,
> > [Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [Class] [int] NULL ,
> > [Subclass] [int] NULL ,
> > [Capabilities] [int] NULL ,
> > [SnapshotID] [int] NOT NULL ,
> > [CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
> > NULL ,
> > [TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
> > NOT NULL ,
> > [LocationID] [int] NULL ,
> > [MirrorID] [binary] (16) NULL ,
> > [DeleteFlag] [bit] NULL ,
> > [AdminStatus] [bit] NULL
> > ) ON [PRIMARY]
> > GO
> > and this is the format file:
> > 8.0
> > 13
> > 1 SQLINT 1 12 "#~@.~#" 1 ID ""
> > 2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
> > 3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
> > 4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
> > 5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
> > SQL_Latin1_General_CP1_CI_AS
> > 6 SQLINT 1 12 "#~@.~#" 6 Class ""
> > 7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
> > 8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
> > 9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
> > 10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
> > SQL_Latin1_General_CP1_CI_AS
> > 11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
> > SQL_Latin1_General_CP1_CI_AS
> > 12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
> > 13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""
> > "#~@.~#" is the field terminator. We have a lot of text files with all
> > kind of charachers in it. So we think this is a set that will never
> > occur in our files.
> > Thanks for your help!
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> Your table has 15 columns, but the format file has 13 - according to
> "Using a Data File with Fewer Fields" in Books Online you should still
> have all the columns in the format file, but use 0 for the field
> length and column number of the columns you don't want to load. You
> should also not specify a terminator for those columns.
> If doing that doesn't resolve the issue, perhaps you could post the
> BCP command you're using, as well as the error and out files?
> Simonsql

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 right truncation: how to ignore

Hi,
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.
I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?
I couldn't find such an option in the documentation.
Any help is greatly appreciated.
Thanks,
Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?
Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.
One possibility would be to write a program that reads the file, and
truncates the over-long rows.
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.
Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).
Kind regards
robert

bcp right truncation: how to ignore

Hi,

I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?

I couldn't find such an option in the documentation.

Any help is greatly appreciated.

Thanks,

Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?

Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.

--
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
>> I'm trying to upload a large number of log entries currently stored as
>> text files into a database table using bcp. For a few rows I get a
>> "right truncation" error and the offending rows are not uploaded to the
>> table.
>>
>> I don't want to increase the size of the table varchar fields because
>> it's only about a dozen out of almost million rows that have this
>> problem ... I want to provide an override - i.e. if a row will result
>> in truncated data, truncate but still bulk copy the offending row. Is
>> that possible?
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.

Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).

Kind regards

robert

BCP Question.

Am using bcp to upload data files to sql server. The data file contains some
duplicate values. So I used something like ">bcp pubs.dbo.stores in
"stores.txt" -m 50..." assuming, the bcp stops only after encountering more
than 50 duplicate. But BCP stops whenever it encounters the first duplicate
value!!
How to continue with BCP, ignoring the duplicate values?
Thanks.
I think it would be best to bcp into a staging table and then do an insert
with a not in subquery keying off the pk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Viga" <Viga@.discussions.microsoft.com> wrote in message
news:89E94705-F691-4DCD-8E9C-BAEEDDD5C722@.microsoft.com...
> Am using bcp to upload data files to sql server. The data file contains
some
> duplicate values. So I used something like ">bcp pubs.dbo.stores in
> "stores.txt" -m 50..." assuming, the bcp stops only after encountering
more
> than 50 duplicate. But BCP stops whenever it encounters the first
duplicate
> value!!
> How to continue with BCP, ignoring the duplicate values?
> Thanks.

BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1SQLCHAR01""1RTSQL_Latin1_General_CP1_CI_AS
2SQLCHAR04""2VERSIONNULL
3SQLCHAR010""3TLIDNULL
4SQLCHAR01""4SIDE1NULL
5SQLCHAR01""5SOURCESQL_Latin1_General_CP1_CI_AS
6SQLCHAR02""6FEDIRPSQL_Latin1_General_CP1_CI_AS
7SQLCHAR030""7FENAMESQL_Latin1_General_CP1_CI_AS
8SQLCHAR04""8FETYPESQL_Latin1_General_CP1_CI_AS
9SQLCHAR02""9FEDIRSSQL_Latin1_General_CP1_CI_AS
10SQLCHAR03""10CFCCSQL_Latin1_General_CP1_CI_AS
11SQLCHAR011""11FRADDLNULL
12SQLCHAR011""12TOADDLNULL
13SQLCHAR011""13FRADDRNULL
14SQLCHAR011""14TOADDRNULL
15SQLCHAR01""15FRIADDLSQL_Latin1_General_CP1_CI_AS
16SQLCHAR01""16TOIADDLSQL_Latin1_General_CP1_CI_AS
17SQLCHAR01""17FRIADDRSQL_Latin1_General_CP1_CI_AS
18SQLCHAR01""18TOIADDRSQL_Latin1_General_CP1_CI_AS
19SQLCHAR05""19ZIPLNULL
20SQLCHAR05""20ZIPRNULL
21SQLCHAR05""21AIANHHFPLNULL
22SQLCHAR05""22AIANHHFPRNULL
23SQLCHAR01""23AIHHTLILSQL_Latin1_General_CP1_CI_AS
24SQLCHAR01""24AIHHTLIRSQL_Latin1_General_CP1_CI_AS
25SQLCHAR01""25CENSUS1SQL_Latin1_General_CP1_CI_AS
26SQLCHAR01""26CENSUS2SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR02""27STATELNULL
28SQLCHAR02""28STATERNULL
29SQLCHAR03""29COUNTYLNULL
30SQLCHAR03""30COUNTYRNULL
31SQLCHAR05""31COUSUBLNULL
32SQLCHAR05""32COUSUBRNULL
33SQLCHAR05""33SUBMCDLNULL
34SQLCHAR05""34SUBMCDRNULL
35SQLCHAR05""35PLACELNULL
36SQLCHAR05""36PLACERNULL
37SQLCHAR06""37TRACTLNULL
38SQLCHAR06""38TRACTRNULL
39SQLCHAR04""39BLOCKLNULL
40SQLCHAR04""40BLOCKRNULL
41SQLCHAR010""41FRLONGNULL
42SQLCHAR0 9""42FRLATNULL
43SQLCHAR010""43TOLONGNULL
44SQLCHAR09"\r\n"44TOLATNULL
The error message indicates an I/O error, are there any network problems, can
SQL Server see the file, does SQL Server have permissions to access the file?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:

> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1SQLCHAR01""1RTSQL_Latin1_General_CP1_CI_AS
> 2SQLCHAR04""2VERSIONNULL
> 3SQLCHAR010""3TLIDNULL
> 4SQLCHAR01""4SIDE1NULL
> 5SQLCHAR01""5SOURCESQL_Latin1_General_CP1_CI_AS
> 6SQLCHAR02""6FEDIRPSQL_Latin1_General_CP1_CI_AS
> 7SQLCHAR030""7FENAMESQL_Latin1_General_CP1_CI_AS
> 8SQLCHAR04""8FETYPESQL_Latin1_General_CP1_CI_AS
> 9SQLCHAR02""9FEDIRSSQL_Latin1_General_CP1_CI_AS
> 10SQLCHAR03""10CFCCSQL_Latin1_General_CP1_CI_AS
> 11SQLCHAR011""11FRADDLNULL
> 12SQLCHAR011""12TOADDLNULL
> 13SQLCHAR011""13FRADDRNULL
> 14SQLCHAR011""14TOADDRNULL
> 15SQLCHAR01""15FRIADDLSQL_Latin1_General_CP1_CI_AS
> 16SQLCHAR01""16TOIADDLSQL_Latin1_General_CP1_CI_AS
> 17SQLCHAR01""17FRIADDRSQL_Latin1_General_CP1_CI_AS
> 18SQLCHAR01""18TOIADDRSQL_Latin1_General_CP1_CI_AS
> 19SQLCHAR05""19ZIPLNULL
> 20SQLCHAR05""20ZIPRNULL
> 21SQLCHAR05""21AIANHHFPLNULL
> 22SQLCHAR05""22AIANHHFPRNULL
> 23SQLCHAR01""23AIHHTLILSQL_Latin1_General_CP1_CI_AS
> 24SQLCHAR01""24AIHHTLIRSQL_Latin1_General_CP1_CI_AS
> 25SQLCHAR01""25CENSUS1SQL_Latin1_General_CP1_CI_AS
> 26SQLCHAR01""26CENSUS2SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR02""27STATELNULL
> 28SQLCHAR02""28STATERNULL
> 29SQLCHAR03""29COUNTYLNULL
> 30SQLCHAR03""30COUNTYRNULL
> 31SQLCHAR05""31COUSUBLNULL
> 32SQLCHAR05""32COUSUBRNULL
> 33SQLCHAR05""33SUBMCDLNULL
> 34SQLCHAR05""34SUBMCDRNULL
> 35SQLCHAR05""35PLACELNULL
> 36SQLCHAR05""36PLACERNULL
> 37SQLCHAR06""37TRACTLNULL
> 38SQLCHAR06""38TRACTRNULL
> 39SQLCHAR04""39BLOCKLNULL
> 40SQLCHAR04""40BLOCKRNULL
> 41SQLCHAR010""41FRLONGNULL
> 42SQLCHAR0 9""42FRLATNULL
> 43SQLCHAR010""43TOLONGNULL
> 44SQLCHAR09"\r\n"44TOLATNULL
>

BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 4 "" 2 VERSION NULL
3 SQLCHAR 0 10 "" 3 TLID NULL
4 SQLCHAR 0 1 "" 4 SIDE1 NULL
5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 FRADDL NULL
12 SQLCHAR 0 11 "" 12 TOADDL NULL
13 SQLCHAR 0 11 "" 13 FRADDR NULL
14 SQLCHAR 0 11 "" 14 TOADDR NULL
15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 5 "" 19 ZIPL NULL
20 SQLCHAR 0 5 "" 20 ZIPR NULL
21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 2 "" 27 STATEL NULL
28 SQLCHAR 0 2 "" 28 STATER NULL
29 SQLCHAR 0 3 "" 29 COUNTYL NULL
30 SQLCHAR 0 3 "" 30 COUNTYR NULL
31 SQLCHAR 0 5 "" 31 COUSUBL NULL
32 SQLCHAR 0 5 "" 32 COUSUBR NULL
33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
35 SQLCHAR 0 5 "" 35 PLACEL NULL
36 SQLCHAR 0 5 "" 36 PLACER NULL
37 SQLCHAR 0 6 "" 37 TRACTL NULL
38 SQLCHAR 0 6 "" 38 TRACTR NULL
39 SQLCHAR 0 4 "" 39 BLOCKL NULL
40 SQLCHAR 0 4 "" 40 BLOCKR NULL
41 SQLCHAR 0 10 "" 41 FRLONG NULL
42 SQLCHAR 0 9 "" 42 FRLAT NULL
43 SQLCHAR 0 10 "" 43 TOLONG NULL
44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULLThe error message indicates an I/O error, are there any network problems, can
SQL Server see the file, does SQL Server have permissions to access the file?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:
> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 4 "" 2 VERSION NULL
> 3 SQLCHAR 0 10 "" 3 TLID NULL
> 4 SQLCHAR 0 1 "" 4 SIDE1 NULL
> 5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 11 "" 11 FRADDL NULL
> 12 SQLCHAR 0 11 "" 12 TOADDL NULL
> 13 SQLCHAR 0 11 "" 13 FRADDR NULL
> 14 SQLCHAR 0 11 "" 14 TOADDR NULL
> 15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
> 17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
> 19 SQLCHAR 0 5 "" 19 ZIPL NULL
> 20 SQLCHAR 0 5 "" 20 ZIPR NULL
> 21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
> 22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
> 23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
> 24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
> 26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR 0 2 "" 27 STATEL NULL
> 28 SQLCHAR 0 2 "" 28 STATER NULL
> 29 SQLCHAR 0 3 "" 29 COUNTYL NULL
> 30 SQLCHAR 0 3 "" 30 COUNTYR NULL
> 31 SQLCHAR 0 5 "" 31 COUSUBL NULL
> 32 SQLCHAR 0 5 "" 32 COUSUBR NULL
> 33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
> 34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
> 35 SQLCHAR 0 5 "" 35 PLACEL NULL
> 36 SQLCHAR 0 5 "" 36 PLACER NULL
> 37 SQLCHAR 0 6 "" 37 TRACTL NULL
> 38 SQLCHAR 0 6 "" 38 TRACTR NULL
> 39 SQLCHAR 0 4 "" 39 BLOCKL NULL
> 40 SQLCHAR 0 4 "" 40 BLOCKR NULL
> 41 SQLCHAR 0 10 "" 41 FRLONG NULL
> 42 SQLCHAR 0 9 "" 42 FRLAT NULL
> 43 SQLCHAR 0 10 "" 43 TOLONG NULL
> 44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULL
>

BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 4 "" 2 VERSION NULL
3 SQLCHAR 0 10 "" 3 TLID NULL
4 SQLCHAR 0 1 "" 4 SIDE1 NULL
5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 FRADDL NULL
12 SQLCHAR 0 11 "" 12 TOADDL NULL
13 SQLCHAR 0 11 "" 13 FRADDR NULL
14 SQLCHAR 0 11 "" 14 TOADDR NULL
15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 5 "" 19 ZIPL NULL
20 SQLCHAR 0 5 "" 20 ZIPR NULL
21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_A
S
24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_A
S
25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 2 "" 27 STATEL NULL
28 SQLCHAR 0 2 "" 28 STATER NULL
29 SQLCHAR 0 3 "" 29 COUNTYL NULL
30 SQLCHAR 0 3 "" 30 COUNTYR NULL
31 SQLCHAR 0 5 "" 31 COUSUBL NULL
32 SQLCHAR 0 5 "" 32 COUSUBR NULL
33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
35 SQLCHAR 0 5 "" 35 PLACEL NULL
36 SQLCHAR 0 5 "" 36 PLACER NULL
37 SQLCHAR 0 6 "" 37 TRACTL NULL
38 SQLCHAR 0 6 "" 38 TRACTR NULL
39 SQLCHAR 0 4 "" 39 BLOCKL NULL
40 SQLCHAR 0 4 "" 40 BLOCKR NULL
41 SQLCHAR 0 10 "" 41 FRLONG NULL
42 SQLCHAR 0 9 "" 42 FRLAT NULL
43 SQLCHAR 0 10 "" 43 TOLONG NULL
44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULLThe error message indicates an I/O error, are there any network problems, ca
n
SQL Server see the file, does SQL Server have permissions to access the file
?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:

> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 4 "" 2 VERSION NULL
> 3 SQLCHAR 0 10 "" 3 TLID NULL
> 4 SQLCHAR 0 1 "" 4 SIDE1 NULL
> 5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 11 "" 11 FRADDL NULL
> 12 SQLCHAR 0 11 "" 12 TOADDL NULL
> 13 SQLCHAR 0 11 "" 13 FRADDR NULL
> 14 SQLCHAR 0 11 "" 14 TOADDR NULL
> 15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
> 17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
> 19 SQLCHAR 0 5 "" 19 ZIPL NULL
> 20 SQLCHAR 0 5 "" 20 ZIPR NULL
> 21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
> 22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
> 23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_A
S
> 24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_A
S
> 25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
> 26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR 0 2 "" 27 STATEL NULL
> 28 SQLCHAR 0 2 "" 28 STATER NULL
> 29 SQLCHAR 0 3 "" 29 COUNTYL NULL
> 30 SQLCHAR 0 3 "" 30 COUNTYR NULL
> 31 SQLCHAR 0 5 "" 31 COUSUBL NULL
> 32 SQLCHAR 0 5 "" 32 COUSUBR NULL
> 33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
> 34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
> 35 SQLCHAR 0 5 "" 35 PLACEL NULL
> 36 SQLCHAR 0 5 "" 36 PLACER NULL
> 37 SQLCHAR 0 6 "" 37 TRACTL NULL
> 38 SQLCHAR 0 6 "" 38 TRACTR NULL
> 39 SQLCHAR 0 4 "" 39 BLOCKL NULL
> 40 SQLCHAR 0 4 "" 40 BLOCKR NULL
> 41 SQLCHAR 0 10 "" 41 FRLONG NULL
> 42 SQLCHAR 0 9 "" 42 FRLAT NULL
> 43 SQLCHAR 0 10 "" 43 TOLONG NULL
> 44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULL
>sql

BCP Problem

I have some bcp archive files without the data format. Is there a way to get
the data format from the archive files.
ThanksHi
This is not possible. You may want to use DTS/Import wizard to graphically
assign data to columns. If you know your destination table maps onto the dat
a
file or have access to the source table, then you can use the format option
to create a format file from the table definition.
John
"Tom" wrote:

> I have some bcp archive files without the data format. Is there a way to g
et
> the data format from the archive files.
> Thanks|||No you cant get it from the output file but if you know what table the data
came from you can use the code below to make a format file of the table so
you can reassociate the columns with data values.
EXEC master.dbo.xp_cmdshell 'BCP dbname.dbo.tablename FORMAT -Usa -Ppass -N
-fc:\TBL_Format.fmt'
From what I have experienced with format files the column width that gets
outputted from using the format option does not always match whats in the
table. From my experience the format option outputs more width than what is
stored in the table its self.
Hope this helps.
"Tom" wrote:

> I have some bcp archive files without the data format. Is there a way to g
et
> the data format from the archive files.
> Thanks