Ciao, ho un problema in una vista che sto preparando su SQL Server 2000. Quando la verifico mi si presenta il seguente messaggio:

Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
Dalla documentazione MS sembra che il numero massimo di tabelle cui si fa riferimento in una vista sia di 260, anche considerando tabelle cui ci si riferisce tramite viste incluse in quella di partenza, però facendo il conto non capisco xchè nel mio caso avvenga l' errore.

http://support.microsoft.com/default...b;en-us;818406

A SELECT Transact-SQL statement can reference a maximum of 256 tables. This limit includes the table and the views that are referenced in the statement. The limit also includes the tables and the views that are referenced by a view that is included in the query. Therefore, if a table or a view is referenced more than one time in the query, each reference to the table or the view (or the tables and views that are referenced in the view) counts against this limit.
codice vista principale:
codice:
SELECT     *
FROM         (         SELECT     *
                       FROM         vista1
                       UNION ALL
                       SELECT     *
                       FROM         vista2
                       UNION ALL
                       SELECT     *
                       FROM         vista3
                       UNION ALL
                       SELECT     *
                       FROM         vista4
                       UNION ALL
                       SELECT     *
                       FROM         vista5
                       UNION ALL
                       SELECT     *
                       FROM         vista6
                       UNION ALL
                       SELECT     *
                       FROM         vista7
                       UNION ALL
                       SELECT     *
                       FROM         vista8
                       UNION ALL
                       SELECT     *
                       FROM         vista9
                       UNION ALL
                       SELECT     *
                       FROM         vista10
                       UNION ALL
                       SELECT     *
                       FROM         vista11
                       UNION ALL
                       SELECT     *
                       FROM         vista12
                       UNION ALL
                       SELECT     *
                       FROM         vista13
                       UNION ALL
                       SELECT     *
                       FROM         vista14
                       UNION ALL
                       SELECT     *
                       FROM         vista15
                       UNION ALL
                       SELECT     *
                       FROM         vista16
                       UNION ALL
                       SELECT     *
                       FROM         vista17
                       UNION ALL
                       SELECT     *
                       FROM         vista18
                       UNION ALL
                       SELECT     *
                       FROM         vista19
                       UNION ALL
                       SELECT     *
                       FROM         vista20) AS dati
WHERE     (campo1 IN ('val1', 'val2', 'val3', 'val4', 'val5', 'val6', 'val7', 'val8', 
                      'val9'))
ogni vista secondaria fa riferimento a 2 sole tabelle contenute in database diversi (20 in tutto)

vista 1:

codice:
SELECT     'tab1' AS PROVENIENZA, r.field1 AS Alias1, py.field2 AS Alias2, py.field3 AS Alias3, py.field4 AS Alias4,
                       py.field5 AS Alias5, py.field6 AS Alias6, py.field7 AS Alias7, py.field8 AS Alias8, 
                      py.field9 AS Alias9, py.field10 AS Alias10, py.field11 AS Alias11
FROM         tabella1 AS py INNER JOIN
                      tabella2 AS r ON py.fieldjoin = r.fieldjoin
WHERE     (r.field1 IS NOT NULL)
mi sembra che in tutto io usi 20 * 2 = 40 tabelle, x cui...

------

ooops :rollo: scoperto l'inghippo..

la vista1 fa riferimento ad 1 tabella r e ad una vista ptv (non me ne ero accorto!) che a sua volta si richiama a 14 tabelle!!

quindi 20 * (1 + 14) = 300 > 260

lascio cmq il post a futura memoria se qualcuno ne avesse bisogno (cercando non ho trovato nulla)