Upgrading - need to breakout of toolkit with DTS for speed

itinkiam

I'm doing an upgrade that requires that I save a lot of the data, and move some data from one field number to another (add-on took our custom field numbers- argh). And I need to move complete tables to backup tables within the database.

I heard that DTS was a good way to do this? Can anyone give me examples of DTS packages to move data within the database?

DenSter

Sounds like the wrong approach to me. You will have to renumber fields anyway, why not renumber the ones that DON'T have data in them, and save yourself the time to also have to move data around.

By the way, if it were an officially registered add-on, you would not have been in this predicament, because those get their own unique numbers. Maybe a call to the company that created this "add-on" will make a difference, they might help you out renumbering their fields for you. I used to work for a partner that had one in the 70000 range, and I've had to renumber those fields a number of times.

itinkiam

Thanks for your input. That would have been a great idea and I'll certainly use it in the future. But now I'm on a time crunch and have inherited this mess. In my upgrade time, I'm spending about 12 hours simply moving and saving data (for various reasons). I really need to reduce the time on that and thought that DTS might help. I've heard of it being used, but haven't written a DTS before. It seems like it would be a simple matter if I only knew the syntax.

DenSter

I haven't used DTS myself, so someone else will have to chime in here. Doesn't it come with a wizard you can use?

David_Singleton

OK, I really shouldn't say this, since it really is the WRONG way to do this,
I wont give you a step by step, because if you don't know how to do this then don't do it, but...

I was in a similar situation a while ago. And the fix is extremely simple. In SQL fields are addressed by the NAME of the field. In NAV by the NUMBER. So changing a field number in NAV is slow, but changing a field NAME in SQL is very fast. So if you detach the NAV table definition from the SQL table (LinkedObject property) rename everything in SQL, change the numbers in NAV, and then just link back. It only takes a couple of minutes to handle a few million records. But make a mistake, and it will be a huge mistake.


And of course "This process was performed by a trained professional, please do not attempt this at home"

itinkiam

Thanks for the warnings David and Daniel. I'll try something else to see if I can get this done.

bbrown

DTS was a feature available in SQL 2000. This was replaced by SQL Server Integration Services (SSIS) in SQL 2005 and 2008. If you have no experience with these tools, a NAV upgrade is not the place to start learning.