I found something else that might help you:
This isn’t for the feint-hearted, but I had a client with a stuck payment that they couldn’t get ‘unattached’ from a document that was still in the open table. They needed that payment gone and the invoice opened back up so that they could pay it properly and clean up the books.
My goal was to delete anything related to the stuck payments (they had two different problem children). Since I couldn’t be sure exactly which tables were affected, I searched the whole database to make sure I found all of the transactions wherever they landed. For instance, you may have records in a third-party module like Mekorma. I searched on both the document number and the voucher number of the payments and the invoice they were trying to pay. What follows are the steps I went through to find and delete the stuck payments.
1. Once you are ready to apply your solution to production, everyone should log out of the production company and stay logged out until you’re finished. You can take the company offline if you want, but that’s not usually acceptable unless it’s absolutely necessary, like if you cannot perform your fix while anyone is logged in.
2. In preparation, I backed up production and restored the data into a test company. This is the MOST IMPORTANT task in the entire process. (the backing up and testing the restore).
3. After the backup, I searched all of the tables in the database for the following using the stored proc ‘spSearchOnAllDB’. I’ve included the text to create the proc and give credit to it’s authors at the end of this post.
a. The Voucher number of the invoice.
b. The Document number of the invoice.
c. The Voucher number of the payment.
d. The Document number of the payment.
4. Next, I looked through the results of my search and deleted any record that I knew didn't have anything to do with my transaction(s). For example, if some record in a SOP table had a Master Number that matched one of my search values. It's pretty easy to scan down the search results and pick out the ones that don't relate. If you don’t know, don't delete it. You’ll discover the answer later.
5. I ended up with a total of 41 tables that included relevant records. I copied the results to a new Excel spreadsheet. I converted the columns that would be copied over to Text. I changed them to Text so that Excel wouldn't truncate any of my values.
6. Next, I used Excel to build a select statement that would return the table and column that contained my search values. Then I evaluated the tables to weed out irrelevant records. Some of the tables will be listed more than once if both documents are referred to in the same table. For example, if you have a record in the Apply table, you will get two hits on the same record because each search value shows up in a single record, just in different fields.
7. I evaluated the results of my select statement and determined which records needed to be deleted or adjusted. Some of them were fine, like the general ledger transaction resulting from posting the invoice. Remember - my goal was to delete all records related to the stuck payment.
8. My evaluation resulted in 22 records that needed to be deleted or adjusted and 18 records that were fine.
9. I went back to my Excel spreadsheet and created a ‘delete’ query for those records needing to be deleted, and an ‘update’ statement for the records that just needed to be changed.
10. I searched the database again to confirm the results were what I expected.
11. In the end, only 9 unique tables were affected. I needed to delete or adjust 22 records, but they were all in just 9 tables. My client did not have any third-party modules.
12. I then launched GP and ran Reconcile and Check Links on the modules that included any of my 9 tables. In my case, it was Finnancial and Purchasing. If your database is small enough you could run it on everything. I like to do that, but it takes too long on a large database. Run Check Links until it returns no errors.
13. Now was the moment of truth. I went the Edit Check Batch window in Purchasing and selected the invoices impacted by stuck payments; I needed to make sure they were free to be paid. Success!
14. I backed up my test company.
15. I refreshed my test company with the production database once more and went through the process again.
16. I compared the first test company’s dataset totals to the current test company’s database to make sure the data was still correct. I just compare totals in the data tables rather than print reports. It’s WAY faster than printing a bunch of reports, and it’s easier to check more tables this way.
17. It worked again, so I moved to production and went through process on production.
18. I compared the production dataset to the test company’s to confirm that the data was correct.
19. Success again!
20. I then backed up both the Test company and the Production company databases.
21. Done!
Arguably, I could have just restored my completed test company over production, but I don’t like doing that. It’s common for clients to be very unhappy if they are locked out of production. They swear they will only print reports and run inquires – but that’s a promise that is often forgotten by somebody.
If I do this at night or over the weekend, I’d lock them out. That way, if somebody did try to log in they wouldn’t be able to. Don’t forget that the datasets might not be equal if an errant transaction was entered. It’s relatively easy to track down what was done, and it’s a step you have to take if your test totals do not match.
Like Harry Lee says “it’s better to take time than chances”.
That’s it. I’ve had to do this several times over the last 20 years, and now I’m sharing it with you all. Enjoy!
Kind regards,
Leslie
/*************************************************************************/
/* Procedure of search of a phrase on all database */
/* Originally developed by Oufimtsev Gleb, MCSE */
/* */
/* Updated by David Musgrave, Microsoft */
/* from feedback from Eduardo Barbosa & Marc K */
/* */
/* Last Modified: 26-Jul-2013 */
/* */
/* Search for spSearchOnAllDB at http://aka.ms/Dev4DynGP for more info */
/* */
/*************************************************************************/
if exists (select * from sysobjects where id = object_id('dbo.spSearchOnAllDB') )
drop procedure dbo.spSearchOnAllDB
GO
CREATE PROCEDURE spSearchOnAllDB @phrase varchar(8000), @OutFullRecords bit = 0 AS
/*
To apply sp:
exec spSearchOnAllDB 'Sugar%'
exec spSearchOnAllDB '%soft%'
exec spSearchOnAllDB '_5234_57%', 1
exec spSearchOnAllDB M_cro_oft
*/
declare @sql varchar(8000)
declare @tbl varchar(128)
declare @col varchar(128)
declare @id_present bit
declare @is_char_phrase bit
declare @min_len int
declare @loop_idx int
declare @loop_chr char(1)
set nocount on
if IsNull(@phrase, '') = '' begin
raiserror('Phrase is absent', 16, -1)
return
end
-- Handle Quotes passed in the search string
set @phrase = replace(@phrase, '''', '''''')
select @loop_idx = 1, @is_char_phrase = 0, @min_len = 0
while @loop_idx <= LEN(@phrase) begin
set @loop_chr = SUBSTRING(@phrase, @loop_idx,1)
if @loop_chr not in ('%', '_')
set @min_len = @min_len + 1
if @is_char_phrase = 0 and @loop_chr not in ('%', '_', '0', '1', '2', '3', '4', '5',
'6', '7', '8', '9', '.')
set @is_char_phrase = 1
set @loop_idx = @loop_idx + 1
end
create table #tbl_res
(TableName varchar(128) not NULL,
ColumnName varchar(128) not NULL,
Id int NULL,
ColumnValue varchar(7500) not NULL)
create table #tbl_res2
(TableName varchar(128) not NULL,
ColumnName varchar(128) not NULL,
Id int NULL,
ColumnValue varchar(7500) not NULL)
declare CRR cursor local fast_forward for
select t.name, c.name, 1
from sysobjects t, syscolumns c
where t.type = 'U'
and c.id = t.id
and c.status&0x80 = 0 -- Not IDENTITY
and exists (select * from syscolumns c2 where t.id = c2.id and c2.status&0x80 = 0x80
and c2.xtype in (48, 52, 56))
and ( (@is_char_phrase = 1 and c.xtype in (175, 239, 99, 231, 35, 167) and c.length
>= @min_len) -- char only
or (@is_char_phrase = 0 and c.xtype not in (34, 165, 173, 189, 61, 58, 36)))
-- char and numeric
union
select t.name, c.name, 0
from sysobjects t, syscolumns c
where t.type = 'U'
and c.id = t.id
and not exists (select * from syscolumns c2 where t.id=c2.id and c2.status&0x80 =
0x80 and c2.xtype in (48, 52, 56))
and ( (@is_char_phrase = 1 and c.xtype in (175, 239, 99, 231, 35, 167) and c.length
>= @min_len) -- char only
or (@is_char_phrase = 0 and c.xtype not in (34, 165, 173, 189, 61, 58, 36)))
-- char and numeric
order by 1, 2
open CRR
fetch CRR into @tbl, @col, @id_present
while @@FETCH_STATUS = 0 begin
if @OutFullRecords = 0 begin
set @sql = 'insert into #tbl_res (TableName, ColumnName, Id, ColumnValue) '
+ 'select ''[' + @tbl + ']'', ''[' + @col + ']'', '
if @id_present = 1
set @sql = @sql + 'IDENTITYCOL, '
else
set @sql = @sql + 'NULL, '
set @sql = @sql + 'convert(varchar(7500), [' + @col + ']) '
+ 'from [' + @tbl + '] (nolock) '
+ 'where convert(varchar(8000), [' + @col +
']) like ''' + @phrase + ''' '
end
if @OutFullRecords = 1 begin
set @sql = 'if exists (select * from [' + @tbl + '] (nolock) '
+ 'where convert(varchar(8000), [' + @col + ']) like ''' +
@phrase + ''') '
+ 'select ''[' + @tbl + ']'' TableName, ''[' + @col+ ']''
ColumnName, * '
+ 'from [' + @tbl + '] (nolock) where convert(varchar
(8000), [' + @col + ']) like ''' + @phrase + ''' '
end
exec(@sql)
fetch CRR into @tbl, @col, @id_present
end
close CRR
deallocate CRR
if @OutFullRecords = 0 begin
-- For the clients supporting new types:
--exec('select * from #tbl_res order by 1,2,3')
-- For the clients who are not supporting new types:
INSERT #tbl_res2
select TableName, ColumnName, Id, convert(varchar(255),ColumnValue) ColumnValue from
#tbl_res
/** exec('select TableName, ColumnName, Id, convert(varchar(255),ColumnValue)
ColumnValue from #tbl_res order by 1,2,3')**/
end
drop table #tbl_res
/***Select Statement to show tables***/
select TableName, ColumnName, ColumnValue from #tbl_res2 group by TableName, ColumnName,
ColumnValue
order by TableName
truncate table #tbl_res2
drop table #tbl_res2
RETURN
GO
--GRANT EXECUTE ON dbo.spSearchOnAllDB TO DYNGRP
/* Copyright © Microsoft Corporation. All Rights Reserved. */
/* This code released under the terms of the */
/* Microsoft Public License (MS-PL, opensource.org/.../ms-pl.html.) */
--exec spSearchOnAllDB '100XL%', 1
--exec spSearchOnAllDB '%''%', 0
Share
Report
All responses (
Answers (
65,271
