BCP Guidance Please

BCP Guidance Please

Post by Bud Dea » Thu, 18 Oct 2001 05:19:31



Hi folks,

Hope someone can help me out with this dilemma.

I have been tasked with the following:

Replicating an SQLServer 7.0 db to other servers, both on the LAN and wan.
Seems pretty straightforward, But...

First, the database I am replicating is not one we developed.  It is EZCAP
by Quadramed.  Previously, the used Centura as their engine.  Anyway, due to
support issues, etc., we cannot modify the database.  Stored procedures,
anything.  This includes any modifications done for replication.

The database I am putting the database into is for reporting only.  I will
be taking tables and views only.  Not a problem. I am running into a little
issue however.  I am using bcp out and in to replicate the tables. I am
having a problem with the "rowid" column, which is a timestamp datatype.
Initially, I changed the datatype to a varchar.  I used a -c switch in the
bcp command line.  That worked ok, except I can't figure out how to compare
the original field with new field.

I'm attempting to do all this without a format file.  All seems to work well
as long I use the -c switch.  I ran into the problem comparing the two
"rowid" columns, so I started experimenting with the -n and -N switches.
The data goes out ok, but I get lots of errors on the in portion.

sure would appreciate any help guidance as to how to deal with the timestamp
column.  I've looked through BOL, google, msdn and the kb.  Just can't get
it into my thick skull how to do it...

Here's the code I use for bcp in and out...

********bcpout********
EXEC master..xp_cmdshell 'bcp MONARCH..CLAIM_DETAILS out
C:\TEMP\CLAIM_DETAILS.txt -c /t"~|" /r
\r  -U -P -m8000 -eC:\TEMP\CLAIM_DETAILS_err.txt'

********bcpin*********
EXEC master..xp_cmdshell 'bcp TESTDB..CLAIM_DETAILS in
C:\TEMP\CLAIM_DETAILS.txt -c /t"~|" /r
\r  -U -P -m8000 -eC:\TEMP\CLAIM_DETAILS_err.txt  -b5000'

Thanks in advance,

Bud Dean

 
 
 

BCP Guidance Please

Post by Sarma U. V » Thu, 18 Oct 2001 16:07:10


Hi

I dont think it is possible to insert timestamp column
value directly from the input file. Its SQL who does this
when the data is imported to the database automatically.
you may need to rely on some key to compare your export /
import

Quote:>-----Original Message-----
>Hi folks,

>Hope someone can help me out with this dilemma.

>I have been tasked with the following:

>Replicating an SQLServer 7.0 db to other servers, both on
the LAN and wan.
>Seems pretty straightforward, But...

>First, the database I am replicating is not one we

developed.  It is EZCAP
Quote:>by Quadramed.  Previously, the used Centura as their

engine.  Anyway, due to
Quote:>support issues, etc., we cannot modify the database.  
Stored procedures,
>anything.  This includes any modifications done for
replication.

>The database I am putting the database into is for

reporting only.  I will
Quote:>be taking tables and views only.  Not a problem. I am

running into a little
Quote:>issue however.  I am using bcp out and in to replicate
the tables. I am
>having a problem with the "rowid" column, which is a
timestamp datatype.
>Initially, I changed the datatype to a varchar.  I used
a -c switch in the
>bcp command line.  That worked ok, except I can't figure
out how to compare
>the original field with new field.

>I'm attempting to do all this without a format file.  All
seems to work well
>as long I use the -c switch.  I ran into the problem
comparing the two
>"rowid" columns, so I started experimenting with the -n
and -N switches.
>The data goes out ok, but I get lots of errors on the in
portion.

>sure would appreciate any help guidance as to how to deal
with the timestamp
>column.  I've looked through BOL, google, msdn and the
kb.  Just can't get
>it into my thick skull how to do it...

>Here's the code I use for bcp in and out...

>********bcpout********
>EXEC master..xp_cmdshell 'bcp MONARCH..CLAIM_DETAILS out
>C:\TEMP\CLAIM_DETAILS.txt -c /t"~|" /r
>\r  -U -P -m8000 -eC:\TEMP\CLAIM_DETAILS_err.txt'

>********bcpin*********
>EXEC master..xp_cmdshell 'bcp TESTDB..CLAIM_DETAILS in
>C:\TEMP\CLAIM_DETAILS.txt -c /t"~|" /r
>\r  -U -P -m8000 -eC:\TEMP\CLAIM_DETAILS_err.txt  -b5000'

>Thanks in advance,

>Bud Dean

>.


 
 
 

BCP Guidance Please

Post by Rand Bo » Fri, 19 Oct 2001 23:46:45


Bud,

Try using a binary(8) for your timestamp column. When SQL Server replicates
a timestamp column it replicates it as a binary(8). You also may be able to
use the -n switch with this as well. Have not tested but it may work.

Rand Boyd
Microsoft SQL Server Support

Please reply only to the newsgroups.
When posting, please state the version of SQL Server being used and the
error number/exact error  message text received, if any.

I've been "encouraged" to share this helpful information from our lawyers.
This posting is  provided "AS IS" with no warranties, and confers no
rights. You assume all risk for your use.
? 2001 Microsoft Corporation. All rights reserved.

 
 
 

1. Guidance with bcp

I am trying to load a data file in to a table using bcp.  The following
is the format file I'm using:
6.0
12
1       SQLNUMERIC    1       8       ""        1       Mstr_Nr
2       SQLCHAR       0       2       "="       2       Occup
3       SQLCHAR       0       30      "="       3       Name
4       SQLCHAR       0       30      "="       4       Addr1
5       SQLCHAR       0       30      "="       5       Addr2
6       SQLCHAR       0       16      "="       6       City
7       SQLCHAR       0       2       "="       7       State
8       SQLCHAR       0       5       "-"       8       ZipCode
9       SQLCHAR       0       4       "="       9       Zip_4
10      SQLCHAR       0       0       "="       0       County
11      SQLCHAR       0       4       "="       10      Car_Rt
12      SQLCHAR       0       3       "="       11      DPBC
13      SQLSHAR       0       0       "\r\n"    0       EndChar

County and EndChar do not exist in my table but they are in the data
file.  The MstrNr is an identity field in the table, but does not exist
in the data file. the "=" is the field separator in the data file.

Every time I enter the following DOS command  I get a major GP fault on
DOS and it is gone.

bcp lists_db.dbo.mstrtbl in sqltest.aa -fmstr.fmt -Sserver01 -T

Is there something obvious that I am doing wrong?

2. Fox 2.6 win printing multi collumn report to Tally Line Printer

3. JAVA infant here - guidance please

4. HP Logical Vol. Manager

5. BCP help please!

6. Oracle alerts and pipes

7. Please explain this bcp error

8. Deleting Tables

9. BCP woes - HELP STILL REQUIRED PLEASE!

10. Please help with BCP - EOF errors

11. BCP Bulk Insert Help - Please

12. BCP IN - help with Date import please.

13. BCP error, please help??!?!?