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?

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?

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?

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?

Bring Data from AccessDear 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 numberI 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 ValueHi 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 ColumnHi.
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 ReceivedWhen 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...

Ntext Data TypeHow 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 usersWe 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 DataI 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 lineHi,
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 boxHere'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 #2I 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 dataIs 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 changesI 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-editableI 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 #28How 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 GraphI 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 cellsI 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 ColorsI 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
...