What do i have to check to fix this?

Ramon Quintana

7y913Databases & DBA

ODBC FAIL in Access 2007-10

Query:

SELECT Cuentas_Maestro.Numero_cuenta, Cuentas_Maestro.Profit_Center, Cuentas_Maestro.Partner_Profit_Ctr, Cuentas_Maestro.Trans_Type, Cuentas_Maestro.profit_center_local_currency, Cuentas_Maestro.Curr_key, Cuentas_Maestro.Functional_Area, Cuentas_Maestro.Controlling_Area, Cuentas_Maestro.Fiscal_Year, Cuentas_Maestro.Origin_Object_num, Cuentas_Maestro.Period, Transaction_Type.Trans_Type, Transaction_Type.Trans_Type_Desc, [Accounts_Master-FS00].Numero_cuenta, [Accounts_Master-FS00].Descripcion_cuenta, Profit_Center_YH050.[Controlling _Area], Profit_Center_YH050.[Profit _Center], Profit_Center_YH050.[Business _Subsegment], KS13.[Controlling _Area], KS13.[Cost _Center], KS13.[Nombre_ Profit _Center], KS13.Funcionalidad, KS13.Profit_Center, Controlling_Area.Controlling_Area, Controlling_Area.Controlling_Area_Desc, Area_Funcional.Functional_area, Area_Funcional.Functional_area_Desc, Area_Funcional.Tipo_de_costo, Origin_Object.Origin_Object_num, Depth_Structure_local.[Business _Subsegment], Depth_Structure_local.[Business _Line], Depth_Structure_local.[Spiridon _2019], Depth_Structure_local.[Business _Area], Depth_Structure_local.[Cross _Div _Biz], Depth_Structure_local.[Short _Text], Origin_Object.Origin_Object_description
FROM Depth_Structure_local RIGHT JOIN (Controlling_Area RIGHT JOIN (Area_Funcional RIGHT JOIN (Transaction_Type RIGHT JOIN ((KS13 RIGHT JOIN Profit_Center_YH050 ON (KS13.Profit_Center = Profit_Center_YH050.[Profit _Center]) AND (KS13.[Controlling _Area] = Profit_Center_YH050.[Controlling _Area])) RIGHT JOIN (Origin_Object RIGHT JOIN ([Accounts_Master-FS00] RIGHT JOIN Cuentas_Maestro ON [Accounts_Master-FS00].Numero_cuenta = Cuentas_Maestro.Numero_cuenta) ON Origin_Object.Origin_Object_num = Cuentas_Maestro.Origin_Object_num) ON (Profit_Center_YH050.[Profit _Center] = Cuentas_Maestro.Profit_Center) AND (Profit_Center_YH050.[Controlling _Area] = Cuentas_Maestro.Controlling_Area)) ON Transaction_Type.Trans_Type = Cuentas_Maestro.Trans_Type) ON Area_Funcional.Functional_area = Cuentas_Maestro.Functional_Area) ON Controlling_Area.Controlling_Area = Cuentas_Maestro.Controlling_Area) ON Depth_Structure_local.[Spiridon _2019] = Profit_Center_YH050.[Business _Subsegment];

But in sql server it runs perfect.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramon Quintana7y ago
Hi Shweta,

Nothing Happened after increase timeout
Suraj Kumar7y ago
This is not the answer but a request please format your SQL query something like this. It is easy to read, understand and reply.
SELECT Cuentas_Maestro.Numero_cuenta
    ,Cuentas_Maestro.Profit_Center
    ,Cuentas_Maestro.Partner_Profit_Ctr
    ,Cuentas_Maestro.Trans_Type
    ,Cuentas_Maestro.profit_center_local_currency
    ,Cuentas_Maestro.Curr_key
    ,Cuentas_Maestro.Functional_Area
    ,Cuentas_Maestro.Controlling_Area
    ,Cuentas_Maestro.Fiscal_Year
    ,Cuentas_Maestro.Origin_Object_num
    ,Cuentas_Maestro.Period
    ,Transaction_Type.Trans_Type
    ,Transaction_Type.Trans_Type_Desc
    ,[Accounts_Master-FS00].Numero_cuenta
    ,[Accounts_Master-FS00].Descripcion_cuenta
    ,Profit_Center_YH050.[Controlling _Area]
    ,Profit_Center_YH050.[Profit _Center]
    ,Profit_Center_YH050.[Business _Subsegment]
    ,KS13.[Controlling _Area]
    ,KS13.[Cost _Center]
    ,KS13.[Nombre_ Profit _Center]
    ,KS13.Funcionalidad
    ,KS13.Profit_Center
    ,Controlling_Area.Controlling_Area
    ,Controlling_Area.Controlling_Area_Desc
    ,Area_Funcional.Functional_area
    ,Area_Funcional.Functional_area_Desc
    ,Area_Funcional.Tipo_de_costo
    ,Origin_Object.Origin_Object_num
    ,Depth_Structure_local.[Business _Subsegment]
    ,Depth_Structure_local.[Business _Line]
    ,Depth_Structure_local.[Spiridon _2019]
    ,Depth_Structure_local.[Business _Area]
    ,Depth_Structure_local.[Cross _Div _Biz]
    ,Depth_Structure_local.[Short _Text]
    ,Origin_Object.Origin_Object_description
FROM Depth_Structure_local
RIGHT JOIN (
    Controlling_Area RIGHT JOIN (
        Area_Funcional RIGHT JOIN (
            Transaction_Type RIGHT JOIN (
                (
                    KS13 RIGHT JOIN Profit_Center_YH050 ON (KS13.Profit_Center = Profit_Center_YH050.[Profit _Center])
                        AND (KS13.[Controlling _Area] = Profit_Center_YH050.[Controlling _Area])
                    ) RIGHT JOIN (
                    Origin_Object RIGHT JOIN (
                        [Accounts_Master-FS00] RIGHT JOIN Cuentas_Maestro ON [Accounts_Master-FS00].Numero_cuenta = Cuentas_Maestro.Numero_cuenta
                        ) ON Origin_Object.Origin_Object_num = Cuentas_Maestro.Origin_Object_num
                    ) ON (Profit_Center_YH050.[Profit _Center] = Cuentas_Maestro.Profit_Center)
                    AND (Profit_Center_YH050.[Controlling _Area] = Cuentas_Maestro.Controlling_Area)
                ) ON Transaction_Type.Trans_Type = Cuentas_Maestro.Trans_Type
            ) ON Area_Funcional.Functional_area = Cuentas_Maestro.Functional_Area
        ) ON Controlling_Area.Controlling_Area = Cuentas_Maestro.Controlling_Area
    ) ON Depth_Structure_local.[Spiridon _2019] = Profit_Center_YH050.[Business _Subsegment];
To easily format your long SQL Server query you can go to the below site:
http://poorsql.com/
Shweta Lodha7y ago
You can try to run the query again after increasing the odbc timeout from 60 to 180.