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)