Strange behaviour of Full Outer Join in VB6 on SQL Server 2005

• kevin murphy

  Newbie

  Points: 4

  More actions

  May 8, 2007 at 7:57 am

  #76701

  I have the following scenario.

  I have the view with a full outer join:

  CREATE VIEW dbo.vwParentTreeView

  AS

  SELECT dbo.tblProjectTree.type, dbo.tblProjectTree.mpointid, tblProjectTreeParent.type AS ParentType, tblProjectTreeParent.mpointid AS Parent,

  dbo.tblProjectTree.name, tblProjectTreeParent.name AS ParentName

  FROM dbo.tblProjectTree FULL OUTER JOIN

  dbo.tblProjectTree tblProjectTreeParent ON dbo.tblProjectTree.parent = tblProjectTreeParent.mpointid

  Then I select from the view as follows.

  SELECT * FROM vwParentTreeView ORDER BY [Parent], [name]

  This returns data in both SQL Server 2005 and SQL Server 2000 when run from SQL Query Analyser. However when records are requested from this view from a VB6 application using ADODB to connect then only the SQL Server 2000 returns results and the SQL Server 2005 returns nothing. If the ORDER BY statement is removed then SQL server 2005 returns the correct records.

  Also when I changed the connection properties in the VB code from

  dynamic and optimistic to readonly and static then the correct results were returned.

  So in theory I can resolve the issue by building a new EXE and distributing it, but that will be a pain in the ass. Would much rather so something on the database.

  So it seems that the combination of a FULL OUTER JOIN and ORDER BY statements on SQL Server 2005 causes unpredictable behaviour.

  Any ideas/suggestions welcome.

  Regards

  Kevin

• bledu

  SSChampion

  Points: 12335

  More actions

  May 12, 2007 at 4:51 pm

  #706050

  you could try the m$ website

  http://msdn2.microsoft.com/en-us/library/ms131035.aspx

  but it might help if you post your vb6 code,

  ---

  Everything you can imagine is real.