Please, What could be the possible reasons why a query I created runs successfully and returns data when run in the SQL Server Query Analyzer but the same query always timeout when runs in the Enterprise Manager. Below is a typical example of the query. Can my methods of joins have such influence.

Thanks very much for your help.

SELECT DISTINCT tblPRODSITE.[PRODSITE SELECT], tblTYPESETTER.TYPESETTER_SELECT, tblPrinter.PRINTER_SELECT, tblJOURNALS.OPCO, tblOPCO.[OPCO NAME], tblJOURNALS.PMG, tblJOURNALS.ACRONYM, tblJOURNALS.VISTANR, tblJOURNALS.SPECIAL, vwQuery16.SUBSTYPE , vwQuery16.CATEGORY AS CATEGORY, vwQuery16.iYEAR AS Table1iYEAR, vwQuery16.SUBSYEAR AS Table1SUBSYEAR, vwQuery16.PRPERVOL AS Table1PRPERVOL, vwQuery16.SUBSEXCL AS Table1SUBEXCL, vwQuery16.DISCOUNT AS Table1DISCOUNT, vwQuery16.INVOEXCL AS Table1INVOEXCL, vwQuery16.NRVOLS AS Table1NRVOLS, vwQuery16.PPPERVOL AS Table1PPPERVOL , vwQuery16.T1EXPR1, vwQuery16.T1EXPR2, vwQuery17.CATEGORY AS Table2CATEGORY, vwQuery17.iYEAR AS Table2iYear, vwQuery17.SUBSYEAR AS Table2SUBSYEAR, vwQuery17.PRPERVOL AS Table2PRPERVOL, vwQuery17.SUBSEXCL AS Table2SUBSEXCL, vwQuery17.DISCOUNT AS Table2DISCOUNT, vwQuery17.INVOEXCL AS Table2INVOEXCL, vwQuery17.NRVOLS AS Table2NRVOLS, vwQuery17.PPPERVOL AS Table2PPPERVOL, vwQuery17.T2EXPR1, vwQuery17.T2EXPR2,

(vwQuery17.[SUBSEXCL]-vwQuery16.[SUBSEXCL])*vwQuery16.[PRPERVOL]*vwQuery16.[NRVOLS]*(100-vwQuery16.[DISCOUNT])/100000 AS Expr3,

(vwQuery17.[SUBSEXCL]-vwQuery16.[SUBSEXCL])*[T1Expr1]*vwQuery16.[NRVOLS]*(100-vwQuery16.[DISCOUNT])/100000 AS Expr4 ,

Expr5 = CASE WHEN vwQuery16.[PPPERVOL]=0 THEN 0 ELSE vwQuery17.[NRVOLS]*vwQuery17.[PPPERVOL]-vwQuery16.[NRVOLS]* vwQuery16.PPPERVOL* vwQuery17.[SUBSEXCL]*vwQuery16.PRPERVOL/vwQuery16.PPPERVOL*100-vwQuery16.[DISCOUNT]/100000 END,

Expr6 =CASE WHEN vwQuery16.[PPPERVOL]=0 THEN 0 ELSE vwQuery17.[NRVOLS]*vwQuery17.[PPPERVOL]-vwQuery16.[NRVOLS]* vwQuery16.PPPERVOL*vwQuery17.[SUBSEXCL]*[T1Expr1]/vwQuery16.PPPERVOL*(100-vwQuery16.[DISCOUNT])/100000 END, Expr7 = CASE WHEN vwQuery17.[PPPERVOL]=0 Or vwQuery16.PPPERVOL=0 THEN 0 ELSE vwQuery17.[PRPERVOL]/vwQuery17.[PPPERVOL]-vwQuery16.PRPERVOL/vwQuery16.PPPERVOL*vwQuery17.[SUBSEXCL]*vwQuery17.[NRVOLS]* vwQuery17.[PPPERVOL]*100-vwQuery17.[DISCOUNT]/100000 END ,

Expr8 = CASE WHEN vwQuery17.[PPPERVOL]=0 Or vwQuery16.PPPERVOL=0 THEN 0 ELSE [T2Expr1]/vwQuery17.[PPPERVOL]-[T1Expr1]/vwQuery16.PPPERVOL*vwQuery17.[SUBSEXCL]*vwQuery17.[NRVOLS]*vwQuery17.[PPPERVOL]* 100-vwQuery17.[DISCOUNT]/100000 END,

Expr9 = CASE WHEN vwQuery16.[PPPERVOL]=0 THEN 0 ELSE vwQuery16.[DISCOUNT]-vwQuery17.[DISCOUNT]*vwQuery17.[SUBSEXCL]* vwQuery17.[NRVOLS]*vwQuery17.[PPPERVOL]*vwQuery16.PRPERVOL/vwQuery16.PPPERVOL/100000 END , Expr10 = CASE WHEN vwQuery16.[PPPERVOL]=0 THEN 0 ELSE vwQuery16.[DISCOUNT]-vwQuery17.[DISCOUNT]*vwQuery17.[SUBSEXCL]* vwQuery17.[NRVOLS]*vwQuery17.[PPPERVOL]*[T1Expr1]/vwQuery16.PPPERVOL/100000 END ,

vwQuery17.[INVOEXCL]-vwQuery16.[INVOEXCL] AS Expr11, [T2Expr2]-[T1Expr2] AS Expr12, Expr13 = CASE WHEN vwQuery16.[NRVOLS]=0 And vwQuery17.[NRVOLS]>0 THEN "New/acquired/resumed/swap-in" WHEN vwQuery16.[NRVOLS]>0 And vwQuery17.[NRVOLS]=0 THEN "Stopped/sold/rescheduled/swap-out" ELSE "Ongoing"

END

FROM tblPrinter INNER JOIN tblJOURNALS ON tblPrinter.PRINTER_ACRONYM = tblJOURNALS.sPrinter INNER JOIN tblTYPESETTER ON tblTYPESETTER.TYPESETTER_ACRONYM = tblJOURNALS.sTypesetter INNER JOIN tblPRODSITE ON tblPRODSITE.[PRODUCTION SITE NAME] = tblJOURNALS.sProdLoca

INNER JOIN tblSPECIAL ON tblSPECIAL.[SPECIAL NAME] = tblJOURNALS.SPECIAL INNER JOIN tblOPCO ON tblOPCO.[OPCO NR] = tblJOURNALS.OPCO INNER JOIN vwQuery17 ON tblJOURNALS.ACRONYM = vwQuery17.ACRONYM AND tblJOURNALS.OPCO = vwQuery17.OPCO INNER JOIN vwQuery16 ON vwQuery16.OPCO = vwQuery17.OPCO AND vwQuery16.ACRONYM = vwQuery17.ACRONYM AND vwQuery16.SUBSTYPE = vwQuery17.SUBSTYPE INNER JOIN tblSUBTYPE ON tblSUBTYPE.SUBSTYPE = vwQuery16.SUBSTYPE

WHERE tblPRODSITE.[PRODSITE SELECT] =1 AND tblTYPESETTER.TYPESETTER_SELECT =1 AND tblPrinter.PRINTER_SELECT =1 AND tblJOURNALS.[JOURNAL SELECT] =1 AND tblOPCO.[OPCO SELECT] =1 AND tblSPECIAL.[SPECIAL SELECT] =1 AND tblSUBTYPE.[SUBSTYPE SELECT] =1

-- Anonymous, June 21, 2000

Answers

Jennifer,

You should double check that the timeout value for the server in Enterprise Manager is 0.

To do this, in Enterprise Manager, right click on the server name you are using, then click properties. Click on the Connections tab and make sure that in the Remote server connections section the Query time-out is set to 0.

Hope this helps,

Eric

-- Anonymous, June 21, 2000