[RELEASE] Item Removal From Multiple Tables

03/02/2013 05:58 BodySalvage#1
I have been asked several times in the past for a script like this and figured I would release it for the community. Its a really easy way to resolve a bad patch situation or various other possible situations where items appear in player possession that should not be in the game based on your desired setup. This will quickly remove an item from Guild WH, Player WH, AH, and character inventory.

Remove By ItemID:

Remove By ItemUID:

*UPDATE - Added basic checks to display messages according to the effected rows.

*NOTE I looked for a similar script release and found none, should this have been released in a different form then please close this thread.*
03/02/2013 18:43 Psycnosis#2
good script but you should also remove from auction board its a common place to forget :p
03/07/2013 12:47 GMCronus#3
i have to be stupid. but i place it here anyway..

i run this and it comes up with Please provide an ItemID

or that itemuid if i placve that there.. i place the itemuid in xxxxxx

SET @ItemID = 'xxxxxx';/* Edit this to contain the ItemID of the Item you wish to remove */

still i have the item in the database charitems and so on. what do i realy do wrong?
03/09/2013 23:38 danilokdn#4
restart the server?
03/09/2013 23:59 GMCronus#5
yes but should still not be in the db when you look for it? in the game yes but not in the db?
03/10/2013 01:51 BodySalvage#6
Quote:
Originally Posted by GMCronus View Post
i have to be stupid. but i place it here anyway..

i run this and it comes up with Please provide an ItemID

or that itemuid if i placve that there.. i place the itemuid in xxxxxx

SET @ItemID = 'xxxxxx';/* Edit this to contain the ItemID of the Item you wish to remove */

still i have the item in the database charitems and so on. what do i realy do wrong?
When running this, use ItemID and not the ItemUID. If you want to remove a specific ItemUID then this is not the script you want. This removes a specific ItemID(like Etains "100001") from everyone. To make this work for UID's just add a "U" to all ItemID and change the int to bigint like below.


** Added to initial release post as well.
03/10/2013 11:02 GMCronus#7
ok let me try and explain this:

Quote:
DECLARE @ItemID int

SET @ItemID = '100200';/* Edit this to contain the ItemID of the Item you wish to remove */

IF (@ItemID != NULL)
BEGIN
DELETE FROM PS_GameData.dbo.CharItems WHERE ItemID = @ItemID;
DELETE FROM PS_GameData.dbo.UserStoredItems WHERE ItemID = @ItemID;
DELETE FROM PS_GameData.dbo.GuildStoredItems WHERE ItemID = @ItemID;
DELETE FROM PS_GameData.dbo.MarketItems WHERE ItemID = @ItemID;
END
ELSE
BEGIN
PRINT 'Please provide an ItemID';
END
becouse i want to remove what i call DP Crystal from the game (just a test:-)

this gives me:

Quote:
Please provide an ItemID
as a message when i run it..

then i run

Quote:
SELECT
UserUID
,UserID
,c.CharID
,c.CharName
,i.ItemID
,i.ItemName
,SUM(ci.Count) as 'TotalCountOfItem'
FROM [PS_GameData].[dbo].[CharItems] ci with (nolock)
inner join [PS_GameDefs].[dbo].[Items] i with (nolock)
on i.ItemID = ci.ItemID
inner join [PS_GameData].[dbo].[Chars] c with (nolock)
on c.CharID = ci.CharID
where
i.ItemName like '%DP Crystal%' -- Insert part of item name here
group by
UserUID
,UserID
,c.CharID
,c.CharName
,i.ItemID
,i.ItemName
order by
i.ItemName asc, TotalCountOfItem desc, c.UserID asc, c.CharName asc
that still show the item ingame in many places (inventory of players)

so what did i do wrong?

should be removed here now right? from the db i mean or am i totaly off atm?