jwcolby
jwcolby at colbyconsulting.com
Fri Oct 29 12:55:55 CDT 2010
Glad you figured it out! John W. Colby www.ColbyConsulting.com On 10/29/2010 1:43 PM, Shamil Salakhetdinov wrote: > Hi All, > > Yes, the issue was with IN clause - here is the test solution: > > http://northwind.codeplex.com/releases/view/26600#DownloadId=162049 > > It has tow tests of the same join SQL with and without using IN clause. > Below is the sample output of the test case using IN clause - it fails. > When IN clause isn't used then it works OK. > > F:\Temp6\OleDbProviderTest\Bin>OleDbProviderTest.exe 1 > + ******** OleDbConnection Test ********* + > 1st arg = 1 - test case > 2nd arg = 100 max cycles qty > Test case #1: use IN '' clause > Test case #2: DO NOT use IN '' clause > + ************* TEST SQL **************** + > SELECT > Order.OrderID, Order.OrderDate, > Customer.CompanyName, Employee.FirstName, > Employee.LastName, Product.ProductName, > OrderDetail.Quantity, Category.CategoryName > FROM Customer > INNER JOIN ((select * from Employee IN '' > [;Database=F:\Temp6\OleDbProvider > Test\Bin\Northwind.NET.mdb]) as Employee > INNER JOIN (((select * from Category IN '' > [;Database=F:\Temp6\OleDbProvide > rTest\Bin\Northwind.NET.mdb]) as Category > INNER JOIN (select * from Product IN '' > [;Database=F:\Temp6\OleDbProviderTes > t\Bin\Northwind.NET.mdb]) as Product ON Category.CategoryID = > Product.CategoryID > ) > INNER JOIN ((select * from [Order] IN '' > [;Database=F:\Temp6\OleDbProviderTe > st\Bin\Northwind.NET.mdb]) as [Order] > INNER JOIN (select * from OrderDetail IN '' > [;Database=F:\Temp6\OleDbProvid > erTest\Bin\Northwind.NET.mdb]) as OrderDetail > ON Order.OrderID = OrderDetail.OrderID) > ON Product.ProductID = OrderDetail.ProductID) > ON Employee.EmployeeID = Order.EmployeeID) > ON Customer.CustomerId = Order.CustomerId > ORDER BY Order.OrderDate DESC > - ************* TEST SQL **************** - > 29/10/2010 21:36:31: 1. ThreadCount = 9 > 29/10/2010 21:36:32: 2. ThreadCount = 18 > 29/10/2010 21:36:32: 3. ThreadCount = 21 > 29/10/2010 21:36:33: 4. ThreadCount = 26 > 29/10/2010 21:36:33: 5. ThreadCount = 31 > 29/10/2010 21:36:33: 6. ThreadCount = 33 > 29/10/2010 21:36:34: 7. ThreadCount = 36 > 29/10/2010 21:36:34: 8. ThreadCount = 38 > 29/10/2010 21:36:34: 9. ThreadCount = 37 > 29/10/2010 21:36:35: 10. ThreadCount = 33 > 29/10/2010 21:36:35: 11. ThreadCount = 36 > 29/10/2010 21:36:36: 12. ThreadCount = 40 > 29/10/2010 21:36:36: 13. ThreadCount = 44 > 29/10/2010 21:36:36: 14. ThreadCount = 46 > 29/10/2010 21:36:37: 15. ThreadCount = 45 > 29/10/2010 21:36:37: 16. ThreadCount = 52 > 29/10/2010 21:36:37: 17. ThreadCount = 57 > 29/10/2010 21:36:38: 18. ThreadCount = 61 > 29/10/2010 21:36:38: 19. ThreadCount = 60 > 29/10/2010 21:36:38: 20. ThreadCount = 65 > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:39: 21. ThreadCount = 61 > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:39: 22. ThreadCount = 63 > TestConnection.ERROR:Unspecified error > 29/10/2010 21:36:39: 23. ThreadCount = 63 > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:40: 24. ThreadCount = 62 > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:40: 25. ThreadCount = 62 > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Unspecified error > 29/10/2010 21:36:40: 26. ThreadCount = 63 > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:41: 27. ThreadCount = 61 > 29/10/2010 21:36:41: 28. ThreadCount = 64 > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Cannot open any more databases. > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:41: 29. ThreadCount = 62 > TestConnection.ERROR:Unspecified error > 29/10/2010 21:36:42: 30. ThreadCount = 63 > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Unspecified error > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:42: 31. ThreadCount = 62 > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:42: 32. ThreadCount = 55 > TestConnection.ERROR:Cannot open any more databases. > 29/10/2010 21:36:43: 33. ThreadCount = 56 > StartTime = 29/10/2010 21:36:31, EndTime = 29/10/2010 21:36:43 > OK, TotalCount = 297, ElapsedTime = 0.0032 hours, ErrCount = 31 > Press any key to quit... > TestConnection.ERROR:Cannot open any more databases. > > > Thank you. > > -- > Shamil > > > <<< snip>>> > > _______________________________________________ > dba-VB mailing list > dba-VB at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-vb > http://www.databaseadvisors.com > >