bcp data to change sort order

I have followed the TK "How to BCP Data Out and Back into a New Database 
(875179)" a few times and it seems to work great.  However, when bcp'ng the 
data back in, I do notice some errors as it flashes by in the dos screen, 
however, NONE of the .err files show any size other than 0k.  After logging 
into Great Plains, I do notice that some information is completely missing.  
I check the tables and they are empty.  I checked the in and out bat files 
and those tables are exported, then imported, the table is empty and its .err 
file is also blank.

If the .err files do not show any errors, how can we ensure that ALL our 
data was successfully imported?
0
Luvsql (265)
10/15/2004 2:07:08 PM
greatplains 29623 articles. 4 followers. Follow

4 Replies
688 Views

Similar Articles

[PageSpeed] 36

I used the script supplied by previous upgrade manuals to see the difference 
in the number of records in each table:

Select name, rows from sysindexes
where indid in (0,1)
order by name

And the numbers are completely different!  As well, on my new install of 7.5 
on the new server, for some reason, some tables do not appear in the 
sysindexes table (GL20000 for example).

Has anyone else actually had positive results with this procedure that MBS 
is endorsing?  Why would the GL20000 table not appear in the sysindexes on a 
brand new installation?

"Luvsql" wrote:

> I have followed the TK "How to BCP Data Out and Back into a New Database 
> (875179)" a few times and it seems to work great.  However, when bcp'ng the 
> data back in, I do notice some errors as it flashes by in the dos screen, 
> however, NONE of the .err files show any size other than 0k.  After logging 
> into Great Plains, I do notice that some information is completely missing.  
> I check the tables and they are empty.  I checked the in and out bat files 
> and those tables are exported, then imported, the table is empty and its .err 
> file is also blank.
> 
> If the .err files do not show any errors, how can we ensure that ALL our 
> data was successfully imported?
0
Luvsql (265)
10/15/2004 6:45:02 PM
I just bcp'd the GL30000 out again, the bcp'd in again (ie exported data from 
old, dropped table on new, bcp'd data in on new) and there is a difference.  
There is nothing in the GL30000.err file.

Before  1166397
After   1166548
That's a big difference if these 151 entries were large amounts.  I have 
found many, many differences.  These are the biggest ones.  This just does 
not work does it?

Before                          After

RM30501	        766446		RM30501	        27907
RM40102	        633558		RM40102	        1
SOP10107	379345		SOP10107	11
SOP10104	257801		SOP10104	0
SOP40200	230403		SOP40200	44
RM00700	        188551		RM00700	        83
RM30401	        123493		RM30401	        0
POP10600	68098		POP10600	10231
RM30202	        180702		RM30202	        125587
POP40400	54610		POP40400	0
SOP10103	45814		SOP10103	0
RM00106	        45504		RM00106	        8
SOP40702	40779		SOP40702	0
POP40100	40639		POP40100	1
RM40101	        31464		RM40101	        1
RM10901	        27474		RM10901	        0
POP30360	67516		POP30360	41432
SOP10109	26083		SOP10109	1
POP30330	25992		POP30330	848
POP10306	20832		POP10306	0


"Luvsql" wrote:

> I used the script supplied by previous upgrade manuals to see the difference 
> in the number of records in each table:
> 
> Select name, rows from sysindexes
> where indid in (0,1)
> order by name
> 
> And the numbers are completely different!  As well, on my new install of 7.5 
> on the new server, for some reason, some tables do not appear in the 
> sysindexes table (GL20000 for example).
> 
> Has anyone else actually had positive results with this procedure that MBS 
> is endorsing?  Why would the GL20000 table not appear in the sysindexes on a 
> brand new installation?
> 
> "Luvsql" wrote:
> 
> > I have followed the TK "How to BCP Data Out and Back into a New Database 
> > (875179)" a few times and it seems to work great.  However, when bcp'ng the 
> > data back in, I do notice some errors as it flashes by in the dos screen, 
> > however, NONE of the .err files show any size other than 0k.  After logging 
> > into Great Plains, I do notice that some information is completely missing.  
> > I check the tables and they are empty.  I checked the in and out bat files 
> > and those tables are exported, then imported, the table is empty and its .err 
> > file is also blank.
> > 
> > If the .err files do not show any errors, how can we ensure that ALL our 
> > data was successfully imported?
0
Luvsql (265)
10/15/2004 7:43:11 PM
Hi,

I am also looking for this solution.Can you pl. forward me the procedure 
"How to BCP Data Out and Back into a New Database  (875179)"

Shall be highly grateful.

Regards

musman@iffco.com
0
10/15/2004 10:29:05 PM
Not all bcp errors will show up in the err file, and so if you did see errors 
flashing by on the screen you should redirect them to a file so that you can 
read through them to determine why your data isn't being loaded.  Try 
something like the following syntax:

bcp "DYNAMICS..SY02000" in SY02000.out -E  -b 100 -e SY02000.err -c  -S 
MyServer –U sa –P MyPassword -t "|" -r "#EOR#\n" -C >> MyOutputFile.bcp

where MyOutputFile.bcp is the text file that will be created that shows the 
result of the bcp command executed.

On another note, I would expect your sysindexes table to contain different 
record counts because this just stores index page information...not actual 
data page information.  Try running the following t-sql script to quickly 
build a list of t-sql statements that can be used to verify your table 
rowcounts before and after your export/import.

select 'select ''' + name + ''' as TableName, count(*) as ''Rowcount'' from 
' + name  from sysobjects where type = 'U' order by name

....though you probably want to SET NOCOUNT ON before you execute the 
resultant statements (and turn it OFF when finished).


"Luvsql" wrote:

> I just bcp'd the GL30000 out again, the bcp'd in again (ie exported data from 
> old, dropped table on new, bcp'd data in on new) and there is a difference.  
> There is nothing in the GL30000.err file.
> 
> Before  1166397
> After   1166548
> That's a big difference if these 151 entries were large amounts.  I have 
> found many, many differences.  These are the biggest ones.  This just does 
> not work does it?
> 
> Before                          After
> 
> RM30501	        766446		RM30501	        27907
> RM40102	        633558		RM40102	        1
> SOP10107	379345		SOP10107	11
> SOP10104	257801		SOP10104	0
> SOP40200	230403		SOP40200	44
> RM00700	        188551		RM00700	        83
> RM30401	        123493		RM30401	        0
> POP10600	68098		POP10600	10231
> RM30202	        180702		RM30202	        125587
> POP40400	54610		POP40400	0
> SOP10103	45814		SOP10103	0
> RM00106	        45504		RM00106	        8
> SOP40702	40779		SOP40702	0
> POP40100	40639		POP40100	1
> RM40101	        31464		RM40101	        1
> RM10901	        27474		RM10901	        0
> POP30360	67516		POP30360	41432
> SOP10109	26083		SOP10109	1
> POP30330	25992		POP30330	848
> POP10306	20832		POP10306	0
> 
> 
> "Luvsql" wrote:
> 
> > I used the script supplied by previous upgrade manuals to see the difference 
> > in the number of records in each table:
> > 
> > Select name, rows from sysindexes
> > where indid in (0,1)
> > order by name
> > 
> > And the numbers are completely different!  As well, on my new install of 7.5 
> > on the new server, for some reason, some tables do not appear in the 
> > sysindexes table (GL20000 for example).
> > 
> > Has anyone else actually had positive results with this procedure that MBS 
> > is endorsing?  Why would the GL20000 table not appear in the sysindexes on a 
> > brand new installation?
> > 
> > "Luvsql" wrote:
> > 
> > > I have followed the TK "How to BCP Data Out and Back into a New Database 
> > > (875179)" a few times and it seems to work great.  However, when bcp'ng the 
> > > data back in, I do notice some errors as it flashes by in the dos screen, 
> > > however, NONE of the .err files show any size other than 0k.  After logging 
> > > into Great Plains, I do notice that some information is completely missing.  
> > > I check the tables and they are empty.  I checked the in and out bat files 
> > > and those tables are exported, then imported, the table is empty and its .err 
> > > file is also blank.
> > > 
> > > If the .err files do not show any errors, how can we ensure that ALL our 
> > > data was successfully imported?
0
smcintyr (8)
10/19/2004 7:31:01 PM
Reply:

Similar Artilces:

Bring Data from Access
Dear all, I'm trying to do bring some information from access to excel, but sometimes I have a criteria and sometimes I don't need a criteria. For example: I can choose only the month January in my criteria, or if I put nothing it will bring all year of 2007. There are a lot of criteria in my sheet, but if I discover how to do in this case, I will do the same for the others criteria. This is the code that I'm working: Sub return_values_qty_2() Dim adoconn As ADODB.Connection Dim adors As ADODB.Recordset Dim sql As String Dim filenm As String Dim xlsht As Excel.Worksheet Set ...

dates suddenly changed from x/xx/2004 format to number
I set up my excel 2003 for dates to be written 4/25/2004 and instead all I get is serial numbers. I tried to format the cell for the date as above but it only comes out in serial numbers. When I set this spead sheet up it worked fine. The suddenly this changed and the dates went away. I cannot reverse this. any hints. As answered by Jim Rech in excel.misc -- Maybe you hit Ctrl-~ by accident and changed to "formulas view"? Hit it again or uncheck Tools, Options, View, Formulas. scheinz wrote: > I set up my excel 2003 for dates to be written 4/25/2004 > and instea...

data
Hi, I transported data into excel. I have values in A10, A20, A30, A40 etc.... A1000 I want to delete all the empty cells. So, B1 should be A10 B2 should be A20 B3 should be A30 B4 should be A40 If I enter the formula in B1: =A10 B2: =A20 etc. I cannot copy the formula, if I copy it the formula in B3 is A12 and it should be A30. I hope someone can help me. -- melle ------------------------------------------------------------------------ melle's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26981 View this thread: http://www.excelforum.com/showt...

Changing Colour of Bar depending on Value
Hi there, I am trying to put together an attendence register for some students, th calculations are sorted, but what I want to do is create a bar graph showing total percentage attendance but having the bar change dependin on the value. e.g. if attendance is 100% a Green Bar, if say 80% Amber and if 60% Red. Is this possible? Cheers Here's a conditional charting approach that uses separate series for each format: http://peltiertech.com/Excel/Charts/ConditionalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions...

BCP Computed Column
Hi. I have a table with a computed column. When I try to import data into table through BCP it generates error due to computed column. So how to handle a computed column while importing data into table through bcp from a text file. Regards, Muhammad Bilal You need to use a format file that tells BCP to skip that column. See BOL for info on bcp format files. Tom "Muhammad Bilal" <MuhammadBilal@discussions.microsoft.com> wrote in message news:5AD65421-C599-4773-B56F-24841FE05D5A@microsoft.com... > Hi. > > I have a table with a computed column...

Quickly Change PO Status to Received
When one or more PO Lines have been partially received, there is an option to change the PO Status to Recevied in Edit PO's. This cancels the remaining items that were unreceived. However, when some PO Lines are fully received, and one or more lines are not received at all (no PO Line is partially received), then it's requried to visit each line to change the line status to cancelled in order to change the PO Status to Received. When the PO's are very long, it is time consuming and difficult to change the status on the individual lines when it is so much easier to accompli...

Extract data from one column to three columns
Hi, I would like to extract information from one column and put it into three columns: Sample Data: A1 Rochester, NY 14621 Desired Result: B1 Rochester C1 NY D1 14621 Thanks! Craig Hi select the column and goto 'Data - Text to columns' and choose a valid delimiter -- Regards Frank Kabel Frankfurt, Germany Craig wrote: > Hi, > > I would like to extract information from one column and > put it into three columns: > > Sample Data: > > A1 > Rochester, NY 14621 > > Desired Result: > > B1 > Rochester > > C1 > NY > > D1 ...

Ntext Data Type
How can I declare a NTEXT parameter within a sproc? The @newsstory parameter is a ntext data type and I can't get the syntax right. The table column it represents is ntext 8000 data type and being used for a fairly decent size news article. If anyone has a better idea for what data type would be better, I'd appreciate the advice. The db is on SQL2K, but I'm going to move to SQL2K8 soon. CODE ======= @NewsSummary varchar (750) OUTPUT, @NewsStory ntext OUTPUT, On Fri, 2 Jul 2010 11:01:04 -0500, "scott" <sbailey@mileslumber.com> wrote: ...

Email reply tracks but icon does not change in Outlook for some users
We have email replies tracking mostly successfully, but we still have one bug. When I receive a reply to a tracked email, the email shows as tracked in my Outlook summary view. However, for other users, the icon does not change. When they open up the email, it still gives them the option to track. But if I go into the contact record they used for Regarding, I can see the email in the history. So it's tracking, but they aren't seeing it as tracked. They have different rights than me in both CRM and in the domain. If they click the Track button, the Regarding will populate and the...

Can I paste data & column widths all at once?
Can I copy a range from one sheet to another and have everything about that range come with it? I notice that Paste Special has the Column Widths option. If I select it, the All option gets deselected. I can then go ahead and paste the column widths and then paste the range (Ctlr-V). This works, but is there a way to do it in one step? If you copy the entire column, then the columnwidth comes with it. Square Peg wrote: > > Can I copy a range from one sheet to another and have everything about > that range come with it? > > I notice that Paste Special has the Column Widths...

Monthly Summary of Data
I am having Daily Sales Figures for the entire year being (Invoice No., Date, Name of Seller, Net Sales Amount, Tax, Gross Sales Amount). Now if i want a Monthly summary from the entire data of say ( Net Sales, Tax, Gross Sales) how do i get it in excel. Hi I would use a Pivot Table to summarise the data. Take a look at Debra Dalgliesh's site for some guidance on how to sue Pivot Tables http://www.contextures.com/tiptech.html -- Regards Roger Govier "proshail" <proshail@discussions.microsoft.com> wrote in message news:C51FE172-7891-468A-A751-E63BEB19CD3C@microsof...

Data label doesn't match data point!
Hi, I currently have a number of graphs (10-12) that I need to replicate (for different data obviously) across 14 worksheets (approx 140-150 graphs in total). I am copying the relevant worksheet into the different work books and amending the source range, however, I have noticed that the data values do not automatically update (even when the rest of the graph does). So I can have a bar chart showing one figure and the data label saying another. Does anyone know of updating the data values automatically i.e. to reflect the data point that the data value is representing? If not, I’l...

Changing the Font on a comment line
Hi, I have fullpage receipt that I am customising. I want to be able to change the font settings for one of the comment lines. eg. Comment1 = "Thanks you for shopping at" comment2 = Store.Name I want to make comment2 bold. Is this possible? if so how? <IF> <CONDITION> ShowComments </CONDITION> <THEN> <TABLE> <BORDER> tbAll </BORDER> <TOP>PaperHeight - MarginBottom - FooterHeight + 0.2</TOP> <COLUMNHEADER> <ALIGNMENT&g...

combo box data changes based on previous combo box
Here's the example: Combo box number #1 has two choices - month or day... When I select "month" from combo box #1... I want Combo Box #2 to pull from a table that has "January, February, March, etc." However, if Combo Box #1 has "day" select.... Combo Box #2 shows "Monday, Tuesday, Wednesday, etc.". Anyone know how to do this? Anotehr workaround if this is not possible would be... if I could have all choices in combo box #2, and filter which ones show up in the the list based on what is selected in combo box #1. Thanks for the help! Damion ...

Lost Data #2
I just lost all my contacts/calendar information when I installed my Palm Software. Does anyone know how I can retrieve that data? ...

Linking sumif formula to the data
Is there a way to link a sumif formula to the data on a different worksheet that makes up the sum of the formula. If I click on the cell that contains the sum, the data that makes up that sum will show up on a different worksheet. Don't know if possible. Thanks. Certainly, you can address ranges in other worksheets, as in: =sumif(sheet2!A:A,"criteria",sheet3!B:B) Does that help? Fred "Jarod" <Jarod@discussions.microsoft.com> wrote in message news:AC36BBF8-7AC4-46A3-B0A7-0ECB0310E189@microsoft.com... > Is there a way to link a sumif formu...

Data validation in data form?
Is there any way to apply data validation when using a data form to restrict text length, etc. Thanks! Not using MS Data|form. But John Walkenbach's enhanced data form has lots of bells and whistles. You can find it at: http://j-walk.com/ss/dataform/index.htm I'm not sure if it has this capability, but for a nominal fee (20 USD), you can get the source code and modify the heck out of it. cottage6 wrote: > > Is there any way to apply data validation when using a data form to restrict > text length, etc. Thanks! -- Dave Peterson ...

Chart REALLY needs to support more than 255 data series
(FWIW -- This posting is via MS "Suggestion" feature, in turn reached via Excel's help... so at this point I am totally confused about what the heck is going to happen next -- submit suggestion? Post to usenet? Both??? Damned if I can tell from this UI.) Excel 2003 Chart continues to have a limit of 255 data series per chart, a rather surprising kind and size of limitation in 2005, given gazigabytes of memory etc. And a very nasty surprise if one has been counting on Chart to be at least modestly scalable. On big paper, with just a few points per series (generated via SQL...

Shared workbook/Track changes
I have a Shared workbook wherein we use the Track Changes feature. Is it possible to view or jump to the cells that have been changed instead of scrolling up, down and sideways? ...

Data Access Page Un-editable
I created a DAP from a form I have built, but none of the text fields, or check boxes allow me to edit them while in the DAP. I am trying to read up on DAP's but I have not found anything that helps me understand how to set them to be editable. Can someone help? more info for you: I am using office 2003 SP3 and IE7. I have installed OWC11. I have the access database and the DAP (HTML Document) saved in the same folder as the db. "Cameron" wrote: > I created a DAP from a form I have built, but none of the text fields, or > check boxes allow me to e...

Data Validation #28
How do you change either the size of the text box or the size of the source data in a drop down text box when created through data validation? You can't change the font or font size for a Data Validation dropdown list. To make the font appear larger, you can use programming to adjust the zoom setting when the cell is selected. There's sample code here: http://www.contextures.com/xlDataVal08.html#Larger Or, there's a sample file here that provides a combobox from which you can select one of the values from the data validation list. In thecombobox, you can set the font...

how much is too much data for a cell?
a cell should hold 32,000 characters if i am not mistaken. i have a large spreadsheet that has codes, their use, description and the # of times they have been 'hit'. most of the information fits in a cell very easily but i have one description that is huge. i made the cell 255x409 which is the max and what happened was part of the information showed and then the rest of the cell was blank. at that cell size there was more than enough room for all the data but for some reason it would not all show. i tried things like changing the alignment and it is on wrap text. i tried selectin...

Excluding Entire Data Fields in a Graph
I am trying to figure this out and there are a lot of other posts that skirt around this subject but none that specifically address my needs. I am using a bar graph and lets say for example I am using a chart of fruit picking that looks like this: Jun Jul Aug Sep Lemons 10 15 18 7 Apples 5 10 15 29 Oranges 9 20 17 33 Now my bar graph shows three bars labeled Lemons, Apples and Oranges. I have a drop down box where I can choose "Citrus" or "All Fruit" When I choose citrus the entire row of Apples in ...

Inseting data in cells
I have a row of cells that have last names in them, is there a way I can insert some data into all of the cells, this data will be the same for all names. Ex Before jones1 After (alias=jones1) then if possible export all of the names to a word or text file all in one line. Ex (alias=jones1)(alias=smith2)(alias=johnson3) Thanks for any help!!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Assuming last names are in A2:Z2 Put in A3 :...

Scatter Chart Changing Colors
I haver a Scatter chart in one of my workbooks. The color of the data series is set to default, which is a bright pink. However, on one of our computers, this graph shows up as neon green. On all other systems it works normally, except on this one computer. It is a problem because the green is very hard to see. I've tried changing the color and everytime it gives me a different color, with a kind of neon green tint. Is there any way to get rid of this? Nothing seems to work for me. Thanks Adam Bush ...