Nav Database with linked table (SQL view) Restore errors

rlawson

Nav 5.0
I get the error "TableData 50004 does not exist" when restoring a backup with a SQL database (when I have 2 linked tables).
I read the MS Making Backups in the SQL Server Option page 19:
You must create a dummy view that selects literal values for its columns before restoring the backup. These literal values must be defined in terms of the appropriate SQL data type.

What does a dummy view mean?
How do I select literal values for its columns?
What is the syntax in SQL view?

SNielsen

Just grab the existing view on the sql server and replace the select statement with literal values like this:

USE [yourDBnamehere]
GO
/****** Object:  View [dbo].[CompanyName$TableName]    Script Date: 07/22/2008 19:26:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CompanyName$TableName]
AS
select 
  0 as TransactionNumber,
  '' as ExtOrderNumber,
  0 as BatchNumber,
  '' as ShippingNote1,
  '' as ShippingNote2,
  '' as ShippingNote3,
  '' as GiftMessage1,
  '' as GiftMessage2,
  '' as GiftMessage3

Just remember the data types have to map to your existing NAV fields.

rlawson

After changing the View with the following code:
Query for Navision Table 50003, RMS Transaction:

SELECT 0 AS TransactionNumber, ' ' AS ExtOrderNumber
Query for Navision Table 50004, RMS Notes:
SELECT 0 AS TransactionNumber, ' ' AS ExtOrderNumber, 0 AS BatchNumber, ' ' AS ShippingNote1, ' ' AS ShippingNote2, ' ' AS ShippingNote3,

' ' AS GiftMessage1, ' ' AS GiftMessage2, ' ' AS GiftMessage3



With these views in place for for my database, I are back to the message “Restoration of the backup was interrupted because the company ‘Crushpad TEST’ is already in the database.”



Any ideas?

SNielsen

Script your new views and save them. Delete the database, recreate it and restore the backup.

rlawson

I have multiple companies and am trying to restore just a data backup of one company. What do I do here?

David_Singleton

That all sounds very complex. The way I do it is this:

1/ Create the NAV SQL Database.
2/ Restore the Linked database.
3/ Run the scripts to build the views in the NAV database.
4/ Restore the FBK file.

Done.

DenSter

David Singleton wrote:

That all sounds very complex. The way I do it is this:

1/ Create the NAV SQL Database.
2/ Restore the Linked database.
3/ Run the scripts to build the views in the NAV database.
4/ Restore the FBK file.

Done.

Yep that's how I'd do it too. And if the linked objects don't link to an external database, you'd skip step 2. You would need the SQL Server objects that the NAV objects link to before restoring the NAV backup, so that the links will work right away and you don;t get the error messages.

bbrown

DenSter wrote:

David Singleton wrote:

That all sounds very complex. The way I do it is this:

1/ Create the NAV SQL Database.
2/ Restore the Linked database.
3/ Run the scripts to build the views in the NAV database.
4/ Restore the FBK file.

Done.

Yep that's how I'd do it too. And if the linked objects don't link to an external database, you'd skip step 2. You would need the SQL Server objects that the NAV objects link to before restoring the NAV backup, so that the links will work right away and you don;t get the error messages.

The problem is when the views refer to tables that won't exist until the FBK is restored. You can't create the real views until those tables exist.

David_Singleton

bbrown wrote:

DenSter wrote:

David Singleton wrote:

That all sounds very complex. The way I do it is this:

1/ Create the NAV SQL Database.
2/ Restore the Linked database.
3/ Run the scripts to build the views in the NAV database.
4/ Restore the FBK file.

Done.

Yep that's how I'd do it too. And if the linked objects don't link to an external database, you'd skip step 2. You would need the SQL Server objects that the NAV objects link to before restoring the NAV backup, so that the links will work right away and you don;t get the error messages.

The problem is when the views refer to tables that won't exist until the FBK is restored. You can't create the real views until those tables exist.

Are you refering to where you have created a view that is based on NAV tables, for example to make a JOIN?

bbrown

David Singleton wrote:

bbrown wrote:

DenSter wrote:

David Singleton wrote:

That all sounds very complex. The way I do it is this:

1/ Create the NAV SQL Database.
2/ Restore the Linked database.
3/ Run the scripts to build the views in the NAV database.
4/ Restore the FBK file.

Done.

Yep that's how I'd do it too. And if the linked objects don't link to an external database, you'd skip step 2. You would need the SQL Server objects that the NAV objects link to before restoring the NAV backup, so that the links will work right away and you don;t get the error messages.

The problem is when the views refer to tables that won't exist until the FBK is restored. You can't create the real views until those tables exist.

Are you refering to where you have created a view that is based on NAV tables, for example to make a JOIN?

That would be one example. A more common scenario I see is when a NAV table is involed in a a JOIN with external data. In these cases I create the dummy view, restore, then create the actual view.

This is also way I rarely (if ever) use the NAV backup/restore with SQL.

DenSter

I've only used views into non-NAV tables, so I've never had to deal with this. I've always thought that when you execute a CREATE script, SQL Server will let you create the view even when the tables don't exist yet. If that's not the case, the constant version of the view would be the only way to make that work.

What a drag though, you'd have to keep track of two versions of each view.

David_Singleton

DenSter wrote:

I've only used views into non-NAV tables, so I've never had to deal with this. I've always thought that when you execute a CREATE script, SQL Server will let you create the view even when the tables don't exist yet. If that's not the case, the constant version of the view would be the only way to make that work.

What a drag though, you'd have to keep track of two versions of each view.

I also though the same. I need to go back and check, because I was sure that what Daniel is saying worked.

SNielsen

Basically you would get a "Invalid object name 'DBName.dbo.Tablename'." error message when trying to create the view.

But again the dummy views with literal values are very easy to create, as suggested in the official documentation.

In this case though, the views refer to external databases - and i still dont know why it would error out on the restore - as i have restored it without problems.

rlawson

I am trying restore a Nav backup of a single company to a SQL Nav database which has views to a RMS database that has Joined Tables. The view that has no joined tables works fine; however, when I have a view with joined tables I get the error "Restoration of the backup was interrupted becuase the company ??? is already in the database. I guess the bottom line question is:
How would you create a test company with linked views to joined tables in a SQL database?

David_Singleton

SNielsen wrote:

Basically you would get a "Invalid object name 'DBName.dbo.Tablename'." error message when trying to create the view.

But again the dummy views with literal values are very easy to create, as suggested in the official documentation.

In this case though, the views refer to external databases - and i still dont know why it would error out on the restore - as i have restored it without problems.

Thanks,

I think that clarifies what bbrown was saying.

Thanks to both of you.

DenSter

Don't forget that if the linked table in NAV is company specific, that you will need a separate view for each company (Company1$SomeView, Company2$SomeView, etcetera). Inside NAV the linked table is called 'SomeView', but it will need one for each company.

rlawson

How exactly do I fix this issue? I have a view for each company but still get the error (due to the join of external tables) as stated in previous posts.

DenSter

I guess you'd have to create one of those dummy views for each company before restoring the backup.

rlawson

I have created a dummy view but due to a joined table I get the above error.

DenSter

Without access to your system I'm afraid I don't know how else to help you troubleshoot this issue.