2013 - Can't delete tables not licensed for?!

boywonder

Tried every trick I know but I just can't get rid of some old tables that I no longer want in a 2013 db, the problem is they are in the 6M range and so even a developer license will not allow deletion (bad design!).

I even tried doing this with a SQL script but ended up with a db that wouldn't backup as it complained about the deleted object number ](*,)

I'm sure I'm not the first to have this problem, any ideas would be much appreciated! #-o

mohana_cse06

try this blog
http://navisionary.com/2011/11/how-to-delete-bsolete-dynamics-nav-objects/

boywonder

Nope ... that's no use as you can't use the "create trick" when the object isn't in the licensed range (as the 2nd comment on that blog states).

Any other ideas?

mohana_cse06

Sorry :oops:

Lets wait together for solution :roll:

boywonder

No problem, it's not an easy one to solve. I can usually solve these but in 2013 my favourite trick isn't allowed ... I usually create a temporary report with the tables defined as variables, then issue a Table.DELETE; and then use Object.GET and Object.DELETE, works fine PRE 2013 but if you try this in 2013 it errors saying you can't manipulate objects other than in the development environment ](*,) ](*,) ](*,)

Marije_Brummel

How big is the database?

Can you create a new database and transfer the objects you want to preserve and then move the data using a backup/restore with data only?

boywonder

I already tried that, strangely the tables still come in even though I've executed a DELETEALL on each of them, I've also made sure there is only 1 Company in the db, so not a case of deleting from just one Company...

I should have also mentioned that the tables are a mix of compiled & uncompiled ones and it's the uncompiled ones causing me the most headache as they produce errors elsewhere in the system

Marije_Brummel

Can you try again?

If you make a fobfile, it should only contain the objects you select. If not, it's a bug.

boywonder

The fob was fine ... it was the backup that wasn't, even though data common and app. objects weren't selected those tables still get created ... so I've given up for now and have removed the objects by just using a restore of the demo database with the objects loaded on...

boywonder

still ](*,) ](*,) ](*,)

have found a database version with the objects in question compiled, checked via SQL that no records exist in the tables, yet when I backup (data only) and restore the tables are always created ... and I just can't get rid of them

Help!??!

JosephGress

I ran into this same issue recently. I resolved it by using two SQL queries
a) As you described in your first post, you need one SQL query to drop the tables from the SQL database.
b) You need a second SQL query to delete the records from the Object table that are not compiled.
Be sure you have a good backup before trying this. Be sure to test this in a test database. If you aren't sure what the SQL queries are doing, get someone to help you who does.

boywonder

Thanks for your reply Joseph, I guess this is the only way.

I have the following script, this example would remove a table named 'Setup Table' with ID 6010001 from the Company Cronus...

DELETE FROM [dbo].[Cronus$Setup Table]
DELETE from [dbo].[Object] where Type = 1 and ID = 6010001
DROP TABLE [dbo].[Cronus$Setup Table]
GO

This does remove the table but causes big problems, backup/restore stops working as it says the above table cannot be found.

Is that because of an entry in the Object table? If so what do I need to do in SQL to remove? Thanks

PS. this problem occurs whether the tables are compiled or not.

Marije_Brummel

I would not recommend editing object data this way, but since you do it anyway you might want to delete both the Table and TableData items from the object table.

boywonder

I hate doing it this way too ... but unless somebody can provide an alternative it seems there is no choice

boywonder

My old post came up on google! \:D/ but I was searching for a solution to this problem

I've got a similar problem again and still no clue how to solve, this db has a table in the 1BN object range for every object ie. (Table 3 Payment Terms, Table 1000000003 Payment Terms).

No clue how they got there and I need to get rid of them as they are preventing a restore.

Anybody?

Koshe

boywonder wrote:

I've got a similar problem again and still no clue how to solve, this db has a table in the 1BN object range for every object ie. (Table 3 Payment Terms, Table 1000000003 Payment Terms).

I've encountered similar problem before if I remember correctly - 1BN tables indicate that the backup restore didn't finish successfully for some reason. Try restoring backup again and see if those tables wouldn't be gone.

boywonder

The tables are in a customers live db .... ouch

Marije_Brummel

boywonder wrote:

My old post came up on google! \:D/ but I was searching for a solution to this problem

I've got a similar problem again and still no clue how to solve, this db has a table in the 1BN object range for every object ie. (Table 3 Payment Terms, Table 1000000003 Payment Terms).

No clue how they got there and I need to get rid of them as they are preventing a restore.

Anybody?

This is a different problem. The 1XXXXXXXXX tables indicate an unfinished restore. You should have a copy of each table in that range.

Complete the restore to get rid of the tables.

rdebath

The safest way to get rid of table due to a license problem (on NAV >= 5sp1) is to renumber the table and tabledata records in the object table from SQL into a range where you have delete permission. Then delete them from the Nav client.

Tables in the 1000000003.. range are NOT this problem.

To get rid of them you do Tools->Restore and when it asks you what you want to do with the partially restored data you say to delete it.

(EDIT: ****!! I will forget to look for the next page in this forum #-o )