bcp_bind

Hi experts,
I created a program loading data from diferent sources to SQL Server
database by using bcp_... bulk copy functions with some variables.(not from a
file)
My problem is: Now, a table, "Person", has 10 columns. I bound 10 variables
to the 10 columns, and load data is fine. But if in the future the table
needs increase 1 column to 11 columns and run the existing program, it will
get the error "HY000--
Not enough columns bound.". That means "bcp_bind" function has to bind to
all the columns of the table. otherwise the binding will fail. If load data
from a file, I can use "bcp_columns" function to specify column number. But I
insert data from variables, how can I bind to only partial columns of a table?
Thank you.
MCH
I have the same question. Did you figure our how to do so?
Thank you very much
Yev
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
|||always bind to a view of a table, not directly to a table, so you can control
the columns you want to bind.
MCH
"Yevheniy" wrote:

> I have the same question. Did you figure our how to do so?
> Thank you very much
> Yev
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
>
sql

bcp_bind

Hi experts,
I created a program loading data from diferent sources to SQL Server
database by using bcp_... bulk copy functions with some variables.(not from a
file)
My problem is: Now, a table, "Person", has 10 columns. I bound 10 variables
to the 10 columns, and load data is fine. But if in the future the table
needs increase 1 column to 11 columns and run the existing program, it will
get the error "HY000--
Not enough columns bound.". That means "bcp_bind" function has to bind to
all the columns of the table. otherwise the binding will fail. If load data
from a file, I can use "bcp_columns" function to specify column number. But I
insert data from variables, how can I bind to only partial columns of a table?
Thank you.
MCHI have the same question. Did you figure our how to do so?
Thank you very much
Yev
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com|||always bind to a view of a table, not directly to a table, so you can control
the columns you want to bind.
MCH
"Yevheniy" wrote:
> I have the same question. Did you figure our how to do so?
> Thank you very much
> Yev
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
>

bcp to import data into a partitioned view

I created a partitioned view as
CREATE VIEW empMaster AS
SELECT * FROM emp1
UNION ALL
SELECT * FROM emp2
(where emp1 and emp2 have check constraints on their primary key)
I tried to run bcp to import data into this view.
i get an error message :"Partitioned view 'empMaster' is not updatable as
the target of a bulk operation."
(iam able to do a simple INSERT into this view though)
Does this mean that a bulk operation to a partitioned view is not possible ?
thanks,
Vivek T S
Member Technical Staff (Inucom)
Vivek,
I believe that is correct. You will have to split your bulk import data and
send it to the correct tables. You also should read the following article:
http://support.microsoft.com/default...b;en-us;286134
Russell Fields
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:5FF78194-3CC7-4BA5-8886-3F036A9C99C4@.microsoft.com...
> I created a partitioned view as
> CREATE VIEW empMaster AS
> SELECT * FROM emp1
> UNION ALL
> SELECT * FROM emp2
> (where emp1 and emp2 have check constraints on their primary key)
> I tried to run bcp to import data into this view.
> i get an error message :"Partitioned view 'empMaster' is not updatable as
> the target of a bulk operation."
> (iam able to do a simple INSERT into this view though)
> Does this mean that a bulk operation to a partitioned view is not possible
?
> thanks,
> --
> Vivek T S
> Member Technical Staff (Inucom)

bcp to import data into a partitioned view

I created a partitioned view as
CREATE VIEW empMaster AS
SELECT * FROM emp1
UNION ALL
SELECT * FROM emp2
(where emp1 and emp2 have check constraints on their primary key)
I tried to run bcp to import data into this view.
i get an error message :"Partitioned view 'empMaster' is not updatable as
the target of a bulk operation."
(iam able to do a simple INSERT into this view though)
Does this mean that a bulk operation to a partitioned view is not possible ?
thanks,
--
Vivek T S
Member Technical Staff (Inucom)Vivek,
I believe that is correct. You will have to split your bulk import data and
send it to the correct tables. You also should read the following article:
http://support.microsoft.com/defaul...kb;en-us;286134
Russell Fields
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:5FF78194-3CC7-4BA5-8886-3F036A9C99C4@.microsoft.com...
> I created a partitioned view as
> CREATE VIEW empMaster AS
> SELECT * FROM emp1
> UNION ALL
> SELECT * FROM emp2
> (where emp1 and emp2 have check constraints on their primary key)
> I tried to run bcp to import data into this view.
> i get an error message :"Partitioned view 'empMaster' is not updatable as
> the target of a bulk operation."
> (iam able to do a simple INSERT into this view though)
> Does this mean that a bulk operation to a partitioned view is not possible
?
> thanks,
> --
> Vivek T S
> Member Technical Staff (Inucom)sql

BCP taking very long time.

I have a table which has near 50million records.
The .dat file created by BCP queryout is of around 51GB.
I am forming the BCP queryout and BCP IN commands in a stored proc and executing the same using xp_cmdshell.
I am using the below BCP options :
/T /b60000000 /a16384 /w /t}@.!{ /r}^#{\n /h"TABLOCK"

With above the QueryOut is taking around 8 hrs and BCP IN another 10 hrs. These figures are not very healthy.

The database recovery mode is set as simple.
Server config: Windows server 2003 X64 edition, with 16 gb RAM.
Any pointers on what could be the reason for this?

The table structure is as below:
CREATE TABLE XXX.dbo.ZZZ (
A nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,B nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,C nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,D datetime
,E numeric(22,7)
,F nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,G nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,H nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,I nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,J nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
,K nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS
,L nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,M nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,N nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,O nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,P nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,Q datetime
,R nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,S nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,T nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,U datetime
,V datetime
,W nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,X nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Y nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Z nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,AA nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,BB nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,CC nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,DD nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,EE Datetime
,FF Datetime
,GG Datetime
,HH Datetime
)

According to the numbers, the data throughput for BCP OUT was ~1.8 MB/sec, for BCP IN - 1.4 MB/sec. Assuming that no blocking is involved the fact that the difference between BCP IN and BCP OUT throughput is only about 20% might be an indication that there is something wrong with the disk IO (or the network IO if you ran BCP from a remote system or if the BCP data files were located on a remote share) since you have plenty of CPU power to provide much higher import/export rates (for a reasonably balanced modern commodity system with gigabit network it can range from 20 to 70 MB/sec). The amount of available memory is not that critical thanks to the amount of data you import/export.

In other words, you need to indentify the bottleneck - the available data strongly suggests that it's the IO, so I would start with inspecting the disk subsystem where you store the BCP data files and/or the network if you store the files on a remote share.

|||

Tenzig,

Thanks for your response !
You are right in guessing that I am running BCP from a remote system. However my data files are stored locally.
However what is intriguing is that the same set up is giving higher throughput for similar process on other tables. So the network can't be blamed as the bottleneck.
I am inspecting the disk subsystem where the data files are stored.
One more thing I observed that the M/c behaves like crazy while running few similar processes simultaneously, with 100% CPU usage and max. PF usage.
What could trigger that?

|||

Did you use the same disk to place the BCP data files for the case where you observed higher throughput when importing/exporting data from other tables? Could you also collect the CPU and IO performance counter logs for the disk where the BCP data files are located for both the "slow" and the "fast" cases? Without the actual numbers it's hard to make any conclusions.

BCP taking very long time.

I have a table which has near 50million records.
The .dat file created by BCP queryout is of around 51GB.
I am forming the BCP queryout and BCP IN commands in a stored proc and executing the same using xp_cmdshell.
I am using the below BCP options :
/T /b60000000 /a16384 /w /t}@.!{ /r}^#{\n /h"TABLOCK"

With above the QueryOut is taking around 8 hrs and BCP IN another 10 hrs. These figures are not very healthy.

The database recovery mode is set as simple.
Server config: Windows server 2003 X64 edition, with 16 gb RAM.
Any pointers on what could be the reason for this?

The table structure is as below:
CREATE TABLE XXX.dbo.ZZZ (
A nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,B nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,C nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,D datetime
,E numeric(22,7)
,F nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,G nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,H nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,I nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,J nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
,K nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS
,L nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,M nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,N nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,O nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,P nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,Q datetime
,R nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,S nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,T nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,U datetime
,V datetime
,W nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,X nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Y nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Z nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,AA nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,BB nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,CC nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,DD nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,EE Datetime
,FF Datetime
,GG Datetime
,HH Datetime
)

According to the numbers, the data throughput for BCP OUT was ~1.8 MB/sec, for BCP IN - 1.4 MB/sec. Assuming that no blocking is involved the fact that the difference between BCP IN and BCP OUT throughput is only about 20% might be an indication that there is something wrong with the disk IO (or the network IO if you ran BCP from a remote system or if the BCP data files were located on a remote share) since you have plenty of CPU power to provide much higher import/export rates (for a reasonably balanced modern commodity system with gigabit network it can range from 20 to 70 MB/sec). The amount of available memory is not that critical thanks to the amount of data you import/export.

In other words, you need to indentify the bottleneck - the available data strongly suggests that it's the IO, so I would start with inspecting the disk subsystem where you store the BCP data files and/or the network if you store the files on a remote share.

|||

Tenzig,

Thanks for your response !
You are right in guessing that I am running BCP from a remote system. However my data files are stored locally.
However what is intriguing is that the same set up is giving higher throughput for similar process on other tables. So the network can't be blamed as the bottleneck.
I am inspecting the disk subsystem where the data files are stored.
One more thing I observed that the M/c behaves like crazy while running few similar processes simultaneously, with 100% CPU usage and max. PF usage.
What could trigger that?

|||

Did you use the same disk to place the BCP data files for the case where you observed higher throughput when importing/exporting data from other tables? Could you also collect the CPU and IO performance counter logs for the disk where the BCP data files are located for both the "slow" and the "fast" cases? Without the actual numbers it's hard to make any conclusions.

bcp syntax error help?

I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
ThanksI have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."

Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'

EXEC master.dbo.xp_cmdshell @.query

EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'

SELECT @.@.ERROR As ErrorNumber
END

Could somebody help me and suggest something as I am going crazy.....here...
Thanks

I have realized how to go around this thanks to all

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
Dylan
Have you checked the event log to verify you are not getting Disk I/O
errors?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>
|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>
|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>
sql

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BINeen">
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BINeen">
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BINeen">
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BINeen">
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BINeen">
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created[vbcol=seagreen]
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...
if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> > Hi Guys
> >
> > I have a client who was having issues importing from a text file
(Created
> by
> > there mainframe ) into SQL server, using DTS it would only import a
> certain
> > number of the rows (like 10 000 out of a million) we hare now trying to
> use
> > BCP and see the following problem below:
> >
> >
> >
> > C:\>bcp CARD..AVAAF_ABSA_Employee in
> > c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
> >
> >
> >
> > netjie -Usa -Ppassword
> >
> >
> >
> > SQLState = S1000, NativeError = 0
> >
> >
> >
> > Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> > format file
> >
> >
> >
> > This is the format of the clients format file:
> >
> >
> >
> > 8.0
> >
> > 25
> >
> > 1 SQLCHAR 2 2 "\0" 0 num1
> > SQL_Latin1_General_Cp437_BIN
> >
> > 2 SQLCHAR 2 7 "\0" 10 Employee_no
> > SQL_Latin1_General_Cp437_BIN
> >
> > 3 SQLCHAR 2 7 "\0" 1 AB_Number
> > SQL_Latin1_General_Cp437_BIN
> >
> > 4 SQLCHAR 2 30 "\0" 3 Surname
> > SQL_Latin1_General_Cp437_BIN
> >
> > 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> >
> > 6 SQLCHAR 2 10 "\0" 2 Initials
> > SQL_Latin1_General_Cp437_BIN
> >
> > 7 SQLCHAR 2 20 "\0" 0 num2
> > SQL_Latin1_General_Cp437_BIN
> >
> > 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> > SQL_Latin1_General_Cp437_BIN
> >
> > 9 SQLCHAR 2 12 "\0" 0 num3
> > SQL_Latin1_General_Cp437_BIN
> >
> > 10 SQLCHAR 2 10 "\0" 0 num4
> > SQL_Latin1_General_Cp437_BIN
> >
> > 11 SQLCHAR 2 40 "\0" 0 num5
> > SQL_Latin1_General_Cp437_BIN
> >
> > 12 SQLCHAR 2 4 "\0" 0 num6
> > SQL_Latin1_General_Cp437_BIN
> >
> > 13 SQLCHAR 2 40 "\0" 0 num7
> > SQL_Latin1_General_Cp437_BIN
> >
> > 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 15 SQLCHAR 2 20 "\0" 7 FaxNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 16 SQLCHAR 2 12 "\0" 6 CellularNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 17 SQLCHAR 2 56 "\0" 0 num8
> > SQL_Latin1_General_Cp437_BIN
> >
> > 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
> SQL_Latin1_General_Cp437_BIN
> >
> > 19 SQLCHAR 2 40 "\0" 0 num9
> > SQL_Latin1_General_Cp437_BIN
> >
> > 20 SQLCHAR 2 10 "\0" 0 num10
> > SQL_Latin1_General_Cp437_BIN
> >
> > 21 SQLCHAR 2 4 "\0" 0 num11
> > SQL_Latin1_General_Cp437_BIN
> >
> > 22 SQLCHAR 2 40 "\0" 0 num12
> > SQL_Latin1_General_Cp437_BIN
> >
> > 23 SQLCHAR 2 40 "\0" 0 num13
> > SQL_Latin1_General_Cp437_BIN
> >
> > 24 SQLCHAR 2 2 "\0" 0 num14
> > SQL_Latin1_General_Cp437_BIN
> >
> > 25 SQLCHAR 2 16 "\r\n" 15 Province
> SQL_Latin1_General_Cp437_BIN
> >
> >
> >
> >
> >
> > I cannot find any info on this error message , has anyone got any ideas,
> > this is SQL server 2000 SP3
> >
> >
> >
> > Thanks
> >
> > Dylan
> >
> >
>

BCP Problem

Hi all,

Im using bcp utility to copy all the data from a table to file. The file is created as it is. But i cant open that bcp file. How can i open this file.

Thanks in advance,
Srinivas r.

Quote:

Originally Posted by rsrinivasan

Hi all,

Im using bcp utility to copy all the data from a table to file. The file is created as it is. But i cant open that bcp file. How can i open this file.

Thanks in advance,
Srinivas r.

What format is your output file?
This works for me (mixed authentication):
bcp <dbname>.<schema>.<table-name> out "C:\Test.dat" -c -U <user-name> -P <password> -S <server-name\instance-name>

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

BCP Import Help

I have a text file that I must import into a table I created but am having terrible difficulty trying to use the command line BCP utility to do so. Can anyone please tell me how to do this?

Text file and table properties below:

Text File

1
Untitled
Mark Rothko
Oil
1961
5'9"x4'2"
2
The Letter
Jan Vermeer
Oil
1666
1'5.25"x1'3.75"
3
Four Apostles
Albrecht Durer
Oil
1526
7'1"x2'6"
4
Big Self-Portrait
Chuck Close
Acrylic
1968
8'11"x6'11"x2
5
Three Angels
Andrei Rublyev
Tempura on wood
1410
4'8"x3'9"
6
Voltaire
Jean-Antoine Houdon
Marble
1781
7
Jaguar Devouring a Hare
Antoine-Louis Barye
Bronze
1851
1'4"x3'1"
8
The Peacock Skirt
Aubrey Beardsley
Pen and Ink
1894
9
Untitled Film Still #35
Cindy Sherman
Black-and-white photograph
1979
10"x8"
10
Reclining Figure
Henry Moore
Elm wood
1939
3'1"x2'6"

Table Properties

tbl_Items
ID(int) - Primary Key
Title (varchar - 50)
Owner (varchar - 50)
Canvas (varchar - 20)
Copyright (char - 4)
Sized (varchar - 20)

Can you use DTS? If so, that would be a lot easier. It will create all the code for you.

BCP Help - Incorrect host-column number found in BCP format-file

I've used BCP before, so I understand how to use it for the most part.
But, I'm trying to do something different.
I created a fmt file from my table. But, what I want is to only import
say column 50. So, this is how I changed it.
8.0
50
50 SQLCHAR 0 510 "\t" 41 COUNTY
That was just a guess.
Originally it was
8.0
108
then all the colums.
The text file I am processing includes all 108 columns.
So, my question is...Is what I am trying to do possible using the same
text file, but only wanting to import 1 column? (Please understand, I
will really be importing about 50 but this is an example)
*** Sent via Developersdex http://www.examnotes.net ***[posted and mailed, please reply in news]
Joey Martin (joey@.infosmiths.net) writes:
> I've used BCP before, so I understand how to use it for the most part.
> But, I'm trying to do something different.
> I created a fmt file from my table. But, what I want is to only import
> say column 50. So, this is how I changed it.
> 8.0
> 50
> 50 SQLCHAR 0 510 "\t" 41 COUNTY
> That was just a guess.
> Originally it was
> 8.0
> 108
> then all the colums.
> The text file I am processing includes all 108 columns.
> So, my question is...Is what I am trying to do possible using the same
> text file, but only wanting to import 1 column? (Please understand, I
> will really be importing about 50 but this is an example)
No, this is perfectly possible. If you already have a format file with
all the 108 fields, just put a 0 after the field delimiter after the
fields you don't care about. This means that this field in the file does
not map to a database column.
A non-zero value, for instance 41 as above, means that you want to
map that field to the 41th column. The name that follows the number
is informational only, and carries no meaning.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP genric error

I am trying to BCP locally, first I created the format file using
"BCP...nul..." and then issued the following bcp phrase :
bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
I am getting the genric error
**********************************
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
************************************
The sql server and sqlserver agent are running under a domain account which
is not in the local admin group
I changed this and added him to the local admin group, I get the same error
Thanks for your help
Hi I figured out what is the problem. It was indicating the fullpath for the
format file.
But now I am getting nothing entered in the table, when I issue the bcp I get
*****************
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
********************
So what is missing?

> I am trying to BCP locally, first I created the format file using
> "BCP...nul..." and then issued the following bcp phrase :
> bcp myDB.dbo.WEBFARM_DailyBandWidth_temp in
> D:\Tracing\Charts\Dailybandwidth_ex061212.txt -T -f DailybandwidthOut.fmt
>
> I am getting the genric error
> **********************************
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
> ************************************
> The sql server and sqlserver agent are running under a domain account which
> is not in the local admin group
> I changed this and added him to the local admin group, I get the same error
> Thanks for your help
|||SalamElias (eliassal@.online.nospam) writes:
> Hi I figured out what is the problem. It was indicating the fullpath for
> the format file. But now I am getting nothing entered in the table, when
> I issue the bcp I get
> *****************
> Starting copy...
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.) Total : 1
> ********************
> So what is missing?
My interpretation is that the file is empty.
How does the format file look like? What sort file is it? A text file?
Binary?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi,
My understanding of your issue is that:
You used BCP to transfer data, however you found zero rows copied.
If I have misunderstood, please let me know.
I am afraid that the information here is not enough to judge what caused
this issue.
Please first check the database table type definition matches your format
file; then check if there are some special characters in your data file.
I would like to provide you the bcp usage articles for your reference:
bcp Utility
http://msdn2.microsoft.com/en-us/library/ms162802.aspx
Creating a Format File
http://msdn2.microsoft.com/en-us/library/ms191516.aspx
Also, you may mail me (changliw@.microsoft.com) your table definition, the
data file and the format file so that I can reproduce your issue and find a
resolution.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, I have sent you an email
Thanks
"Charles Wang[MSFT]" wrote:

> Hi,
> My understanding of your issue is that:
> You used BCP to transfer data, however you found zero rows copied.
> If I have misunderstood, please let me know.
> I am afraid that the information here is not enough to judge what caused
> this issue.
> Please first check the database table type definition matches your format
> file; then check if there are some special characters in your data file.
> I would like to provide you the bcp usage articles for your reference:
> bcp Utility
> http://msdn2.microsoft.com/en-us/library/ms162802.aspx
> Creating a Format File
> http://msdn2.microsoft.com/en-us/library/ms191516.aspx
> Also, you may mail me (changliw@.microsoft.com) your table definition, the
> data file and the format file so that I can reproduce your issue and find a
> resolution.
> Thanks for using Microsoft Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
|||Hi,
I got it.
This issue was caused by your format file not matching your data file. I
correct it with the following:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLNUMERIC 0 18 "|" 3 Incoming_Traffic
""
4 SQLNUMERIC 0 18 "\r\n" 4 Outgoing_Traffic
""
Please try it and see if this issue persists at your side.
Thanks for using Microsoft Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||Hi Salam,
It's my bad. I am so sorry that I omit checking the table. It is indeed a
strange behavior that the last two fields are populated with zero.
I am afraid that I need to consult our SQL team on this and I will let you
know their response as soon as possible.
For now, per my test, you can use SQLCHAR for all the columns:
9.0
4
1 SQLCHAR 0 10 "|" 1 Server_Name
French_CI_AS
2 SQLCHAR 0 10 "|" 2 Date_Day
French_CI_AS
3 SQLCHAR 0 9 "|" 3 Incoming_Traffic
""
4 SQLCHAR 0 9 "\r\n" 4 Outgoing_Traffic
""
The type SQLCHAR will be automatically converted to the database data type
when the data are imported into the table.
I am sorry again for bringing you any inconvenience. Sincerely hope this
suggestion can help resolve your problem.
Please feel free to let me know if you have any other questions or
concerns. It is my pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support
|||Hi Elias,
I am sorry for this late response. I just got the confirmation that the
type SQLNUMERIC is only for SQL native data type.
For a plain text file, all data types in the BCP format file should be
SQLCHAR.
For the explanation, please see Cindy's response as following.
Please feel free to let me know if need further assistance on this issue.
It's always our pleasure to be of assistance.
Merry Christmas!
Charles Wang
Microsoft Online Community Support

BCP Format File Issues

Hi, All,
I'm having problems with BCP and a format file that I created. The current version of BCP is 8.00.382 and the format file looks like this:
8.0
2
1SQL VARCHAR018"||" 1PRONUM
2SQL VARCHAR030"||"2DESCR
When I run BCP it gives me the following error:
Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BCP format file
I then tried modifying the version to 8.00.382 and it then gives me the following error:
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file
I'm assuming that this is because it doesn't like the 8.00.338 but I really am not sure. If anyone can help it would be greatly appreciated.
Cheers,
Neil Guyette
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
nguyette@.gemssensors.com wrote:

>Hi, All,
>I'm having problems with BCP and a format file that I created. The current version of BCP is 8.00.382 and the format file looks like this:
>8.0
>2
>1SQL VARCHAR018"||" 1PRONUM
>2SQL VARCHAR030"||"2DESCR
>
The problem is not with the 8.0 version number. Your file should
probably look more like this:
8.0
2
1SQLCHAR018"||" 1PRONUM""
2SQLCHAR030"\r\n"2DESCR"Arabic_CI_AS"
1. If your host file is a text file, use SQLCHAR (no spaces in that) as
the type.
2. Add a collation column for each column (use "" if you don't want to
specify one)
3. || for the column 2 terminator might be correct, but if your data
file has usual line breaks that you don't want to import, you need to
specify that.
The "Using Format Files" topic in Books Online describes the basic
format file layout, and for many good samples, go to
http://groups.google.co.uk/groups?q=...ie+format+file
Steve Kass
Drew University

>When I run BCP it gives me the following error:
>Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BCP format file
>I then tried modifying the version to 8.00.382 and it then gives me the following error:
>Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file
>I'm assuming that this is because it doesn't like the 8.00.338 but I really am not sure. If anyone can help it would be greatly appreciated.
>Cheers,
>Neil Guyette
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>

BCP error - missing data

After loading the BCP files that are created during the trigger/
reporting events I've noticed that the data in the table is missing
records. I've also noticed that the missing records (records in the
table but not in the BCP out files) seem to occur in contiguous
blocks. Since the complete set of records exists in the table, I
assume this points to an issue in the way the TableUpdate script/
Triggers interact with the system. But i tried to take out the bcp
procedure, do test on trigger, then no data missing, So, I think the
problem is still on bcp part. Could you help me with that?

CREATE TABLE [EventUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins

bcp script:

bcp "select a.* from w..eventlog a, w..eventupdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <userwithaccess-P <password-S doserver -f EventLog.fmt

Thanks in advance for your reply!(danceli@.gmail.com) writes:

Quote:

Originally Posted by

After loading the BCP files that are created during the trigger/
reporting events I've noticed that the data in the table is missing
records. I've also noticed that the missing records (records in the
table but not in the BCP out files) seem to occur in contiguous
blocks. Since the complete set of records exists in the table, I
assume this points to an issue in the way the TableUpdate script/
Triggers interact with the system. But i tried to take out the bcp
procedure, do test on trigger, then no data missing, So, I think the
problem is still on bcp part. Could you help me with that?

Please, could you take that again, and more slowly this time? Keep in
mind that people reading this newsgroup does not know about your system.

You are missing data but where? You appear to extract data with BCP, do
you load it anywhere else?

One thing I can note:

Quote:

Originally Posted by

create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
>
>bcp "select a.* from w..eventlog a, w..eventupdate b where
>a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
>and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
>U <userwithaccess-P <password-S doserver -f EventLog.fmt

The BCP query has a condition on the column UpdatePass which is not
set in the trigger. So from what you have posted, I would not expect
anything at all to go in the BCP file.

--
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|||1.

Quote:

Originally Posted by

You are missing data but where? You appear to extract data withBCP, do
you load it anywhere else?

On table Eventlog, there are missing data.

2.

Quote:

Originally Posted by

Quote:

Originally Posted by

create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins

>

Quote:

Originally Posted by

bcp"select a.* from w..eventlog a, w..eventupdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <userwithaccess-P <password-S doserver -f EventLog.fmt

>
TheBCPquery has a condition on the column UpdatePass which is not
set in the trigger. So from what you have posted, I would not expect
anything at all to go in theBCPfile.

sorry, i didn't clearly explain it. i did set the UpdataPass:

CREATE TABLE [EventLogUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventLogUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
GO|||(danceli@.gmail.com) writes:

Quote:

Originally Posted by

On table Eventlog, there are missing data.

But it was the EventLog table you had the trigger on? If data is
missing in EventLog what has the trigger or the BCP stuff to do with it?

Quote:

Originally Posted by

sorry, i didn't clearly explain it. i did set the UpdataPass:
>
CREATE TABLE [EventLogUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventLogUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
GO

I still can't see any update of UpdatePass?

It's your call. If you don't want to explain what you are doing, that
is alright. But unfortunately it is difficult to answer your questions
in this case.

--
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 error - missing data

After loading the BCP files that are created during the trigger/
reporting events I've noticed that the data in the table is missing
records. I've also noticed that the missing records (records in the
table but not in the BCP out files) seem to occur in contiguous
blocks. Since the complete set of records exists in the table, I
assume this points to an issue in the way the TableUpdate script/
Triggers interact with the system. But i tried to take out the bcp
procedure, do test on trigger, then no data missing, So, I think the
problem is still on bcp part. Could you help me with that?

CREATE TABLE [EventUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins

bcp script:

bcp "select a.* from w..eventlog a, w..eventupdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <userwithaccess-P <password-S doserver -f EventLog.fmt

Thanks in advance for your reply!Danceli,

Are your triggers set-based?

-- Bill

<danceli@.gmail.comwrote in message
news:1170212352.130762.295120@.m58g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

After loading the BCP files that are created during the trigger/
reporting events I've noticed that the data in the table is missing
records. I've also noticed that the missing records (records in the
table but not in the BCP out files) seem to occur in contiguous
blocks. Since the complete set of records exists in the table, I
assume this points to an issue in the way the TableUpdate script/
Triggers interact with the system. But i tried to take out the bcp
procedure, do test on trigger, then no data missing, So, I think the
problem is still on bcp part. Could you help me with that?
>
CREATE TABLE [EventUpdate] (
[id] [int] NOT NULL ,
[eventid] [int] NOT NULL ,
[sequenceid] [int] NOT NULL ,
[UpdatePass] [int] NULL
) ON [PRIMARY]
GO
create trigger trgEventUpdate on EventLog For Insert,Update as
insert into EventUpdate (id,eventid,sequenceid) select ins.id,
ins.eventid,ins.sequenceid from inserted ins
>
>
>
bcp script:
>
bcp "select a.* from w..eventlog a, w..eventupdate b where
a.eventid=b.eventid and a.sequenceid=b.sequenceid and b.eventid<>-1
and b.sequenceid<>-1 and b.updatepass=1" queryout 30sec-%TFN_NOW%.wrk -
U <userwithaccess-P <password-S doserver -f EventLog.fmt
>
Thanks in advance for your reply!
>

BCP Entire DB in MYSQL 2000

I have a stored proc that currently BCP 27 tables into a MS SQL 2000 DB. The original script was created to import FIXED-WIDTH. I have made the changes to TAB-DELIMITED. But the source db that outputs the TAB files is enclosing every field within double quotes("). I am getting a 'Invalid character value for cast specification' error on the first row which is an integer. Is there a parameter that I can set for bcp to strip out the "...".

Thanks,
JNunezNYCWhy don't you use DTS where you can specify the required settings for data.

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -T
Please post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here's
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T

bcp batch file

created a batch file to bcp info out of tables. When executing the file it
just loops on the first bcp statement without executing. The odd thing is I
can't execute the bcp from the command line on the c: drive, but it will
execute from a network drive prompt. Have full rights to the server. Here's
the statement. Have also tried with sql login.
bcp Database.dbo.table out d:\bcp\table.bcp -n -TPlease post the error which you are getting.
"cheilig" wrote:

> created a batch file to bcp info out of tables. When executing the file it
> just loops on the first bcp statement without executing. The odd thing is
I
> can't execute the bcp from the command line on the c: drive, but it will
> execute from a network drive prompt. Have full rights to the server. Here'
s
> the statement. Have also tried with sql login.
> bcp Database.dbo.table out d:\bcp\table.bcp -n -T