Deleting All Items at the store and Resending from HQ

Posted by Anthony Laidler on October 3, 2006, 7:49 pm

Please log in for more thread options

A question was asked of me so I am posting it here...What would happen if I
was to run an SQL Query and delete all the Items at the store level, and
follow it up with a 250 to send everything back to the store?

The problem that he is having is that he is seeing some items in the store
that may exist like with the same Item lookup number 50 plus times. Of course
I tried to run a Check and ReIndex and it had no effect. I tried to delete
just that itemlookupcode and it gives an Overflow Error. The way I was able
to delete one of the items was to run the Inventory Wizard and make that item
inactive, and then excuted a SQL query to delete all inactive and that got
rid of it since there was no other inactive items.

There is a KB on the CustomerSource that I sent him, that relates to
duplicates but it did not work for him in this case, and looking at his
database, he has several items this way. One item there has the same item
lookup like 200+ times. When I look at the database, it has all the same
info, store id, HQID, price, cost, everything is identical.

Thoughts???

Posted by Jeff on October 3, 2006, 9:20 pm

Please log in for more thread options

: quoted-printable

Anthony,

Sounds like you have some database corruption! Were they really able to =
complete a Check without issues?

To reduce it from happening again, if running MSDE, follow the =
directions in this KB;
http://tinyurl.com/or8kx

This solved some of my clients with the exact same problem (all running =
HQ too!) of duplicated items in the database. The only problem is that =
you have the delete the duplicated items manually, one at a time, =
because there is nothing unique about the duped item. Do it on the SO =
and HQ databases, really anything that is running MSDE!

I wouldn't do what you are describing without have a couple of backups =
and have tested it _real_ well!

--=20
*
Get Secure! - www.microsoft.com/security

You must be using Outlook Express or some other type of newsgroup reader =
to
see and download the file attachment. If you are not using a reader, =
follow
the link below to setup Outlook Express. Click on "Open with =
newsreader"
under the MS Retail Management System on the right.

http://tinyurl.com/75bgz

**********

A question was asked of me so I am posting it here...What would happen =
if I=20
was to run an SQL Query and delete all the Items at the store level, =
and=20
follow it up with a 250 to send everything back to the store?

The problem that he is having is that he is seeing some items in the =
store=20
that may exist like with the same Item lookup number 50 plus times. Of =
course=20
I tried to run a Check and ReIndex and it had no effect. I tried to =
delete=20
just that itemlookupcode and it gives an Overflow Error. The way I was =
able=20
to delete one of the items was to run the Inventory Wizard and make =
that item=20
inactive, and then excuted a SQL query to delete all inactive and that =
got=20
rid of it since there was no other inactive items.

There is a KB on the CustomerSource that I sent him, that relates to=20
duplicates but it did not work for him in this case, and looking at =
his=20
database, he has several items this way. One item there has the same =
item=20
lookup like 200+ times. When I look at the database, it has all the =
same=20
info, store id, HQID, price, cost, everything is identical.

Thoughts???
------=_NextPart_000_0500_01C6E718.96CF30B0
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT color=3D#008000>Anthony,</FONT></DIV>
<DIV><FONT color=3D#008000></FONT> </DIV>
<DIV><FONT color=3D#008000>Sounds like you have some database =
corruption! =20
Were they really able to complete a Check without issues?</FONT></DIV>
<DIV><FONT color=3D#008000></FONT> </DIV>
<DIV><FONT color=3D#008000>To reduce it from happening again, if running =
MSDE,=20
follow the directions in this KB;</FONT></DIV>
<DIV><FONT color=3D#008000><A=20
href=3D"http://tinyurl.com/or8kx">http://tinyurl.com/or8kx</A></FONT></DI=
V>
<DIV><FONT color=3D#008000></FONT> </DIV>
<DIV><FONT color=3D#008000>This solved some of my clients with the exact =
same=20
problem (all running HQ too!) of duplicated items in the database.  =
The=20
only problem is that you have the delete the duplicated items manually, =
one at a=20
time, because there is nothing unique about the duped item.  Do =
it on=20
the SO and HQ databases, really anything that is running =
MSDE!</FONT></DIV>
<DIV><FONT color=3D#008000></FONT> </DIV>
<DIV><FONT color=3D#008000>I wouldn't do what you are describing without =
have a=20
couple of backups and have tested it _real_ well!</FONT></DIV>
<DIV><FONT color=3D#008000></FONT> </DIV>
<DIV><FONT color=3D#008000></FONT> </DIV>
<DIV><BR>-- <BR>*<BR>Get Secure! - <A=20
href=3D"http://www.microsoft.com/security">www.microsoft.com/security</A>=
</DIV>
<DIV> </DIV>
<DIV>You must be using Outlook Express or some other type of newsgroup =
reader=20
to<BR>see and download the file attachment.  If you are not using a =
reader,=20
follow<BR>the link below to setup Outlook Express.  Click on "Open =
with=20
newsreader"<BR>under the MS Retail Management System on the right.</DIV>
<DIV> </DIV>
<DIV><A =
href=3D"http://tinyurl.com/75bgz">http://tinyurl.com/75bgz</A></DIV>
<DIV> </DIV>
<DIV>**********<BR></DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #008000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Anthony Laidler" <<A=20
=
iscussions.microsoft.com</A>>=20
wrote in message <A=20
=
5D071-52DC-41BB-992E-015D9FAD2DBC@microsoft.com</A>...</DIV>A=20
question was asked of me so I am posting it here...What would happen =
if I=20
<BR>was to run an SQL Query and delete all the Items at the store =
level, and=20
<BR>follow it up with a 250 to send everything back to the =
store?<BR><BR>The=20
problem that he is having is that he is seeing some items in the store =

<BR>that may exist like with the same Item lookup number 50 plus =
times. Of=20
course <BR>I tried to run a Check and ReIndex and it had no effect. I =
tried to=20
delete <BR>just that itemlookupcode and it gives an Overflow Error. =
The way I=20
was able <BR>to delete one of the items was to run the Inventory =
Wizard and=20
make that item <BR>inactive, and then excuted a SQL query to delete =
all=20
inactive and that got <BR>rid of it since there was no other inactive=20
items.<BR><BR>There is a KB on the CustomerSource that I sent him, =
that=20
relates to <BR>duplicates but it did not work for him in this case, =
and=20
looking at his <BR>database, he has several items this way. One item =
there has=20
the same item <BR>lookup like 200+ times. When I look at the database, =
it has=20
all the same <BR>info, store id, HQID, price, cost, everything is=20
identical.<BR><BR>Thoughts???</BLOCKQUOTE></BODY></HTML>

------=

Posted by Anthony Laidler on October 3, 2006, 10:29 pm

Please log in for more thread options

Was not planning to do it, just wanted to know if anyone has done this and
what impacts it would have.

"Jeff" wrote:

> Anthony,

> Sounds like you have some database corruption! Were they really able to

complete a Check without issues?

> To reduce it from happening again, if running MSDE, follow the directions in

this KB;

> http://tinyurl.com/or8kx

> This solved some of my clients with the exact same problem (all running HQ

too!) of duplicated items in the database. The only problem is that you have
the delete the duplicated items manually, one at a time, because there is
nothing unique about the duped item. Do it on the SO and HQ databases, really
anything that is running MSDE!

> I wouldn't do what you are describing without have a couple of backups and

have tested it _real_ well!

> --

> *

> Get Secure! - www.microsoft.com/security

> You must be using Outlook Express or some other type of newsgroup reader to

> see and download the file attachment. If you are not using a reader, follow

> the link below to setup Outlook Express. Click on "Open with newsreader"

> under the MS Retail Management System on the right.

> http://tinyurl.com/75bgz

> **********

> A question was asked of me so I am posting it here...What would happen if I

> was to run an SQL Query and delete all the Items at the store level, and

> follow it up with a 250 to send everything back to the store?

> The problem that he is having is that he is seeing some items in the store

> that may exist like with the same Item lookup number 50 plus times. Of

course

> I tried to run a Check and ReIndex and it had no effect. I tried to delete

> just that itemlookupcode and it gives an Overflow Error. The way I was able

> to delete one of the items was to run the Inventory Wizard and make that

item

> inactive, and then excuted a SQL query to delete all inactive and that got

> rid of it since there was no other inactive items.

> There is a KB on the CustomerSource that I sent him, that relates to

> duplicates but it did not work for him in this case, and looking at his

> database, he has several items this way. One item there has the same item

> lookup like 200+ times. When I look at the database, it has all the same

> info, store id, HQID, price, cost, everything is identical.

> Thoughts???

Posted by Glenn Adams [MVP - Retail Mgmt on October 4, 2006, 12:11 pm

Please log in for more thread options

The impact would be that all of your transaction history at the store
would be shot. All of that is linked to Item.ID. If you delete then
use a 250 to resend all items, you will be generating all new IDs and
none of the existing transaction entries will match them. Also, you
would need to clear out more than just the item table - SupplierList,
Alias, ItemClass & ItemClassComponent would all have to be cleared as
well - those are just the ones off the top of my head, there are
probably a few more...

I think that a better solution would be to export a new DB for that
store from HQ. Make sure that you have requested all Journals from the
store if you want them to be available after the export. Since all of
the duplicates share a common HQID, you should end up with all of the
history linked to a single instance of the item upon export. You should
also test this solution before implementing, but it should be pretty
easy to do.

Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@tibercreek.com
----------------------------------------------
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information.

Anthony Laidler wrote:

> Was not planning to do it, just wanted to know if anyone has done this and

> what impacts it would have.

> "Jeff" wrote:

>> Anthony,

>> Sounds like you have some database corruption! Were they really able to

complete a Check without issues?

>> To reduce it from happening again, if running MSDE, follow the directions in

this KB;

>> http://tinyurl.com/or8kx

>> This solved some of my clients with the exact same problem (all running HQ

>> I wouldn't do what you are describing without have a couple of backups and

have tested it _real_ well!

>> --

>> *

>> Get Secure! - www.microsoft.com/security

>> You must be using Outlook Express or some other type of newsgroup reader to

>> see and download the file attachment. If you are not using a reader, follow

>> the link below to setup Outlook Express. Click on "Open with newsreader"

>> under the MS Retail Management System on the right.

>> http://tinyurl.com/75bgz

>> **********

>> A question was asked of me so I am posting it here...What would happen if I

>> was to run an SQL Query and delete all the Items at the store level, and

>> follow it up with a 250 to send everything back to the store?

>> The problem that he is having is that he is seeing some items in the store

>> that may exist like with the same Item lookup number 50 plus times. Of

course

>> I tried to run a Check and ReIndex and it had no effect. I tried to delete

>> just that itemlookupcode and it gives an Overflow Error. The way I was able

>> to delete one of the items was to run the Inventory Wizard and make that

item

>> inactive, and then excuted a SQL query to delete all inactive and that got

>> rid of it since there was no other inactive items.

>> There is a KB on the CustomerSource that I sent him, that relates to

>> duplicates but it did not work for him in this case, and looking at his

>> database, he has several items this way. One item there has the same item

>> lookup like 200+ times. When I look at the database, it has all the same

>> info, store id, HQID, price, cost, everything is identical.

>> Thoughts???