BCP Utility

Hi all,

I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:

AppointmentKey
SurgerySlotKey
PatientKey
Cancelled
Continuation
Deleted
TimeArrived

I would like to import only two of these fields into a table called tbl_Appointments e.g.

AppointmentKey
TimeArrived

I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?

ThanksReplace IN with FORMAT and add "-f file_name.fmt" without quotes on the command line when issuing BCP statement.

Open your favorite text editor and modify the format file to look something like this:8.0
7
1 SQLINT 0 0 "\t" 1 AppointmentKey ""
2 SQLCHAR 0 255 "\t" 0 SurgerySlotKey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 255 "\t" 0 PatientKey SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 255 "\t" 0 Cancelled SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 255 "\t" 0 Continuation SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 255 "\t" 0 Deleted SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 255 "\r\n" 7 TimeArrived SQL_Latin1_General_CP1_CI_AS|||Thanks for replying and you've certainly pointed me in the right direction. I've almost got it working but for a couple of problems. The first problem is that when it imports the Appointment key it seems to be adding a tab character or something to before the key e.g. 341084.80096.Appointment becomes 341084.80096.Appointment when imported to the new table. Also, I can't get it to import the date field 'TimeArrived' (please see attached), SQL server throws up the following error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file

Even although the column name is correct...I have tried importing the other date fileds and it does the same thing.

I have attached my .fmt file for you to have a look at.

Thanks|||The second token in the format file (where you have SQLCHAR etc) refers to the format of the field IN THE HOST FILE, not on the server. If this a date in ASCII readable string format, e.g. "Jan 1 2004 10:00AM" or "2004-01-01" the this should be set to SQLCHAR

see Books online : bcp utility / format files / Using format files|||Tried setting the date field to SQLCHAR (see attached file) but it's still giving me the same error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt'

Here's the SQL I'm using:

BULK INSERT tbl_UpdateTimeArrived FROM '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\xv_Appointment.dat'
WITH (FORMATFILE = '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt

The only other thing that may affect it is the .dat file in question is from a SQL Server 6.5 database and I'm importing it into a SQL 2000 database.|||maybe your SQLBIT columns should be SQLCHAR as well...|||Still didn't work but I'm slowly getting to the bottom of it. Tried importing a non-date, non-bit field and it imported fine (again see attached) - so it seems to be the date fields that are causing the problem...Any ideas?|||can you attach say top 5 lines of your data file ?|||I've figured it out. I need the same number of fields in the destination table as the table in the .dat file. Even although I only want to import two fileds. In effect, the destination table needs to be an exact replica regardless of whether I want to use all the fields or not!!

If anybodyt knows any different then please let me know.

bcp utf8 file

I'm outputting a file from a mySQL server that contains 1 column that
has input it many languages. The base definition for the column is
UTF8.

I output the data into a flat file and bcp it to a sql2000 server, and
the multi-byte data does not survive the translation.

Any ideas on how to input a UTF8 flat file into the server using BCP?

I've tried the -n -N -w options -- no luck.

thanks,

edAssuming that your destination column is nvarchar or nchar, then -w
should be the correct option; -n or -N are specific to MSSQL, so
they're unlikely to work. I've never used bcp to import a file like
that myself, so I can't really say for sure what the solution is, but
under "Copying Data Between Different Collations" in Books Online, it
suggests using a bcp format file with specific collations. You might
also try using DTS to import the data, as it's generally a lot smarter
than bcp, however again I've never tried importing a UTF8 file with it.

Simon|||(geekboyed@.gmail.com) writes:
> I'm outputting a file from a mySQL server that contains 1 column that
> has input it many languages. The base definition for the column is
> UTF8.
> I output the data into a flat file and bcp it to a sql2000 server, and
> the multi-byte data does not survive the translation.
> Any ideas on how to input a UTF8 flat file into the server using BCP?

Since you cannot store UTF-8 data in SQL 2000 in any civilized way,
you need to first convert the file to UCS-2. (Or UTF_16 if you prefer.
I hope you are not using surrogates.)

A simple way to do this is to open the file in Notepad, and then change
encoding when you do Save As.

I'm a little uncertain how any byte-order mark would affect BCP.
Notepad is likely to add one, but I don't know if BCP is smart
enough to ignore it.

--
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 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 Problems with ISO Latin-1 Characters past ASCII 192

I am trying to export data from a table with char and varchar fields
(Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
with the character past ASCII 192 the characters like , , , etc. My
export statement looks like this as I am using the -c flag.
bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
But I get the following:
MXICO| |MX|0|1
The data in the table looks like this
MXICO| |MX|0|1
-n (Native) did not seem to work.
-w (Unicode character) did work, geting it out, but I can not put it back
in...
Do I have some sort of code page/collation not set correct?
Thanks
Reeves
Hi
Have you tried -C RAW?
John
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>
|||Reeves,
We had a similar problem that I believe was fixed when we switched to
sing -C ACP. (RAW may do for you as well, try it out.) The problem is
that the default is -C OEM, which is basically the old DOS character set and
that is trashing some characters for you.
Russell Fields
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>

bcp problem

I'm not having a syntax problem, I don't think, but the following is telling 0 rows copied, but I do know the file contains data...anyone have any ideas?
bcp "Privacy_OHM.[dbo].[Dat_up_log]" in D:\DATA\OHM\T0000001.log -T -S<servername> -fD:\DATA\OHM\Dat_log_In.fmtwhat does the fmt file look like?

and the first few rows of your .log file?|||Perhaps toss in an error file to catch the rows that bomb out?|||it was the format file

The redirected out put is essentially 1 column

The table has 3 columns and I forgot to "zero out" the column in the format file

Thanks guys...sql

BCP out in UTF8 format

How to generate a file using BCP in UTF8 format. The table contains varchar,nvarchar and money as dataytpe for some columns. Will the –w switch in BCP for data file type would solve the problem because SQL server stores the data in UCS 2.0 format.
No, BCP will save it as UCS2
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:48BA7BC9-61DF-45FF-8D8F-586C21D0CF07@.microsoft.com...
> How to generate a file using BCP in UTF8 format. The table contains
> varchar,nvarchar and money as dataytpe for some columns. Will the -w
> switch in BCP for data file type would solve the problem because SQL
> server stores the data in UCS 2.0 format.
>

BCP out data to text files using SQL 2000

I am exporting data from a table with one column (varchar
255), to a flat text file using BCP out utility. If the
table contains around a 1000 or more rows sometimes the
records in the text file is not generated is the same
order as it was in the table.
I know if I add a clustered index column to the table and
use a select query I probably could get the results I
need. But I would like to keep that as a last resort, as
it would involve many stored procedure changes.
Note: not as much of a problem with SQL 6.5. After
upgrading to SQL 2000 it has become much more of an issue.
Thanks
Adding a clustered index does not guarantee data order from a select, bcp
etc. You must specify an ORDER BY clause to read the data in the proper
order. With that said you may want to ensure you have MAXDOP set to 1 if
you have a multiprocessor system when importing or exporting to get the best
chance of it going into the file in that order. Ideally you should have
some column that determines the proper order if that is important.
Andrew J. Kelly SQL MVP
"BC" <anonymous@.discussions.microsoft.com> wrote in message
news:bdbf01c479b3$d8a9f3a0$a501280a@.phx.gbl...
> I am exporting data from a table with one column (varchar
> 255), to a flat text file using BCP out utility. If the
> table contains around a 1000 or more rows sometimes the
> records in the text file is not generated is the same
> order as it was in the table.
> I know if I add a clustered index column to the table and
> use a select query I probably could get the results I
> need. But I would like to keep that as a last resort, as
> it would involve many stored procedure changes.
> Note: not as much of a problem with SQL 6.5. After
> upgrading to SQL 2000 it has become much more of an issue.
> Thanks
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schrieb im Newsbeitrag
news:OV6JPbbeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Adding a clustered index does not guarantee data order from a select,
bcp
> etc. You must specify an ORDER BY clause to read the data in the proper
> order. With that said you may want to ensure you have MAXDOP set to 1
if
> you have a multiprocessor system when importing or exporting to get the
best
> chance of it going into the file in that order. Ideally you should have
> some column that determines the proper order if that is important.
Additional remark: OP should never rely on the order of data in a table.
AFAIK there is no guarantee that data is drawn from a table with a certain
order other than specifying the desired order. Even if there is a
clustered index, you should explicitely specify the order. After all,
relational databases manage record sets rather lists of records.
Kind regards
robert

> --
> Andrew J. Kelly SQL MVP
>
> "BC" <anonymous@.discussions.microsoft.com> wrote in message
> news:bdbf01c479b3$d8a9f3a0$a501280a@.phx.gbl...
>

bcp issue

Dear all,
I've got a little issue and I can't work out with it. Using BCP in order to
export the contains of a .dat file into a table:
C:\OFI0501>BCP abs..ABS_OF501 IN 20050726.DAT -e enric.txt -n -Sserver -U
us1 -Pdts1
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Se encontró un EOF inesperado en
un archivo de datos BCP
((suddenly error in bcp file))
How do I find out where the eof mark are?
Does anyone ever used or suffered this error?
Regards,Hi,
EOF means End Of File - there's no particular code. Check if data format in
the file is correct
Peter|||Thanks Rogas69. I knew it. Only was I wondering how to solve it of an
automatically way or something like that. Bearing on mind I've got 200 files
to load...
Anyway
"Rogas69" wrote:

> Hi,
> EOF means End Of File - there's no particular code. Check if data format i
n
> the file is correct
> Peter
>
>|||Enric (Enric@.discussions.microsoft.com) writes:
> I've got a little issue and I can't work out with it. Using BCP in order
> to export the contains of a .dat file into a table:
> C:\OFI0501>BCP abs..ABS_OF501 IN 20050726.DAT -e enric.txt -n -Sserver
> -U us1 -Pdts1
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Se encontr un EOF inesperado
> en un archivo de datos BCP
> ((suddenly error in bcp file))
> How do I find out where the eof mark are?
> Does anyone ever used or suffered this error?
Better to ask if there is anyone who have used BCP and never got any error.
It would at least be easier to count the hands.
These extrmely common error means that the data file does not match the
format specification. BCP finds that the file ends in the middle of a
record.
Since you are using native format, this means that the table definition
does not match the table definition. I have no idea what you expected.
Are the files really in native format? Native format means that data appear
in the field as they appear in SQL Server, that is binary.
Since you mentioned that you had 200 files, I would more expect a text
format...
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|||XXXX..life, yep. I've came to the conclusion that the problem is that these
files were extracted from a 'unknown table' and now they want to load again
into another one...
"Erland Sommarskog" wrote:

> Enric (Enric@.discussions.microsoft.com) writes:
> Better to ask if there is anyone who have used BCP and never got any error
.
> It would at least be easier to count the hands.
> These extrmely common error means that the data file does not match the
> format specification. BCP finds that the file ends in the middle of a
> record.
> Since you are using native format, this means that the table definition
> does not match the table definition. I have no idea what you expected.
> Are the files really in native format? Native format means that data appea
r
> in the field as they appear in SQL Server, that is binary.
> Since you mentioned that you had 200 files, I would more expect a text
> format...
>
> --
> 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 errors.

I am trying to use bcp to copy some common columns from one table into
another table. Each table contains columns which will be ignored. I have
never seen a real-world example of bcp, and the only bcp examples I have seen
in MSDN are trivial, happy-path examples which do not address real-world
situations like this.
Do I have to account for each field in the text file? In the target table?
Here is what I have so far:
bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb -T
bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T -f
BCPformat.txt -k
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
BCPformat.txt
8.0
27
1 SQLINT 1 12 "\t" 88 NQUserId
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 50 "\t" 2 UserFirst
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 50 "\t" 6 Address
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 50 "\t" 19 UsedForSQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_CI_AS
17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 2 200 "\t" 79 Type
SQL_Latin1_General_CP1_CI_AS
19 SQLBIT 1 1 "\t" 27 nomail
SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 2 50 "\t" 31 CoBra
SQL_Latin1_General_CP1_CI_AS
21 SQLBIT 1 1 "\t" 37 optout
SQL_Latin1_General_CP1_CI_AS
22 SQLTINYINT 1 5 "\t" 80 lowData
SQL_Latin1_General_CP1_CI_AS
23 SQLTINYINT 1 5 "\t" 81 AgentLead
SQL_Latin1_General_CP1_CI_AS
24 SQLBIT 1 1 "\t" 82 Membership
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_CI_AS
26 SQLBIT 1 1 "\t" 84 EmailSent
SQL_Latin1_General_CP1_CI_AS
27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
SQL_Latin1_General_CP1_CI_AS
Why can't you just run a sql statement, or wrap the sql in a stored
procedure...
INSERT Users_mjl
SELECT col1, col3, col5, col6, col7
FROM NotQuiteUsers
Snake wrote:
> I am trying to use bcp to copy some common columns from one table into
> another table. Each table contains columns which will be ignored. I have
> never seen a real-world example of bcp, and the only bcp examples I have seen
> in MSDN are trivial, happy-path examples which do not address real-world
> situations like this.
> Do I have to account for each field in the text file? In the target table?
> Here is what I have so far:
> bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb -T
> bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T -f
> BCPformat.txt -k
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>
> BCPformat.txt
> 8.0
> 27
> 1 SQLINT 1 12 "\t" 88 NQUserId
> SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 2 50 "\t" 2 UserFirst
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 2 50 "\t" 6 Address
> SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
> 12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_AS
> 13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI_AS
> 14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_AS
> 15 SQLCHAR 2 50 "\t" 19 UsedForSQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_CI_AS
> 17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 2 200 "\t" 79 Type
> SQL_Latin1_General_CP1_CI_AS
> 19 SQLBIT 1 1 "\t" 27 nomail
> SQL_Latin1_General_CP1_CI_AS
> 20 SQLCHAR 2 50 "\t" 31 CoBra
> SQL_Latin1_General_CP1_CI_AS
> 21 SQLBIT 1 1 "\t" 37 optout
> SQL_Latin1_General_CP1_CI_AS
> 22 SQLTINYINT 1 5 "\t" 80 lowData
> SQL_Latin1_General_CP1_CI_AS
> 23 SQLTINYINT 1 5 "\t" 81 AgentLead
> SQL_Latin1_General_CP1_CI_AS
> 24 SQLBIT 1 1 "\t" 82 Membership
> SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_CI_AS
> 26 SQLBIT 1 1 "\t" 84 EmailSent
> SQL_Latin1_General_CP1_CI_AS
> 27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
> SQL_Latin1_General_CP1_CI_AS
|||If you are moving data from one table to another table in the same instance,
what Corey suggested is the right way to go. However, if you are moving data
to a new instance of SQL Server, here is what you can do
(1) Use QUERY feature of BCP-out to only export the columns that you want.
(2) Use Format file to skip columns or fields in the data file when
importing data file.
If you look at SQL2000 BOL, it does provide you info on how to use data file
with fewer fields, different order or more fields than the target column
thanks,
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1132072636.728960.229590@.g47g2000cwa.googlegr oups.com...
> Why can't you just run a sql statement, or wrap the sql in a stored
> procedure...
> INSERT Users_mjl
> SELECT col1, col3, col5, col6, col7
> FROM NotQuiteUsers
>
> Snake wrote:
>
|||Sunil Agarwal [MSFT] wrote:
> If you are moving data from one table to another table in the same
> instance, what Corey suggested is the right way to go. However, if
> you are moving data to a new instance of SQL Server, here is what you
> can do (1) Use QUERY feature of BCP-out to only export the columns
> that you want. (2) Use Format file to skip columns or fields in the
> data file when importing data file.
Additional options:
- DTS
- link instances
Kind regards
robert

bcp errors.

I am trying to use bcp to copy some common columns from one table into
another table. Each table contains columns which will be ignored. I have
never seen a real-world example of bcp, and the only bcp examples I have see
n
in MSDN are trivial, happy-path examples which do not address real-world
situations like this.
Do I have to account for each field in the text file? In the target table?
Here is what I have so far:
bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb -
T
bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T -f
BCPformat.txt -k
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right trunca
tion
BCPformat.txt
8.0
27
1 SQLINT 1 12 "\t" 88 NQUserId
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 50 "\t" 2 UserFirst
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_CI_
AS
4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI_A
S
5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_CI_
AS
6 SQLCHAR 2 50 "\t" 6 Address
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI_A
S
9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI_A
S
14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_CI_
AS
17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 2 200 "\t" 79 Type
SQL_Latin1_General_CP1_CI_AS
19 SQLBIT 1 1 "\t" 27 nomail
SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 2 50 "\t" 31 CoBra
SQL_Latin1_General_CP1_CI_AS
21 SQLBIT 1 1 "\t" 37 optout
SQL_Latin1_General_CP1_CI_AS
22 SQLTINYINT 1 5 "\t" 80 lowData
SQL_Latin1_General_CP1_CI_AS
23 SQLTINYINT 1 5 "\t" 81 AgentLead
SQL_Latin1_General_CP1_CI_AS
24 SQLBIT 1 1 "\t" 82 Membership
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_CI_
AS
26 SQLBIT 1 1 "\t" 84 EmailSent
SQL_Latin1_General_CP1_CI_AS
27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
SQL_Latin1_General_CP1_CI_ASWhy can't you just run a sql statement, or wrap the sql in a stored
procedure...
INSERT Users_mjl
SELECT col1, col3, col5, col6, col7
FROM NotQuiteUsers
Snake wrote:
> I am trying to use bcp to copy some common columns from one table into
> another table. Each table contains columns which will be ignored. I have
> never seen a real-world example of bcp, and the only bcp examples I have s
een
> in MSDN are trivial, happy-path examples which do not address real-world
> situations like this.
> Do I have to account for each field in the text file? In the target table?
> Here is what I have so far:
> bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb
-T
> bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T
-f
> BCPformat.txt -k
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right trun
cation
>
> BCPformat.txt
> 8.0
> 27
> 1 SQLINT 1 12 "\t" 88 NQUserId
> SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 2 50 "\t" 2 UserFirst
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_C
I_AS
> 4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI
_AS
> 5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_C
I_AS
> 6 SQLCHAR 2 50 "\t" 6 Address
> SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_
AS
> 8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI
_AS
> 9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_A
S
> 10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_A
S
> 11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
> 12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_A
S
> 13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI
_AS
> 14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_
AS
> 15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_C
I_AS
> 17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 2 200 "\t" 79 Type
> SQL_Latin1_General_CP1_CI_AS
> 19 SQLBIT 1 1 "\t" 27 nomail
> SQL_Latin1_General_CP1_CI_AS
> 20 SQLCHAR 2 50 "\t" 31 CoBra
> SQL_Latin1_General_CP1_CI_AS
> 21 SQLBIT 1 1 "\t" 37 optout
> SQL_Latin1_General_CP1_CI_AS
> 22 SQLTINYINT 1 5 "\t" 80 lowData
> SQL_Latin1_General_CP1_CI_AS
> 23 SQLTINYINT 1 5 "\t" 81 AgentLead
> SQL_Latin1_General_CP1_CI_AS
> 24 SQLBIT 1 1 "\t" 82 Membership
> SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_C
I_AS
> 26 SQLBIT 1 1 "\t" 84 EmailSent
> SQL_Latin1_General_CP1_CI_AS
> 27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
> SQL_Latin1_General_CP1_CI_AS|||If you are moving data from one table to another table in the same instance,
what Corey suggested is the right way to go. However, if you are moving data
to a new instance of SQL Server, here is what you can do
(1) Use QUERY feature of BCP-out to only export the columns that you want.
(2) Use Format file to skip columns or fields in the data file when
importing data file.
If you look at SQL2000 BOL, it does provide you info on how to use data file
with fewer fields, different order or more fields than the target column
thanks,
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1132072636.728960.229590@.g47g2000cwa.googlegroups.com...
> Why can't you just run a sql statement, or wrap the sql in a stored
> procedure...
> INSERT Users_mjl
> SELECT col1, col3, col5, col6, col7
> FROM NotQuiteUsers
>
> Snake wrote:
>|||Sunil Agarwal [MSFT] wrote:
> If you are moving data from one table to another table in the same
> instance, what Corey suggested is the right way to go. However, if
> you are moving data to a new instance of SQL Server, here is what you
> can do (1) Use QUERY feature of BCP-out to only export the columns
> that you want. (2) Use Format file to skip columns or fields in the
> data file when importing data file.
Additional options:
- DTS
- link instances
Kind regards
robert

bcp errors.

I am trying to use bcp to copy some common columns from one table into
another table. Each table contains columns which will be ignored. I have
never seen a real-world example of bcp, and the only bcp examples I have seen
in MSDN are trivial, happy-path examples which do not address real-world
situations like this.
Do I have to account for each field in the text file? In the target table?
Here is what I have so far:
bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb -T
bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T -f
BCPformat.txt -k
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
BCPformat.txt
8.0
27
1 SQLINT 1 12 "\t" 88 NQUserId
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 50 "\t" 2 UserFirst
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 50 "\t" 6 Address
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_CI_AS
17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 2 200 "\t" 79 Type
SQL_Latin1_General_CP1_CI_AS
19 SQLBIT 1 1 "\t" 27 nomail
SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 2 50 "\t" 31 CoBra
SQL_Latin1_General_CP1_CI_AS
21 SQLBIT 1 1 "\t" 37 optout
SQL_Latin1_General_CP1_CI_AS
22 SQLTINYINT 1 5 "\t" 80 lowData
SQL_Latin1_General_CP1_CI_AS
23 SQLTINYINT 1 5 "\t" 81 AgentLead
SQL_Latin1_General_CP1_CI_AS
24 SQLBIT 1 1 "\t" 82 Membership
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_CI_AS
26 SQLBIT 1 1 "\t" 84 EmailSent
SQL_Latin1_General_CP1_CI_AS
27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
SQL_Latin1_General_CP1_CI_ASWhy can't you just run a sql statement, or wrap the sql in a stored
procedure...
INSERT Users_mjl
SELECT col1, col3, col5, col6, col7
FROM NotQuiteUsers
Snake wrote:
> I am trying to use bcp to copy some common columns from one table into
> another table. Each table contains columns which will be ignored. I have
> never seen a real-world example of bcp, and the only bcp examples I have seen
> in MSDN are trivial, happy-path examples which do not address real-world
> situations like this.
> Do I have to account for each field in the text file? In the target table?
> Here is what I have so far:
> bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S dev\dweb -T
> bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S dev\dweb -T -f
> BCPformat.txt -k
> Starting copy...
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>
> BCPformat.txt
> 8.0
> 27
> 1 SQLINT 1 12 "\t" 88 NQUserId
> SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 2 50 "\t" 2 UserFirst
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 2 50 "\t" 3 UserLast SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 2 50 "\t" 4 Company SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 2 50 "\t" 5 SponsorId SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 2 50 "\t" 6 Address
> SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 2 50 "\t" 7 City SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 2 50 "\t" 8 State SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 2 50 "\t" 9 Zip SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 2 50 "\t" 10 Phone SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
> 12 SQLCHAR 2 50 "\t" 12 Email SQL_Latin1_General_CP1_CI_AS
> 13 SQLCHAR 2 50 "\t" 78 Subscription SQL_Latin1_General_CP1_CI_AS
> 14 SQLCHAR 2 40 "\t" 18 HeardFrom SQL_Latin1_General_CP1_CI_AS
> 15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 2 50 "\t" 20 Profession SQL_Latin1_General_CP1_CI_AS
> 17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 2 200 "\t" 79 Type
> SQL_Latin1_General_CP1_CI_AS
> 19 SQLBIT 1 1 "\t" 27 nomail
> SQL_Latin1_General_CP1_CI_AS
> 20 SQLCHAR 2 50 "\t" 31 CoBra
> SQL_Latin1_General_CP1_CI_AS
> 21 SQLBIT 1 1 "\t" 37 optout
> SQL_Latin1_General_CP1_CI_AS
> 22 SQLTINYINT 1 5 "\t" 80 lowData
> SQL_Latin1_General_CP1_CI_AS
> 23 SQLTINYINT 1 5 "\t" 81 AgentLead
> SQL_Latin1_General_CP1_CI_AS
> 24 SQLBIT 1 1 "\t" 82 Membership
> SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 2 1 "\t" 83 MemberType SQL_Latin1_General_CP1_CI_AS
> 26 SQLBIT 1 1 "\t" 84 EmailSent
> SQL_Latin1_General_CP1_CI_AS
> 27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
> SQL_Latin1_General_CP1_CI_AS|||If you are moving data from one table to another table in the same instance,
what Corey suggested is the right way to go. However, if you are moving data
to a new instance of SQL Server, here is what you can do
(1) Use QUERY feature of BCP-out to only export the columns that you want.
(2) Use Format file to skip columns or fields in the data file when
importing data file.
If you look at SQL2000 BOL, it does provide you info on how to use data file
with fewer fields, different order or more fields than the target column
thanks,
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1132072636.728960.229590@.g47g2000cwa.googlegroups.com...
> Why can't you just run a sql statement, or wrap the sql in a stored
> procedure...
> INSERT Users_mjl
> SELECT col1, col3, col5, col6, col7
> FROM NotQuiteUsers
>
> Snake wrote:
>> I am trying to use bcp to copy some common columns from one table into
>> another table. Each table contains columns which will be ignored. I
>> have
>> never seen a real-world example of bcp, and the only bcp examples I have
>> seen
>> in MSDN are trivial, happy-path examples which do not address real-world
>> situations like this.
>> Do I have to account for each field in the text file? In the target
>> table?
>> Here is what I have so far:
>> bcp MYDB.dbo.NotQuiteUsers out c:\temp\NotQuiteUsers.txt -S
>> ev\dweb -T
>> bcp MYDB.dbo.Users_mjl IN c:\temp\NotQuiteUsers.txt -S
>> dev\dweb -T -f
>> BCPformat.txt -k
>> Starting copy...
>> SQLState = 22001, NativeError = 0
>> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>>
>> BCPformat.txt
>> 8.0
>> 27
>> 1 SQLINT 1 12 "\t" 88 NQUserId
>> SQL_Latin1_General_CP1_CI_AS
>> 2 SQLCHAR 2 50 "\t" 2 UserFirst
>> SQL_Latin1_General_CP1_CI_AS
>> 3 SQLCHAR 2 50 "\t" 3 UserLast
>> SQL_Latin1_General_CP1_CI_AS
>> 4 SQLCHAR 2 50 "\t" 4 Company
>> SQL_Latin1_General_CP1_CI_AS
>> 5 SQLCHAR 2 50 "\t" 5 SponsorId
>> SQL_Latin1_General_CP1_CI_AS
>> 6 SQLCHAR 2 50 "\t" 6 Address
>> SQL_Latin1_General_CP1_CI_AS
>> 7 SQLCHAR 2 50 "\t" 7 City
>> SQL_Latin1_General_CP1_CI_AS
>> 8 SQLCHAR 2 50 "\t" 8 State
>> SQL_Latin1_General_CP1_CI_AS
>> 9 SQLCHAR 2 50 "\t" 9 Zip
>> SQL_Latin1_General_CP1_CI_AS
>> 10 SQLCHAR 2 50 "\t" 10 Phone
>> SQL_Latin1_General_CP1_CI_AS
>> 11 SQLCHAR 2 50 "\t" 11 Fax SQL_Latin1_General_CP1_CI_AS
>> 12 SQLCHAR 2 50 "\t" 12 Email
>> SQL_Latin1_General_CP1_CI_AS
>> 13 SQLCHAR 2 50 "\t" 78 Subscription
>> SQL_Latin1_General_CP1_CI_AS
>> 14 SQLCHAR 2 40 "\t" 18 HeardFrom
>> SQL_Latin1_General_CP1_CI_AS
>> 15 SQLCHAR 2 50 "\t" 19 UsedFor SQL_Latin1_General_CP1_CI_AS
>> 16 SQLCHAR 2 50 "\t" 20 Profession
>> SQL_Latin1_General_CP1_CI_AS
>> 17 SQLDATETIM4 1 24 "\t" 25 sdCreated SQL_Latin1_General_CP1_CI_AS
>> 18 SQLCHAR 2 200 "\t" 79 Type
>> SQL_Latin1_General_CP1_CI_AS
>> 19 SQLBIT 1 1 "\t" 27 nomail
>> SQL_Latin1_General_CP1_CI_AS
>> 20 SQLCHAR 2 50 "\t" 31 CoBra
>> SQL_Latin1_General_CP1_CI_AS
>> 21 SQLBIT 1 1 "\t" 37 optout
>> SQL_Latin1_General_CP1_CI_AS
>> 22 SQLTINYINT 1 5 "\t" 80 lowData
>> SQL_Latin1_General_CP1_CI_AS
>> 23 SQLTINYINT 1 5 "\t" 81 AgentLead
>> SQL_Latin1_General_CP1_CI_AS
>> 24 SQLBIT 1 1 "\t" 82 Membership
>> SQL_Latin1_General_CP1_CI_AS
>> 25 SQLCHAR 2 1 "\t" 83 MemberType
>> SQL_Latin1_General_CP1_CI_AS
>> 26 SQLBIT 1 1 "\t" 84 EmailSent
>> SQL_Latin1_General_CP1_CI_AS
>> 27 SQLBIT 1 1 "\r\n" 85 EmailSentReal
>> SQL_Latin1_General_CP1_CI_AS
>|||Sunil Agarwal [MSFT] wrote:
> If you are moving data from one table to another table in the same
> instance, what Corey suggested is the right way to go. However, if
> you are moving data to a new instance of SQL Server, here is what you
> can do (1) Use QUERY feature of BCP-out to only export the columns
> that you want. (2) Use Format file to skip columns or fields in the
> data file when importing data file.
Additional options:
- DTS
- link instances
Kind regards
robert

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:

> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

bcp and exporting

Hello,
I'm exporting data from text file which contains 15 lakh records.
Each field is delimited by "," and record is delimited by "$".
Only 163200 records were inserted.
I'm using this option
bcp tablename in
"datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
Any workarounds?Hi
Was there any error message?
John
"Raj" wrote:
> Hello,
> I'm exporting data from text file which contains 15 lakh records.
> Each field is delimited by "," and record is delimited by "$".
> Only 163200 records were inserted.
> I'm using this option
> bcp tablename in
> "datafile.txt" -c -t"," -r"$" -S"MyServer" -U"sa" -P"pwd123"
> Any workarounds?
>
>

BCP & odd charachters

Hi

When I BCP out some data from a database that contains german charachters ( in particular characters with hyphens & unlatts on them ), the letter appears as a square box when I open it in a text file.

I run BCP locally on the server that the database is on.

Any ideas what might be causing this? I have the local Regional settings of the server set to German but still no joy.

Any ideas?

Help!!

JWhat happens when you BCP the data back into a database? Sometimes certain products that we use to view files can not display none printable characters to they write out the 'box'. It does not mean that data is incorrect. You should create another test table and BCP the data into the new test table.

Basic SQL Execution Question

I have a table that contains a history of record changes within another table. It may have rows that look like this:

DML_Type Record_ID Record_Value DateTime
Insert 1 A Today @. 11AM
Update 1 B Today @. 11:15AM
Update 1 C Today @. 11:22AM
Delete 1 C Today @. 11:25AM
Insert 2 A Today @. 11AM

Update 2 B Today @. 11:15AM

Update 2 C Today @. 11:22AM

I've inherited a stored procedure that migrates these changes to another database in another schema. The proc is using a cursor to perform each operation in sequence.

If I perform the migration in 3 steps (inserts, updates, deletes) - Can I be sure that the updates will occur in the correct sequence if ordered by DateTime or is there a possibility that SQL Server will parellize the operation and I'd end up with incorrect data? For example, is there a possibility using the T-SQL below that I'd end up with a record_ID 2 and a value of B?

UPDATE
destination table
SET
field1 = field,
...
FROM
SELECT
Record_ID,
Record_Value
FROM
sourceTable st
WHERE
DML_Type = 'Update'
ORDER BY
DateTime
WHERE
dt.Record_ID = st.Record_ID

Dan:

Is Record_ID unique in the [destination table]?

Dave

|||Yes. Record_ID is unique in the destination table. It's amusing that you ask this because the problem I'm dealing with can be completely avoided by using the state of the record in the original table - the audit history isn't important for our purposes.

I am still interested in the answer to the original question for my own edification however.|||

Dan:

Here are a couple of mock-ups of your update:

set nocount on

update destinationTable
set record_value = 'A',
[dateTime] = '12/13/2006 11:00'
--select * from destinationTable

--
-- This works with the particular data but it is really
-- not supported; this is likely to be faster than
-- the second query:
--
/*
UPDATE destinationTable
SET record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join sourceTable st
on dt.record_Id = st.record_id
and st.DML_Type = 'Update'
*/

--
-- This is "safer":
--

update destinationTable
set record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join
( select record_id,
record_value,
[dateTime],
row_number () over
( partition by record_id
order by [dateTime] desc, record_value
) as seq
from sourceTable
where dml_type = 'Update'
) st
on dt.record_id = st.record_id
and st.seq = 1

select * from destinationTable

-- Output:

-- record_id record_value dateTime
-- -- --
-- 1 C 2006-12-13 11:22:00.000
-- 2 C 2006-12-13 11:22:00.000

|||Thanks for the response. Based on your answer, I'm going to assume that the default behavior is undefined and may produce data loss. Now I'll have to read up on partioning :-)

Basic RS Parameter Question

Being VERY new to RS 2000 (started looking at it a week ago), I need some advice. I have a table that contains a datetime field and I have a report that is prompting for a start date and start time, along with an end date and end time (4 prompts in total). How do I combine the dates and times together to pull into the query filter? Am I even going about this the right way?

Thanks much!

If I am understanding correctly you could do something like in your filters set the

TimeValue(Fields!Example.value) = TimeValue(Parameters!Time.Value)

and you could do that for time and date if you are trying to break apart a datetime field.

Josh

|||

u can add the parameters into the SQL code such as :

SELECT * FROM tbl_Name WHEREId=@.Id

The RS refer the @.Id as Parameter.