Sql Database with linked table (SQL view) Restore errors

zutzu

NAV 5 sp 1, sql server 2005.
I want to restore a sql database with linked table (sql view).
I have read this post:
http://www.mibuso.com/forum/viewtopic.php?f=23&t=27236&hilit=restore+linked
and i create a dummy view, after creating database from NAV.
It starts restoring database without problems (without dummy view, it stopped) with this dummy view.
But when it is finishing, this error appears:

Sql server error accesing XXX (linked table):
an object or column is missing or empty. For select into statements, verify each column has a name.
For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed.
Add a name or single space as the alias name.

SQL:
Create table XXX (a lot of fields, ...) CONSTRAINT "" PRIMARY KEY CLUSTERED (the fields)

bbrown

Don't create an index on the dummy view.

zutzu

The dummy view has no index.
The Navision table linked with with the view has index.

bbrown

:oops: I need to read closer

Could you post your CREATE VIEW statement? Do you have any NULL values being returned?

zutzu

CREATE VIEW [dbo].[XXX$Trazabilidad Doc_ Compras]
AS
SELECT 'X' AS [No_ Solicitud], 1 AS [No_ pedido], 1 AS [Line No_], GETDATE() AS [Order Date], 'X' AS [No_ Vendor], 'X' AS [Nombre proveedor],
'X' AS [No_ producto], 'X' AS [Descripción producto], 1.1 AS [Cant_ pedido], 'X' AS [Valor dim_ 1], 'X' AS [Dim_ 1 name], 'X' AS [No_ Albarán], GETDATE()
AS [Fecha Albarán], 'X' AS [No_ Factura], GETDATE() AS [Fecha factura], 1.1 AS [Cant_ albarán], 1.1 AS [Cant_ factura], 1.1 AS [Cant_ a recibir],
1.1 AS [Cant_ a facturar], 'X' AS [No_ alb_ int_], 'X' AS [No_ fac_ int_]
FROM dbo.[Record Link]

There is no 'null' returned:

X 1 1 06/11/2008 14:29:05 X X X X 1,1 X X X 06/11/2008 14:29:05 X 06/11/2008 14:29:05 1,1 1,1 1,1 1,1 X X

davmac1

create it as a dummy table instead, then replace it with the real view after you have restored.
since your select looks like data per company = yes, if you have more than one company, repeat it for each company.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]