April 3, 2007 at 11:09 pm
while connecting to views in SQL Server 2000 I am getting the message "Server: Msg 4414, Level 16, State 1, Line 1 Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded."
Earlier I was getting the message max tables in a query (256) which got solved after installing SQL2000-KB884525-SP4-x86-ENU
Thanks
April 4, 2007 at 6:39 am
Are you querying over 200 tables? That's a problem in itself.
April 4, 2007 at 11:07 pm
I encountered this problem once before using views as the views I was querying were actually covering a large number of tables and underneath one was cross referencing the other - giving the huge number of tables included in the overall query.
Do you need to use queries that cover so many tables? Would it be simpler to query the tables directly? Perhaps you could post your query and we could offer a suggestion?
Catherine
Catherine Eibner
cybner.com.au
April 4, 2007 at 11:54 pm
Hi Catherine,
Thank you indeed, but we are querying different tables for different purposes hence cannot query individual tables
The following view is used with various views of quotations, purchase orders, inwards etc (do you want us to post those views also?)
CREATE VIEW dbo.V027PurchaseQuotation
AS
SELECT dbo.PurQuotationMas.PurQuoID, dbo.PurQuotationMas.PurQuoAliasCode + ' (' + dbo.PurQuotationMas.PurQuoVersion + ')' AS AliasCode,
dbo.PurQuotationMas.PurQuoDate, dbCompany.dbo.PurSaleReason.PurSaleReasonDesc,
dbo.V003TooMst.CommonName + ' | ' + dbo.V003TooMst.TOOName AS SupplierName,dbo.V003TooMst.CommonToomstId,dbo.V002TItemMaster.ItemName,
dbo.V002TItemMaster.ItemType, dbo.V002TItemMaster.ItemCode, dbo.V002TItemMaster.ItemTypeId, dbo.V002TItemMaster.ItemId,
dbo.UnitMaster.UnitMainId, dbo.V002TItemMaster.DecAllow, dbo.UnitMaster.UnitName,dbo.PurQuotationDet.MinQty,dbo.PurQuotationDet.GRNQty, dbo.PurQuotationDet.PurQuoReqQty,
dbo.PurQuotationDet.PurQuoRate, dbo.PurQuotationDet.PurQuoDiscType, dbo.PurQuotationDet.PurQuoDiscAmt, dbo.PurQuotationDet.PurQuoTotal,
dbo.PurQuotationDet.PurAltCurrId, dbo.PurQuotationDet.PurCurrValue, dbo.PurQuotationDet.PurQuoInquiryId, dbo.PurQuotationDet.PurQuoRequId,
dbo.PurQuotationDet.CurrConvMasId, PurEnquiryMas.PurEnAliasCode + ' (' + PurEnquiryMas.PurEnVersion + ')' AS PurInqAliasCode,PurQuotationFor
FROM dbo.PurQuotationMas INNER JOIN
dbo.PurQuotationDet ON dbo.PurQuotationMas.PurQuoID = dbo.PurQuotationDet.PurQuoMasId INNER JOIN
dbCompany.dbo.PurSaleReason ON dbo.PurQuotationMas.PurReason = dbCompany.dbo.PurSaleReason.PurSaleReasonId INNER JOIN
dbo.V002TItemMaster ON dbo.PurQuotationDet.ItemTypeMasId = dbo.V002TItemMaster.ItemTypeId AND
dbo.PurQuotationDet.ItemMasId = dbo.V002TItemMaster.ItemId INNER JOIN
dbo.V003TooMst ON dbo.PurQuotationMas.PurSuppMasId = dbo.V003TooMst.CommonToomstId INNER JOIN
dbo.UnitMaster ON dbo.PurQuotationDet.UnitMasId = dbo.UnitMaster.UnitMainId LEFT OUTER JOIN
dbo.PurEnquiryMas ON dbo.PurQuotationDet.PurQuoInquiryId = dbo.PurEnquiryMas.PurEnID
WHERE PurQuotationFor ='ITEM'
Union All
SELECT dbo.PurQuotationMas.PurQuoID, dbo.PurQuotationMas.PurQuoAliasCode + ' (' + dbo.PurQuotationMas.PurQuoVersion + ')' AS AliasCode,
dbo.PurQuotationMas.PurQuoDate, dbCompany.dbo.PurSaleReason.PurSaleReasonDesc,
dbo.V003TooMst.CommonName + ' | ' + dbo.V003TooMst.TOOName AS SupplierName,dbo.V003TooMst.CommonToomstId, dbo.V013ServiceMaster.ServiceName,
dbo.V013ServiceMaster.ServiceType, dbo.V013ServiceMaster.ServiceCode, dbo.V013ServiceMaster.ServiceTypeId, dbo.V013ServiceMaster.ServiceId,
dbo.UnitMaster.UnitMainId, NULL AS DecAllow, dbo.UnitMaster.UnitName,dbo.PurQuotationDet.MinQty,dbo.PurQuotationDet.GRNQty, dbo.PurQuotationDet.PurQuoReqQty, dbo.PurQuotationDet.PurQuoRate,
dbo.PurQuotationDet.PurQuoDiscType, dbo.PurQuotationDet.PurQuoDiscAmt, dbo.PurQuotationDet.PurQuoTotal, dbo.PurQuotationDet.PurAltCurrId,
dbo.PurQuotationDet.PurCurrValue, dbo.PurQuotationDet.PurQuoInquiryId, dbo.PurQuotationDet.PurQuoRequId, dbo.PurQuotationDet.CurrConvMasId,
PurEnquiryMas.PurEnAliasCode + ' (' + PurEnquiryMas.PurEnVersion + ')' AS PurInqAliasCode,PurQuotationFor
FROM dbo.PurQuotationMas INNER JOIN
dbo.PurQuotationDet ON dbo.PurQuotationMas.PurQuoID = dbo.PurQuotationDet.PurQuoMasId INNER JOIN
dbCompany.dbo.PurSaleReason ON dbo.PurQuotationMas.PurReason = dbCompany.dbo.PurSaleReason.PurSaleReasonId INNER JOIN
dbo.V013ServiceMaster ON dbo.PurQuotationDet.ItemTypeMasId = dbo.V013ServiceMaster.ServiceTypeId AND
dbo.PurQuotationDet.ItemMasId = dbo.V013ServiceMaster.ServiceId INNER JOIN
dbo.V003TooMst ON dbo.PurQuotationMas.PurSuppMasId = dbo.V003TooMst.CommonToomstId INNER JOIN
dbo.UnitMaster ON dbo.PurQuotationDet.UnitMasId = dbo.UnitMaster.UnitMainId LEFT OUTER JOIN
dbo.PurEnquiryMas ON dbo.PurQuotationDet.PurQuoInquiryId = dbo.PurEnquiryMas.PurEnID
WHERE PurQuotationFor ='SERVICE'
Union All
SELECT dbo.PurQuotationMas.PurQuoID, dbo.PurQuotationMas.PurQuoAliasCode + ' (' + dbo.PurQuotationMas.PurQuoVersion + ')' AS AliasCode,
dbo.PurQuotationMas.PurQuoDate, dbCompany.dbo.PurSaleReason.PurSaleReasonDesc,
dbo.V003TooMst.CommonName + ' | ' + dbo.V003TooMst.TOOName AS SupplierName,dbo.V003TooMst.CommonToomstId, dbo.V015MngtServiceMaster.MngtServiceName,
dbo.V015MngtServiceMaster.MngtServiceType, dbo.V015MngtServiceMaster.MngtServiceCode, dbo.V015MngtServiceMaster.MngtServiceTypeId, dbo.V015MngtServiceMaster.MngtServiceId,
dbo.UnitMaster.UnitMainId, NULL AS DecAllow, dbo.UnitMaster.UnitName,dbo.PurQuotationDet.MinQty,dbo.PurQuotationDet.GRNQty, dbo.PurQuotationDet.PurQuoReqQty, dbo.PurQuotationDet.PurQuoRate,
dbo.PurQuotationDet.PurQuoDiscType, dbo.PurQuotationDet.PurQuoDiscAmt, dbo.PurQuotationDet.PurQuoTotal, dbo.PurQuotationDet.PurAltCurrId,
dbo.PurQuotationDet.PurCurrValue, dbo.PurQuotationDet.PurQuoInquiryId, dbo.PurQuotationDet.PurQuoRequId, dbo.PurQuotationDet.CurrConvMasId,
PurEnquiryMas.PurEnAliasCode + ' (' + PurEnquiryMas.PurEnVersion + ')' AS PurInqAliasCode,PurQuotationFor
FROM dbo.PurQuotationMas INNER JOIN
dbo.PurQuotationDet ON dbo.PurQuotationMas.PurQuoID = dbo.PurQuotationDet.PurQuoMasId INNER JOIN
dbCompany.dbo.PurSaleReason ON dbo.PurQuotationMas.PurReason = dbCompany.dbo.PurSaleReason.PurSaleReasonId INNER JOIN
dbo.V015MngtServiceMaster ON dbo.PurQuotationDet.ItemTypeMasId = dbo.V015MngtServiceMaster.MngtServiceTypeId AND
dbo.PurQuotationDet.ItemMasId = dbo.V015MngtServiceMaster.MngtServiceId INNER JOIN
dbo.V003TooMst ON dbo.PurQuotationMas.PurSuppMasId = dbo.V003TooMst.CommonToomstId INNER JOIN
dbo.UnitMaster ON dbo.PurQuotationDet.UnitMasId = dbo.UnitMaster.UnitMainId LEFT OUTER JOIN
dbo.PurEnquiryMas ON dbo.PurQuotationDet.PurQuoInquiryId = dbo.PurEnquiryMas.PurEnID
WHERE PurQuotationFor ='SERVICEM'
April 5, 2007 at 3:04 am
When all is said and done, you have hit a limit on what SQL Server can do.
We had a similar problem with our data warehouse. We had to examine what the end-user queries (i.e. with real-life WHERE clauses) were doing and produce specific SQL for these queries. We were able to replace a few generalised views with a larger number of specific queries that did not reference tables not required by that query. (Typically, tables holding data outside the date range required were lost from the specific queries.)
If we had not got this working, an alternative would have been to produce the result set via a SP. We could then have split our final-form query into multiple queries that created and joined temporary tables.
Another approach would have been to materialise a low-level view into a permanent table. We looked at indexed views, but like you our low-level queries contained syntax that is not allowed in an indexed view. As the problem DB was a DW, we did have a viable alternative of loading a table with the required data after our daily DW load, but in the end we fixed it as above.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply