add RECOMPILE for specific query server wide

Question

Hi,

I've SQL 2017 and i have a query performance problem. i did try to play with all those trace flag (4199,4136,4137,9481)server wide and play with the indexes and stats and the query is still running slow. the only thing that resolve the issue is if i add the OPTION  (RECOMPILE) to the query. my problem is that i can't add it to query itself that comes from the ERP program.can i use it server wide for specific query?

---

Avi.G

Answer 1

can i use it server wide for specific query?

Simple answer: No.

You could run frequently DBCC FREEPROCCACHE (Transact-SQL) to delete cached query plans, but that will cause problem and won't solve your main problem. Better solve the root cause of DB design & queries, but that would be the task of the ERP software vendor.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

i did try the  DBCC FREEPROCCACHE buy it is not working. only if i add the OPTION  (RECOMPILE) to the query it is working good. 1sec with OPTION  (RECOMPILE) , 25sec without it.

---

Avi.G

Answer 3

Is the query executed directly by external program you have no control of or you can somehow modify the way it's called? 

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Answer 4

Did you see my reply on StackExchange? Try that, i.e, a plan guide.

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 5

I didn't since you didn't add a link.

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

My reply was to Avi, not you Naomi. Avi asked that exact same question on SE, but it seems my reply there wasn't satisfactory, so the same question was then posted here. 

---

Tibor Karaszi, SQL Server MVP (Web Blog)

Answer 6

the query is coming from an ERP program that i can't change the query to put the OPTION  (RECOMPILE) in it.so i tried the SQL Plan guide but it doesn't work for me. the query is very long and i put it extactly as i see it in SQL profiler but it doesn't work.

this is the query that i see in profiler for the event class sp:stmtcompleted

select 	                ncenter.dbo.CUSTOMERS.CUSTNAME , case when ( ( coalesce( ncenter.dbo.NSCUST.CUSTDES , '' ) = rtrim(ltrim(reverse( @P1 ))) ) ) then ( ncenter.dbo.CUSTOMERS.CUSTDES ) else ( coalesce( ncenter.dbo.NSCUST.CUSTDES , '' ) ) end , ncenter.dbo.PHONEBOOK.NAME , ncenter.dbo.PHONEBOOK.POSITIONDES , ncenter.dbo.ORDERS.CURDATE , ncenter.dbo.ORDERS.ORDNAME , ncenter.dbo.ORDERS.REFERENCE , ncenter.dbo.ORDERS.BOOKNUM , ncenter.dbo.DOCUMENTS.DOCNO , ncenter.dbo.DOCPROJ.PROJDES , ncenter.dbo.ORDSTATUS.ORDSTATUSDES , case when ( ( ncenter.dbo.ORDERS.CLOSED =  'C' ) ) then (  'Y' ) else (  ' ' ) end , case when ( ( ncenter.dbo.ORDERS.PCLOSED =  'C' ) ) then (  'Y' ) else (  ' ' ) end , ncenter.dbo.ORDERS.FORECASTFLAG , ncenter.dbo.CPROF.CPROFNUM , ncenter.dbo.PRICELIST.PLNAME , ncenter.dbo.DEAL.ORDNAME , ncenter.dbo.DEALA.YOURORDNAME , ncenter.dbo.ORDERS.DETAILS , ncenter.dbo.ORDERSA.STATUSDATE , ncenter.dbo.ORDERSA.RMADOCNUM , coalesce( ncenter.dbo.ORDERSB.DUEDATE , 0 ) , ncenter.dbo.AGENTS.AGENTCODE , ncenter.dbo.AGENTS.AGENTNAME , ncenter.dbo.BRANCHES.BRANCHNAME , ncenter.dbo.DESTCODES.CODE , ncenter.dbo.DESTCODES.CODEDES , ncenter.dbo.SHIPTYPES.STCODE , ncenter.dbo.SHIPTYPES.STDES , ncenter.dbo.CPROFTYPES.TYPECODE , ncenter.dbo.CPROFTYPES.TYPEDES , ncenter.dbo.MODELS.MODELNAME , ncenter.dbo.ORDERSA.QUANT , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.QPRICE) )) , (0.0 + ( convert(decimal(11,2), ncenter.dbo.ORDERS.T$PERCENT) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.VAT) )) , ncenter.dbo.TAXES.TAXCODE , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.TOTPRICE) )) , ncenter.dbo.CURRENCIES.CODE , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.QPROFIT) )) , (0.0 + ( case when ( ( convert( int , round( ncenter.dbo.LOGCONST.VALUE ,0)) = @P2 ) ) then ( case when ( ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) = 0.00 ) ) then ( 0.000000000 ) else ( ( 100.000000000 * convert(decimal(19,2), ncenter.dbo.ORDERS.QPROFIT) ) / (case when ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) ) = 0 then 1 else ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) ) end) ) end ) when ( ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) = 0.00 ) ) then ( 0.000000000 ) else ( ( 100.000000000 * convert(decimal(19,2), ncenter.dbo.ORDERS.QPROFIT) ) / (case when ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) ) = 0 then 1 else ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) ) end) ) end )) , CURRENCIES1.CODE , (0.0 + ( ( convert(decimal(27,9), ncenter.dbo.ORDERS.LEXCHANGE) * CURRENCIES1.EXCHQUANT ) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERSA.LEXCHTOL) )) , ncenter.dbo.ORDERSA.LEXCHNEG , ncenter.dbo.ORDERS.ADJPRICEFLAG , ncenter.dbo.ADJPRICES.ADJPRICEDES , ncenter.dbo.ORDERSA.LINKOPTIONS , ncenter.dbo.PAY.PAYCODE , ncenter.dbo.PAY.PAYDES , case when ( ( ncenter.dbo.ORDERSA.OBLIGOFLAG =  'Y' ) ) then (  ' ' ) else (  'Y' ) end , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.ADVBAL) )) , (0.0 + ( convert(decimal(9,2), ncenter.dbo.ORDERS.ADVPERCENT) )) , case when ( ( ncenter.dbo.CUSTOMERS.VATNUM <> @P3 ) ) then ( ncenter.dbo.CUSTOMERS.VATNUM ) else ( ncenter.dbo.ORDERSA.VATNUM ) end , system.dbo.USERS.USERLOGIN , coalesce( USERS2.USERLOGIN , '' ) , coalesce( USERS3.USERLOGIN , '' ) , coalesce( ncenter.dbo.ORDERSCHED.SDATE , 0 ) , case when ( ( coalesce( ncenter.dbo.ORDERSCHED.SDATE , 0 ) <> @P4 ) ) then ( substring( system .dbo.tabula_dtoa( coalesce( ncenter.dbo.ORDERSCHED.SDATE , 0 ) , 'day' , '' , 'א  ,ב  ,ג  ,ד  ,ה  ,ו  ,ש  ' ) , 1, 3) ) else ( @P5 ) end , coalesce( ncenter.dbo.ORDERSCHED.STIME , 0 ) , coalesce( ncenter.dbo.ORDERSCHED.EDATE , 0 ) , case when ( ( coalesce( ncenter.dbo.ORDERSCHED.EDATE , 0 ) <> @P6 ) ) then ( substring( system .dbo.tabula_dtoa( coalesce( ncenter.dbo.ORDERSCHED.EDATE , 0 ) , 'day' , '' , 'א  ,ב  ,ג  ,ד  ,ה  ,ו  ,ש  ' ) , 1, 3) ) else ( @P7 ) end , coalesce( ncenter.dbo.ORDERSCHED.ETIME , 0 ) , WAREHOUSES1.WARHSNAME , WAREHOUSES1.LOCNAME , WAREHOUSES1.WARHSDES , ncenter.dbo.WAREHOUSES.WARHSNAME , ncenter.dbo.WAREHOUSES.LOCNAME , ncenter.dbo.WAREHOUSES.WARHSDES , ncenter.dbo.ORDERS.OPENDCH , ncenter.dbo.ORDERSA.EXTFILEFLAG , ncenter.dbo.ORDERSA.BONUSFLAG , ncenter.dbo.CUSTCAMPAIGNS.CCNUM , ncenter.dbo.TERRITORIES.TERRITORYCODE , ncenter.dbo.TERRITORIES.TERRITORYDES , (0.0 + ( convert(decimal(20,3), ncenter.dbo.ORDERSA.TOTQUANT) )) , ncenter.dbo.PORDERS.ORDNAME , case when ( ( ncenter.dbo.ORDERS.SIM_PORD <> @P8 ) ) then ( @P9 ) else ( @P10 ) end , case when ( ( convert(decimal(19,2), ncenter.dbo.ORDERSA.PIKALONEFLAG) = 0.00 ) ) then (  ' ' ) else (  'Y' ) end , coalesce( ncenter.dbo.STORAGEZONES.STZONECODE , '' ) , coalesce( ncenter.dbo.DISTRLINES.DISTRLINECODE , '' ) , coalesce( ncenter.dbo.DISTRLINES.DISTRLINEDES , '' ) , coalesce( ncenter.dbo.STORAGEZONES.STZONEDES , '' ) , coalesce( ncenter.dbo.DOCTYPES.DOCCODE , '' ) , ncenter.dbo.ORDERS.SIM_CHLINE , coalesce( ncenter.dbo.DOCTYPES.DOCDES , '' ) , coalesce( DESTCODES1.CODE , '' ) , ncenter.dbo.ORDERS.SIM_CUSTHOME , coalesce( DESTCODES1.CODEDES , '' ) , coalesce( ncenter.dbo.ORDERSB.LFROMDATE , 0 ) , coalesce( ncenter.dbo.ORDERSB.LBASEDATE , 0 ) , (0.0 + ( coalesce( convert(decimal(19,2), ncenter.dbo.ORDERSB.LPERCENT) , 0.0 ) )) , coalesce( ncenter.dbo.ORDERSB.SHIPREMARK , '' ) , coalesce( ncenter.dbo.ORDERSB.GPSX , '' ) , coalesce( ncenter.dbo.ORDERSB.GPSY , '' ) , coalesce( ncenter.dbo.ORDERSB.PIKORDER , 0 ) , coalesce( ncenter.dbo.INCPARTEXT.FLAG , '' ) , coalesce( ncenter.dbo.ORDERSB.CONTCHARGE , '' ) , coalesce( ncenter.dbo.ORDERSB.CHARGEPERIOD , 0 ) , coalesce( ncenter.dbo.CONTPAYOPTIONS.CONTPAYNAME , '' ) , ncenter.dbo.ORDERSA.CURVERSION , ncenter.dbo.CPROFTYPES.SUBSCRIBEFLAG , coalesce( ncenter.dbo.ORDERSB.CALCVATFLAG , '' ) , CUSTOMERS9.CUSTNAME , ncenter.dbo.ORDERS.NSC_SORTORDER , CUSTOMERS9.CUSTDES , ncenter.dbo.ORDERS.NSC_NAUTOSORT , ncenter.dbo.ORDERS.NSC_OPENDATE , case when ( CUSTOMERS9.PHONE like ltrim(rtrim( @P11 )) ) then ( CUSTOMERS9.PHONE ) else ( CUSTOMERS9.NSC_PHONETWO ) end , ncenter.dbo.ORDERS.NSC_INSTITUTION , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.NSC_ALREADYPAID) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.NSC_LEFTTOPAID) )) , ncenter.dbo.ORDERS.NSC_SMSDATESENT , coalesce( ncenter.dbo.SIM_ORDERS.NSC_MANUALAPPRBILL , '' ) , ncenter.dbo.ORDERS.NSC_LINKTOWEB , (0.0 + ( coalesce( convert(decimal(19,2), ncenter.dbo.SIM_ORDERS.NSC_PREVTOTPRICE) , 0.0 ) )) , (0.0 + ( coalesce( convert(decimal(19,2), ncenter.dbo.SIM_ORDERS.NSC_TOTPRICE) , 0.0 ) )) , coalesce( ncenter.dbo.SIM_ORDERS.NSC_UDATE , 0 ) , USERS9.USERLOGIN , 
ncenter.dbo.ORDERS.NSC_WEBINTERFACE , ncenter.dbo.ORDERS.SIM_UNIFAYDELIVERY , ncenter.dbo.ORDERS.SIM_URGENT , DISTRLINES9.DISTRLINECODE , DISTRLINES9.DISTRLINEDES , ncenter.dbo.ORDERS.SIM_DISTLOCATION , ncenter.dbo.ORDERS.SIM_APPIRRUNIT , 
ncenter.dbo.ORDERSA.SIM_DUEDATE , ncenter.dbo.ORDSTATUS.NSC_DOCFLAG , coalesce( ncenter.dbo.SIM_ORDERS.NSC_WRKDAYSTODELIVER , 0 ) , coalesce( ncenter.dbo.SIM_ORDERS.NSC_ORDTIME , 0 ) , ncenter.dbo.CSL_FIELDS.FIELDCODE , ncenter.dbo.CSL_FIELDS.FIELDES , coalesce( 
ncenter.dbo.SIM_TEUM.CODE , '' ) , coalesce( ncenter.dbo.SIM_TEUM.DES , '' ) , coalesce( DOCUMENTS9.DOCNO , '' ) , ncenter.dbo.ORDERS.NSC_STATDATE , ncenter.dbo.ORDERS.SIM_DELAYREASON , ncenter.dbo.ORDERS.SIM_NOENTRYCHECK , ncenter.dbo.ORDERS.CSL_FIELD , 
ncenter.dbo.CUSTOMERS.CUSTDES , ncenter.dbo.CUSTOMERS.AGENT , ncenter.dbo.ORDERS.TYPE , ncenter.dbo.ORDERS.CLOSED , ncenter.dbo.ORDERS.PCLOSED , (0.0 + ( convert(decimal(27,9), ncenter.dbo.ORDERS.LEXCHANGE) )) , ncenter.dbo.ORDERS.DOER , case when ( ( 
CUSTOMERS1.ACCOUNT <> @P12 ) ) then ( CUSTOMERS1.ACCOUNT ) else ( ncenter.dbo.CUSTOMERS.ACCOUNT ) end , case when ( ( coalesce( ncenter.dbo.NSCUST.ADDRESS , '' ) <> rtrim(ltrim(reverse( @P13 ))) ) ) then ( coalesce( ncenter.dbo.NSCUST.STATE , '' ) + reverse( reverse( 
coalesce( ncenter.dbo.NSCUST.ADDRESS , '' ) ) + @P14 ) ) when ( ( ncenter.dbo.DESTCODES.ADDRESS <> rtrim(ltrim(reverse( @P15 ))) ) ) then ( ncenter.dbo.DESTCODES.STATE + reverse( reverse( ncenter.dbo.DESTCODES.ADDRESS ) + @P16 ) ) else ( ncenter.dbo.CUSTOMERS.STATE + 
reverse( reverse( ncenter.dbo.CUSTOMERS.ADDRESS ) + @P17 ) ) end , ncenter.dbo.PHONEBOOK.NAME , case when ( ( coalesce( ncenter.dbo.NSCUST.FAX , '' ) <> @P18 ) ) then ( coalesce( ncenter.dbo.NSCUST.FAX , '' ) ) when ( ( ncenter.dbo.PHONEBOOK.FAX <> @P19 ) ) then ( 
ncenter.dbo.PHONEBOOK.FAX ) else ( ncenter.dbo.CUSTOMERS.FAX ) end , ncenter.dbo.ORDERS.AGENT , case when ( ( coalesce( ncenter.dbo.NSCUST.EMAIL , '' ) <> @P20 ) ) then ( coalesce( ncenter.dbo.NSCUST.EMAIL , '' ) ) when ( ( ncenter.dbo.PHONEBOOK.EMAIL <> @P21 ) ) then ( ncenter.dbo.PHONEBOOK.EMAIL ) else ( coalesce( ncenter.dbo.CUSTOMERSA.EMAIL , '' ) ) end , ncenter.dbo.ORDERS.BRANCH , ncenter.dbo.CPROFA.BONUSFLAG , ncenter.dbo.ORDSTATUS.CHANGEFLAG , ncenter.dbo.ORDSTATUS.CLOSEASSEMBLY , coalesce( ncenter.dbo.ORDERSB.CONTPAY , 0 ) , ncenter.dbo.DOCPROJ.COSTC , ncenter.dbo.COSTCENTERS.INACTIVE , ncenter.dbo.COSTCENTERS.COSTCNAME , ncenter.dbo.CPROF.COPYFLAG , ncenter.dbo.CPROF.CURRENCY , ncenter.dbo.CPROF.CUST , ncenter.dbo.CPROF.DESTCODE , (0.0 + ( convert(decimal(19,2), ncenter.dbo.CPROF.QPRICE) )) , ncenter.dbo.CPROF.CPROFSTAT , ncenter.dbo.ORDERS.CURRENCY , ncenter.dbo.ORDERS.CUST , ncenter.dbo.CUSTOMERS.CUST , ncenter.dbo.CUSTOMERS.BRANCH , ncenter.dbo.CUSTOMERS.CASH , ncenter.dbo.CUSTOMERS.CURRENCY , ncenter.dbo.CUSTOMERS.LINKDATE , ncenter.dbo.CUSTOMERS.PAY , ncenter.dbo.CUSTOMERS.SHIPTYPE , ncenter.dbo.CUSTOMERS.TAX , ncenter.dbo.ORDERS.DEAL , ncenter.dbo.DEAL.CLOSED , ncenter.dbo.DEAL.CURRENCY , ncenter.dbo.DEAL.CUST , ncenter.dbo.DEALA.DESTCODE , ncenter.dbo.ORDERS.DESTCODE , ncenter.dbo.ORDERSA.DIALOGFLAG , coalesce( ncenter.dbo.ORDERSB.DISTRLINE , 0 ) , ncenter.dbo.DOCUMENTS.DOC , ncenter.dbo.ORDERSA.PAYCUST , coalesce( ncenter.dbo.ORDERSCHED.DOER2 , 0 ) , coalesce( ncenter.dbo.ORDERSCHED.DOER3 , 0 ) , ncenter.dbo.DOCUMENTS.TYPE , ncenter.dbo.ORDERSA.DIALOGFLAG , (0.0 + ( convert(decimal(27,9), ncenter.dbo.CURRENCIES.EXCHANGE) )) , CURRENCIES1.EXCHQUANT ,  'O' , ncenter.dbo.ORDERS.ORD , coalesce( ncenter.dbo.FOLLOWUPLIST.TYPE , '' ) , coalesce( ncenter.dbo.FOLLOWUPLIST.T$USER , 0 ) , @P22 , ncenter.dbo.CUSTOMERS.IMPTERM , coalesce( ncenter.dbo.ORDERSB.IVDESTCODE , 0 ) , ncenter.dbo.CUSTOMERS.IVTYPE , ncenter.dbo.ORDERS.LCURRENCY , ncenter.dbo.ORDERS.ORD ,  'O' , ncenter.dbo.CUSTOMERS.MCUST , ncenter.dbo.ORDERSA.MODEL , ncenter.dbo.DEAL.NOCOPY , ncenter.dbo.ORDERS.ORD , coalesce( ncenter.dbo.NSCUST.CUSTDES , '' ) , ncenter.dbo.CUSTOMERS.NSFLAG , coalesce( ncenter.dbo.NSCUST.TYPE , '' ) , ncenter.dbo.ORDERSA.OBLIGOFLAG , ncenter.dbo.ORDSTATUS.OPENASSEMBLY , ncenter.dbo.ORDSTATUS.OPENDOCFLAG ,  ' ' , 
ncenter.dbo.ORDERS.ORD , ncenter.dbo.ORDERS.ORD , ncenter.dbo.LOGCONST.NAME , convert( int , round( ncenter.dbo.LOGCONST.VALUE ,0)) , ncenter.dbo.ORDERS.ORD , ncenter.dbo.ORDSTATUS.CLOSED , ncenter.dbo.ORDERS.ORDSTATUS , ncenter.dbo.ORDERS.ORDTYPE , 
ncenter.dbo.ORDSTATUS.PARTIALASSEMBLY , ncenter.dbo.ORDERS.PAY , ncenter.dbo.CUSTOMERS.PAYCUST , ncenter.dbo.ORDERS.PHONE , ncenter.dbo.ORDERS.PLIST , ncenter.dbo.ORDERS.PROF , ncenter.dbo.CPROF.PROF , ncenter.dbo.CPROFA.PROJ , ncenter.dbo.ORDERS.PROJ , 
ncenter.dbo.DOCUMENTS.CUST , ncenter.dbo.DOCUMENTS.PLIST , ncenter.dbo.CUSTOMERS.SECONDLANGTEXT , ncenter.dbo.ORDERS.SHIPTYPE , coalesce( ncenter.dbo.CPROFSTATS.STATDES , '' ) , ncenter.dbo.CUSTOMERS.STATEID ,  'O' , coalesce( ncenter.dbo.STORAGEZONES.WARHS , 0 ) , 
coalesce( ncenter.dbo.ORDERSB.STZONE , 0 ) , ncenter.dbo.CPROF.SUP , ncenter.dbo.ORDERSA.TAX , ncenter.dbo.CUSTOMERS.TERRITORY , ncenter.dbo.ORDERS.UDATE , case when ( ( ncenter.dbo.CUSTOMERS.IVTYPE =  'F' ) ) then (  ' ' ) when ( ( coalesce( 
ncenter.dbo.CUSTOMERSA.VATPRICEFLAG , '' ) <>  ' ' ) ) then ( case when ( ( coalesce( ncenter.dbo.CUSTOMERSA.VATPRICEFLAG , '' ) =  '0' ) ) then (  ' ' ) else (  'Y' ) end ) when ( ( coalesce( CUSTOMERSA1.VATPRICEFLAG , '' ) <>  ' ' ) ) then ( case when ( ( coalesce( 
CUSTOMERSA1.VATPRICEFLAG , '' ) =  '0' ) ) then (  ' ' ) else (  'Y' ) end ) when ( ( @P23 = @P24 ) ) then (  'Y' ) else (  ' ' ) end , ncenter.dbo.ORDERS.T$USER , ncenter.dbo.CUSTOMERS.VATFLAG , ncenter.dbo.CUSTOMERS.VATNUM , coalesce( 
ncenter.dbo.CUSTOMERSA.VATPRICEFLAG , '' ) , coalesce( CUSTOMERSA1.VATPRICEFLAG , '' ) , ncenter.dbo.ORDERS.WARHS , coalesce( ncenter.dbo.ORDERSB.WTASKDOCTYPE , '' ) , ncenter.dbo.CUSTOMERS.ZIP , case when ( ( ncenter.dbo.CPROFTYPES.SUBSCRIBEFLAG =  'Y' ) ) then ( @P25 
) else ( @P26 ) end , case when ( ( ncenter.dbo.CPROFTYPES.SUBSCRIBEFLAG =  'Y' ) ) then ( @P27 ) else ( @P28 ) end , ncenter.dbo.DOCUMENTS.FLAG , ncenter.dbo.DOCUMENTS.TOWARHS , ncenter.dbo.ORDERSA.CPAY , ncenter.dbo.ORDERSA.CPAYDES , coalesce( ncenter.dbo.CUSTOMERSA.MAILINTERFACE , '' ) , case when ( ( ncenter.dbo.ORDERS.REFERENCE <> @P29 ) ) then (  'Y' ) else (  ' ' ) end , ncenter.dbo.ORDERSA.CHANGESTATFLAG , coalesce( ncenter.dbo.CUSTOMERSA.EMAIL , '' ) , case when ( ( ncenter.dbo.PHONEBOOK.EMAIL <> @P30 ) ) then ( ncenter.dbo.PHONEBOOK.EMAIL ) else ( coalesce( ncenter.dbo.CUSTOMERSA.EMAIL , '' ) ) end , @P31 , @P32 ,  ' ' , - ( @P33 ) , ncenter.dbo.COMPDATA.COMP , @P34 , ncenter.dbo.COMPDATA.EMAIL ,  ' ' , (0.0 + ( convert(decimal(11,2), ncenter.dbo.ORDERSA.CPERCENT) )) ,  ' ' , ncenter.dbo.CUSTTOPICS.MAILFLAG , ncenter.dbo.CUSTTOPICS.TOPIC ,  ' ' , ncenter.dbo.ORDERSA.CC , ncenter.dbo.CUSTCAMPAIGNS.CUST , ncenter.dbo.CUSTCAMPAIGNS.CCSTATUS , ncenter.dbo.ORDERSA.FROMWARHS , ncenter.dbo.DEAL.DEAL , ncenter.dbo.ORDSTATUS.EXTERNALUPDATE , ncenter.dbo.ORDSTATUS.CANCELFLAG , ncenter.dbo.ORDSTATUS.CHLFLAG , ncenter.dbo.CPROF.SHIPTYPE , ncenter.dbo.ORDERS.CASH , ncenter.dbo.CPROFTYPES.FUTURISTIC , ncenter.dbo.CPROFTYPES.CCFLAG , @P35 , system .dbo.tabula_itoa( ncenter.dbo.ORDERS.ORD , @P36 , 0, ',') , @P37 , ncenter.dbo.CPROFTYPES.CONTCHARGE , ncenter.dbo.ORDERS.ORD , coalesce( ncenter.dbo.INCPARTEXT.TYPE , '' ) , coalesce( ncenter.dbo.SIM_ORDERS.DOC , 0 ) , MODELS5.MODELNAME , ncenter.dbo.ORDERS.SIM_MODELQUANT , ncenter.dbo.ORDERS.SIM_PORD , ncenter.dbo.ORDERS.SIM_MODEL , @P38 , ncenter.dbo.ORDERS.NSC_EDGECUST , ncenter.dbo.ORDERS.SIM_DISTRLINE , ncenter.dbo.ORDERS.NSC_ORDREF , coalesce( ncenter.dbo.SIM_ORDERS.TEUM , 0 ) ,  ' ' , coalesce( ncenter.dbo.SIM_ORDERS.NSC_USER , 0 ) , ncenter.dbo.ORDERS.ORD , ncenter.dbo.ORDSTATUS.SIM_ORDDELAYED , ncenter.dbo.ORDSTATUS.SIM_SUSPEND 
from ncenter.dbo.LOGCONST  inner join ncenter.dbo.COMPDATA  on ( ncenter.dbo.COMPDATA.COMP = - ( @P39 ) ) inner join ncenter.dbo.CUSTTOPICS  on ( ncenter.dbo.CUSTTOPICS.TOPIC = - ( @P40 ) ) inner join ncenter.dbo.CUSTOMERS  on 1 = 1 inner join ncenter.dbo.CUSTOMERS CUSTOMERS9 on CUSTOMERS9.CUSTNAME like ltrim(rtrim( @P41 )) inner join ncenter.dbo.ORDERSA  on 1 = 1 inner join ncenter.dbo.ORDERS  on ( ncenter.dbo.ORDERS.ORD = ncenter.dbo.ORDERSA.ORD ) inner join ncenter.dbo.DOCUMENTS  on ( ncenter.dbo.DOCUMENTS.DOC = ncenter.dbo.ORDERS.PROJ ) inner join ncenter.dbo.DEAL  on ( ncenter.dbo.DEAL.DEAL = ncenter.dbo.ORDERS.DEAL ) inner join ncenter.dbo.DOCPROJ  on ( ncenter.dbo.DOCPROJ.DOC = ncenter.dbo.DOCUMENTS.DOC ) inner join ncenter.dbo.CPROF  on ( ncenter.dbo.CPROF.PROF = ncenter.dbo.ORDERS.PROF ) inner join ncenter.dbo.COSTCENTERS  on ( ncenter.dbo.COSTCENTERS.COSTC = ncenter.dbo.DOCPROJ.COSTC ) inner join ncenter.dbo.PRICELIST  on ( ncenter.dbo.PRICELIST.PLIST = ncenter.dbo.ORDERS.PLIST ) inner join ncenter.dbo.CPROFA  on ( ncenter.dbo.CPROFA.PROF = ncenter.dbo.CPROF.PROF ) inner join ncenter.dbo.ORDSTATUS  on ( ncenter.dbo.ORDSTATUS.ORDSTATUS = ncenter.dbo.ORDERS.ORDSTATUS ) inner join ncenter.dbo.DEALA  on ( ncenter.dbo.DEALA.DEAL = ncenter.dbo.DEAL.DEAL ) inner join ncenter.dbo.CSL_FIELDS  on ( ncenter.dbo.CSL_FIELDS.FIELD = ncenter.dbo.ORDERS.CSL_FIELD ) inner join ncenter.dbo.DISTRLINES DISTRLINES9 on ( DISTRLINES9.DISTRLINE = ncenter.dbo.ORDERS.SIM_DISTRLINE ) inner join system.dbo.USERS USERS9 on 1 = 1 inner join ncenter.dbo.CUSTOMERS CUSTOMERS1 on ( CUSTOMERS1.CUST = ncenter.dbo.CUSTOMERS.PAYCUST ) inner join ncenter.dbo.PHONEBOOK  on ( ncenter.dbo.PHONEBOOK.PHONE = ncenter.dbo.ORDERS.PHONE ) inner join ncenter.dbo.AGENTS  on ( ncenter.dbo.AGENTS.AGENT = ncenter.dbo.ORDERS.AGENT ) inner join ncenter.dbo.BRANCHES  on ( ncenter.dbo.BRANCHES.BRANCH = ncenter.dbo.ORDERS.BRANCH ) inner join ncenter.dbo.DESTCODES  on ( ncenter.dbo.DESTCODES.DESTCODE = ncenter.dbo.ORDERS.DESTCODE ) inner join ncenter.dbo.SHIPTYPES  on ( 
ncenter.dbo.SHIPTYPES.SHIPTYPE = ncenter.dbo.ORDERS.SHIPTYPE ) inner join ncenter.dbo.CPROFTYPES  on ( ncenter.dbo.CPROFTYPES.CPROFTYPE = ncenter.dbo.ORDERS.ORDTYPE ) inner join ncenter.dbo.PORDERS  on ( ncenter.dbo.PORDERS.ORD = ncenter.dbo.ORDERS.SIM_PORD ) inner 
join ncenter.dbo.TERRITORIES  on ( ncenter.dbo.TERRITORIES.TERRITORY = ncenter.dbo.CUSTOMERS.TERRITORY ) inner join ncenter.dbo.CUSTCAMPAIGNS  on ( ncenter.dbo.CUSTCAMPAIGNS.CC = ncenter.dbo.ORDERSA.CC ) inner join ncenter.dbo.WAREHOUSES  on ( 
ncenter.dbo.WAREHOUSES.WARHS = ncenter.dbo.ORDERS.WARHS ) inner join ncenter.dbo.WAREHOUSES WAREHOUSES1 on ( WAREHOUSES1.WARHS = ncenter.dbo.ORDERSA.FROMWARHS ) inner join ncenter.dbo.MODELS  on ( ncenter.dbo.MODELS.MODEL = ncenter.dbo.ORDERSA.MODEL ) inner join 
ncenter.dbo.TAXES  on ( ncenter.dbo.TAXES.TAX = ncenter.dbo.ORDERSA.TAX ) inner join system.dbo.USERS  on ( system.dbo.USERS.T$USER = ncenter.dbo.ORDERS.DOER ) inner join ncenter.dbo.PAY  on ( ncenter.dbo.PAY.PAY = ncenter.dbo.ORDERS.PAY ) inner join 
ncenter.dbo.ADJPRICES  on ( ncenter.dbo.ADJPRICES.ADJPRICEFLAG = ncenter.dbo.ORDERS.ADJPRICEFLAG ) inner join ncenter.dbo.CURRENCIES CURRENCIES1 on ( CURRENCIES1.CURRENCY = ncenter.dbo.ORDERS.LCURRENCY ) inner join ncenter.dbo.CURRENCIES  on ( 
ncenter.dbo.CURRENCIES.CURRENCY = ncenter.dbo.ORDERS.CURRENCY ) inner join ncenter.dbo.MODELS MODELS5 on ( MODELS5.MODEL = ncenter.dbo.ORDERS.SIM_MODEL ) left outer join ncenter.dbo.INCPARTEXT  on ( ncenter.dbo.INCPARTEXT.TYPE =  'O' ) and ( ncenter.dbo.INCPARTEXT.IV = 
ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.NSCUST  on ( ncenter.dbo.NSCUST.TYPE =  'O' ) and ( ncenter.dbo.NSCUST.IV = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.FOLLOWUPLIST  on ( ncenter.dbo.FOLLOWUPLIST.T$USER = @P42 ) and ( 
ncenter.dbo.FOLLOWUPLIST.TYPE = @P43 ) and ( ncenter.dbo.FOLLOWUPLIST.IV = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.ORDERSCHED  on ( ncenter.dbo.ORDERSCHED.ORD = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.ORDERSB  on ( ncenter.dbo.ORDERSB.ORD = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.SIM_ORDERS  on ( ncenter.dbo.SIM_ORDERS.ORD = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.CONTPAYOPTIONS  on ( ncenter.dbo.CONTPAYOPTIONS.CONTPAY = coalesce( ncenter.dbo.ORDERSB.CONTPAY , 0 ) ) left outer join ncenter.dbo.SIM_TEUM  on ( ncenter.dbo.SIM_TEUM.SIM_TEUM = coalesce( ncenter.dbo.SIM_ORDERS.TEUM , 0 ) ) left outer join ncenter.dbo.DOCUMENTS DOCUMENTS9 on ( DOCUMENTS9.DOC = coalesce( ncenter.dbo.SIM_ORDERS.DOC , 0 ) ) left outer join ncenter.dbo.DESTCODES DESTCODES1 on ( DESTCODES1.DESTCODE = coalesce( ncenter.dbo.ORDERSB.IVDESTCODE , 0 ) ) left outer join ncenter.dbo.DOCTYPES  on ( ncenter.dbo.DOCTYPES.TYPE = coalesce( ncenter.dbo.ORDERSB.WTASKDOCTYPE , '' ) ) left outer join ncenter.dbo.DISTRLINES  on ( ncenter.dbo.DISTRLINES.DISTRLINE = coalesce( ncenter.dbo.ORDERSB.DISTRLINE , 0 ) ) left outer join ncenter.dbo.STORAGEZONES  on ( ncenter.dbo.STORAGEZONES.STZONE = coalesce( ncenter.dbo.ORDERSB.STZONE , 0 ) ) left outer join ncenter.dbo.CPROFSTATS  on ( ncenter.dbo.CPROFSTATS.CPROFSTAT = ncenter.dbo.CPROF.CPROFSTAT ) left outer join ncenter.dbo.CUSTOMERSA  on ( ncenter.dbo.CUSTOMERSA.CUST = ncenter.dbo.CUSTOMERS.CUST ) left outer join ncenter.dbo.CUSTOMERSA CUSTOMERSA1 on ( CUSTOMERSA1.CUST = ncenter.dbo.CUSTOMERS.MCUST ) left outer join system.dbo.USERS USERS3 on ( USERS3.T$USER = coalesce( ncenter.dbo.ORDERSCHED.DOER3 , 0 ) ) left outer join system.dbo.USERS USERS2 on ( USERS2.T$USER = coalesce( ncenter.dbo.ORDERSCHED.DOER2 , 0 ) ) 
where ( coalesce( ncenter.dbo.SIM_ORDERS.NSC_USER , 0 ) = USERS9.T$USER ) and ( ncenter.dbo.ORDERS.CUST = ncenter.dbo.CUSTOMERS.CUST ) and ( ncenter.dbo.ORDERS.NSC_EDGECUST = CUSTOMERS9.CUST ) and ( ncenter.dbo.LOGCONST.NAME = @P44 ) 
order by 5 desc , 6 desc

this is the query that i see in profiler for event class RPC:completed

exec sp_executesql N'select 	                ncenter.dbo.CUSTOMERS.CUSTNAME , case when ( ( coalesce( ncenter.dbo.NSCUST.CUSTDES , '''' ) = rtrim(ltrim(reverse( @P1 ))) ) ) then ( ncenter.dbo.CUSTOMERS.CUSTDES ) else ( coalesce( ncenter.dbo.NSCUST.CUSTDES , '''' ) ) end , ncenter.dbo.PHONEBOOK.NAME , ncenter.dbo.PHONEBOOK.POSITIONDES , ncenter.dbo.ORDERS.CURDATE , ncenter.dbo.ORDERS.ORDNAME , ncenter.dbo.ORDERS.REFERENCE , ncenter.dbo.ORDERS.BOOKNUM , ncenter.dbo.DOCUMENTS.DOCNO , ncenter.dbo.DOCPROJ.PROJDES , ncenter.dbo.ORDSTATUS.ORDSTATUSDES , case when ( ( ncenter.dbo.ORDERS.CLOSED =  ''C'' ) ) then (  ''Y'' ) else (  '' '' ) end , case when ( ( ncenter.dbo.ORDERS.PCLOSED =  ''C'' ) ) then (  ''Y'' ) else (  '' '' ) end , ncenter.dbo.ORDERS.FORECASTFLAG , ncenter.dbo.CPROF.CPROFNUM , ncenter.dbo.PRICELIST.PLNAME , ncenter.dbo.DEAL.ORDNAME , ncenter.dbo.DEALA.YOURORDNAME , ncenter.dbo.ORDERS.DETAILS , ncenter.dbo.ORDERSA.STATUSDATE , ncenter.dbo.ORDERSA.RMADOCNUM , coalesce( ncenter.dbo.ORDERSB.DUEDATE , 0 ) , ncenter.dbo.AGENTS.AGENTCODE , ncenter.dbo.AGENTS.AGENTNAME , ncenter.dbo.BRANCHES.BRANCHNAME , ncenter.dbo.DESTCODES.CODE , ncenter.dbo.DESTCODES.CODEDES , ncenter.dbo.SHIPTYPES.STCODE , ncenter.dbo.SHIPTYPES.STDES , ncenter.dbo.CPROFTYPES.TYPECODE , ncenter.dbo.CPROFTYPES.TYPEDES , ncenter.dbo.MODELS.MODELNAME , ncenter.dbo.ORDERSA.QUANT , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.QPRICE) )) , (0.0 + ( convert(decimal(11,2), ncenter.dbo.ORDERS.T$PERCENT) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.VAT) )) , ncenter.dbo.TAXES.TAXCODE , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.TOTPRICE) )) , ncenter.dbo.CURRENCIES.CODE , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.QPROFIT) )) , (0.0 + ( case when ( ( convert( int , round( ncenter.dbo.LOGCONST.VALUE ,0)) = @P2 ) ) then ( case when ( ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) = 0.00 ) ) then ( 0.000000000 ) else ( ( 100.000000000 * convert(decimal(19,2), ncenter.dbo.ORDERS.QPROFIT) ) / (case when ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) ) = 0 then 1 else ( convert(decimal(19,2), ncenter.dbo.ORDERSA.TOTPURCHASEPRICE) ) end) ) end ) when ( ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) = 0.00 ) ) then ( 0.000000000 ) else ( ( 100.000000000 * convert(decimal(19,2), ncenter.dbo.ORDERS.QPROFIT) ) / (case when ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) ) = 0 then 1 else ( convert(decimal(19,2), ncenter.dbo.ORDERS.DISPRICE) ) end) ) end )) , CURRENCIES1.CODE , (0.0 + ( ( convert(decimal(27,9), ncenter.dbo.ORDERS.LEXCHANGE) * CURRENCIES1.EXCHQUANT ) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERSA.LEXCHTOL) )) , ncenter.dbo.ORDERSA.LEXCHNEG , ncenter.dbo.ORDERS.ADJPRICEFLAG , ncenter.dbo.ADJPRICES.ADJPRICEDES , ncenter.dbo.ORDERSA.LINKOPTIONS , ncenter.dbo.PAY.PAYCODE , ncenter.dbo.PAY.PAYDES , case when ( ( ncenter.dbo.ORDERSA.OBLIGOFLAG =  ''Y'' ) ) then (  '' '' ) else (  ''Y'' ) end , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.ADVBAL) )) , (0.0 + ( convert(decimal(9,2), ncenter.dbo.ORDERS.ADVPERCENT) )) , case when ( ( ncenter.dbo.CUSTOMERS.VATNUM <> @P3 ) ) then ( ncenter.dbo.CUSTOMERS.VATNUM ) else ( ncenter.dbo.ORDERSA.VATNUM ) end , system.dbo.USERS.USERLOGIN , coalesce( USERS2.USERLOGIN , '''' ) , coalesce( USERS3.USERLOGIN , '''' ) , coalesce( ncenter.dbo.ORDERSCHED.SDATE , 0 ) , case when ( ( coalesce( ncenter.dbo.ORDERSCHED.SDATE , 0 ) <> @P4 ) ) then ( substring( system .dbo.tabula_dtoa( coalesce( ncenter.dbo.ORDERSCHED.SDATE , 0 ) , ''day'' , '''' , ''א  ,ב  ,ג  ,ד  ,ה  ,ו  ,ש  '' ) , 1, 3) ) else ( @P5 ) end , coalesce( ncenter.dbo.ORDERSCHED.STIME , 0 ) , coalesce( ncenter.dbo.ORDERSCHED.EDATE , 0 ) , case when ( ( coalesce( ncenter.dbo.ORDERSCHED.EDATE , 0 ) <> @P6 ) ) then ( substring( system .dbo.tabula_dtoa( coalesce( ncenter.dbo.ORDERSCHED.EDATE , 0 ) , ''day'' , '''' , ''א  ,ב  ,ג  ,ד  ,ה  ,ו  ,ש  '' ) , 1, 3) ) else ( @P7 ) end , coalesce( ncenter.dbo.ORDERSCHED.ETIME , 0 ) , WAREHOUSES1.WARHSNAME , WAREHOUSES1.LOCNAME , WAREHOUSES1.WARHSDES , ncenter.dbo.WAREHOUSES.WARHSNAME , ncenter.dbo.WAREHOUSES.LOCNAME , ncenter.dbo.WAREHOUSES.WARHSDES , ncenter.dbo.ORDERS.OPENDCH , ncenter.dbo.ORDERSA.EXTFILEFLAG , ncenter.dbo.ORDERSA.BONUSFLAG , ncenter.dbo.CUSTCAMPAIGNS.CCNUM , ncenter.dbo.TERRITORIES.TERRITORYCODE , ncenter.dbo.TERRITORIES.TERRITORYDES , (0.0 + ( convert(decimal(20,3), ncenter.dbo.ORDERSA.TOTQUANT) )) , ncenter.dbo.PORDERS.ORDNAME , case when ( ( ncenter.dbo.ORDERS.SIM_PORD <> @P8 ) ) then ( @P9 ) else ( @P10 ) end , case when ( ( convert(decimal(19,2), ncenter.dbo.ORDERSA.PIKALONEFLAG) = 0.00 ) ) then (  '' '' ) else (  ''Y'' ) end , coalesce( ncenter.dbo.STORAGEZONES.STZONECODE , '''' ) , coalesce( ncenter.dbo.DISTRLINES.DISTRLINECODE , '''' ) , coalesce( ncenter.dbo.DISTRLINES.DISTRLINEDES , '''' ) , coalesce( ncenter.dbo.STORAGEZONES.STZONEDES , '''' ) , coalesce( ncenter.dbo.DOCTYPES.DOCCODE , '''' ) , ncenter.dbo.ORDERS.SIM_CHLINE , coalesce( ncenter.dbo.DOCTYPES.DOCDES , '''' ) , coalesce( DESTCODES1.CODE , '''' ) , ncenter.dbo.ORDERS.SIM_CUSTHOME , coalesce( DESTCODES1.CODEDES , '''' ) , coalesce( ncenter.dbo.ORDERSB.LFROMDATE , 0 ) , coalesce( ncenter.dbo.ORDERSB.LBASEDATE , 0 ) , (0.0 + ( coalesce( convert(decimal(19,2), ncenter.dbo.ORDERSB.LPERCENT) , 0.0 ) )) , coalesce( ncenter.dbo.ORDERSB.SHIPREMARK , '''' ) , coalesce( ncenter.dbo.ORDERSB.GPSX , '''' ) , coalesce( ncenter.dbo.ORDERSB.GPSY , '''' ) , coalesce( ncenter.dbo.ORDERSB.PIKORDER , 0 ) , coalesce( ncenter.dbo.INCPARTEXT.FLAG , '''' ) , coalesce( ncenter.dbo.ORDERSB.CONTCHARGE , '''' ) , coalesce( ncenter.dbo.ORDERSB.CHARGEPERIOD , 0 ) , coalesce( ncenter.dbo.CONTPAYOPTIONS.CONTPAYNAME , '''' ) , ncenter.dbo.ORDERSA.CURVERSION , ncenter.dbo.CPROFTYPES.SUBSCRIBEFLAG , coalesce( ncenter.dbo.ORDERSB.CALCVATFLAG , '''' ) , CUSTOMERS9.CUSTNAME , ncenter.dbo.ORDERS.NSC_SORTORDER , CUSTOMERS9.CUSTDES , ncenter.dbo.ORDERS.NSC_NAUTOSORT , ncenter.dbo.ORDERS.NSC_OPENDATE , case when ( CUSTOMERS9.PHONE like ltrim(rtrim( @P11 )) ) then ( CUSTOMERS9.PHONE ) else ( CUSTOMERS9.NSC_PHONETWO ) end , ncenter.dbo.ORDERS.NSC_INSTITUTION , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.NSC_ALREADYPAID) )) , (0.0 + ( convert(decimal(19,2), ncenter.dbo.ORDERS.NSC_LEFTTOPAID) )) , ncenter.dbo.ORDERS.NSC_SMSDATESENT , coalesce( ncenter.dbo.SIM_ORDERS.NSC_MANUALAPPRBILL , '''' ) , ncenter.dbo.ORDERS.NSC_LINKTOWEB , (0.0 + ( coalesce( convert(decimal(19,2), ncenter.dbo.SIM_ORDERS.NSC_PREVTOTPRICE) , 0.0 ) )) , (0.0 + ( coalesce( convert(decimal(19,2), ncenter.dbo.SIM_ORDERS.NSC_TOTPRICE) , 0.0 ) )) , coalesce( ncenter.dbo.SIM_ORDERS.NSC_UDATE , 0 ) , USERS9.USERLOGIN , ncenter.dbo.ORDERS.NSC_WEBINTERFACE , ncenter.dbo.ORDERS.SIM_UNIFAYDELIVERY , ncenter.dbo.ORDERS.SIM_URGENT , 
DISTRLINES9.DISTRLINECODE , DISTRLINES9.DISTRLINEDES , ncenter.dbo.ORDERS.SIM_DISTLOCATION , ncenter.dbo.ORDERS.SIM_APPIRRUNIT , ncenter.dbo.ORDERSA.SIM_DUEDATE , ncenter.dbo.ORDSTATUS.NSC_DOCFLAG , coalesce( ncenter.dbo.SIM_ORDERS.NSC_WRKDAYSTODELIVER , 0 ) , 
coalesce( ncenter.dbo.SIM_ORDERS.NSC_ORDTIME , 0 ) , ncenter.dbo.CSL_FIELDS.FIELDCODE , ncenter.dbo.CSL_FIELDS.FIELDES , coalesce( ncenter.dbo.SIM_TEUM.CODE , '''' ) , coalesce( ncenter.dbo.SIM_TEUM.DES , '''' ) , coalesce( DOCUMENTS9.DOCNO , '''' ) , 
ncenter.dbo.ORDERS.NSC_STATDATE , ncenter.dbo.ORDERS.SIM_DELAYREASON , ncenter.dbo.ORDERS.SIM_NOENTRYCHECK , ncenter.dbo.ORDERS.CSL_FIELD , ncenter.dbo.CUSTOMERS.CUSTDES , ncenter.dbo.CUSTOMERS.AGENT , ncenter.dbo.ORDERS.TYPE , ncenter.dbo.ORDERS.CLOSED , 
ncenter.dbo.ORDERS.PCLOSED , (0.0 + ( convert(decimal(27,9), ncenter.dbo.ORDERS.LEXCHANGE) )) , ncenter.dbo.ORDERS.DOER , case when ( ( CUSTOMERS1.ACCOUNT <> @P12 ) ) then ( CUSTOMERS1.ACCOUNT ) else ( ncenter.dbo.CUSTOMERS.ACCOUNT ) end , case when ( ( coalesce( 
ncenter.dbo.NSCUST.ADDRESS , '''' ) <> rtrim(ltrim(reverse( @P13 ))) ) ) then ( coalesce( ncenter.dbo.NSCUST.STATE , '''' ) + reverse( reverse( coalesce( ncenter.dbo.NSCUST.ADDRESS , '''' ) ) + @P14 ) ) when ( ( ncenter.dbo.DESTCODES.ADDRESS <> rtrim(ltrim(reverse( 
@P15 ))) ) ) then ( ncenter.dbo.DESTCODES.STATE + reverse( reverse( ncenter.dbo.DESTCODES.ADDRESS ) + @P16 ) ) else ( ncenter.dbo.CUSTOMERS.STATE + reverse( reverse( ncenter.dbo.CUSTOMERS.ADDRESS ) + @P17 ) ) end , ncenter.dbo.PHONEBOOK.NAME , case when ( ( coalesce( 
ncenter.dbo.NSCUST.FAX , '''' ) <> @P18 ) ) then ( coalesce( ncenter.dbo.NSCUST.FAX , '''' ) ) when ( ( ncenter.dbo.PHONEBOOK.FAX <> @P19 ) ) then ( ncenter.dbo.PHONEBOOK.FAX ) else ( ncenter.dbo.CUSTOMERS.FAX ) end , ncenter.dbo.ORDERS.AGENT , case when ( ( coalesce( 
ncenter.dbo.NSCUST.EMAIL , '''' ) <> @P20 ) ) then ( coalesce( ncenter.dbo.NSCUST.EMAIL , '''' ) ) when ( ( ncenter.dbo.PHONEBOOK.EMAIL <> @P21 ) ) then ( ncenter.dbo.PHONEBOOK.EMAIL ) else ( coalesce( ncenter.dbo.CUSTOMERSA.EMAIL , '''' ) ) end , ncenter.dbo.ORDERS.BRANCH , ncenter.dbo.CPROFA.BONUSFLAG , ncenter.dbo.ORDSTATUS.CHANGEFLAG , ncenter.dbo.ORDSTATUS.CLOSEASSEMBLY , coalesce( ncenter.dbo.ORDERSB.CONTPAY , 0 ) , ncenter.dbo.DOCPROJ.COSTC , ncenter.dbo.COSTCENTERS.INACTIVE , ncenter.dbo.COSTCENTERS.COSTCNAME , ncenter.dbo.CPROF.COPYFLAG , ncenter.dbo.CPROF.CURRENCY , ncenter.dbo.CPROF.CUST , ncenter.dbo.CPROF.DESTCODE , (0.0 + ( convert(decimal(19,2), ncenter.dbo.CPROF.QPRICE) )) , ncenter.dbo.CPROF.CPROFSTAT , ncenter.dbo.ORDERS.CURRENCY , ncenter.dbo.ORDERS.CUST , ncenter.dbo.CUSTOMERS.CUST , ncenter.dbo.CUSTOMERS.BRANCH , ncenter.dbo.CUSTOMERS.CASH , ncenter.dbo.CUSTOMERS.CURRENCY , ncenter.dbo.CUSTOMERS.LINKDATE , ncenter.dbo.CUSTOMERS.PAY , ncenter.dbo.CUSTOMERS.SHIPTYPE , ncenter.dbo.CUSTOMERS.TAX , ncenter.dbo.ORDERS.DEAL , ncenter.dbo.DEAL.CLOSED , ncenter.dbo.DEAL.CURRENCY , ncenter.dbo.DEAL.CUST , ncenter.dbo.DEALA.DESTCODE , ncenter.dbo.ORDERS.DESTCODE , ncenter.dbo.ORDERSA.DIALOGFLAG , coalesce( ncenter.dbo.ORDERSB.DISTRLINE , 0 ) , ncenter.dbo.DOCUMENTS.DOC , ncenter.dbo.ORDERSA.PAYCUST , coalesce( ncenter.dbo.ORDERSCHED.DOER2 , 0 ) , coalesce( ncenter.dbo.ORDERSCHED.DOER3 , 0 ) , ncenter.dbo.DOCUMENTS.TYPE , ncenter.dbo.ORDERSA.DIALOGFLAG , (0.0 + ( convert(decimal(27,9), ncenter.dbo.CURRENCIES.EXCHANGE) )) , CURRENCIES1.EXCHQUANT ,  ''O'' , ncenter.dbo.ORDERS.ORD , coalesce( ncenter.dbo.FOLLOWUPLIST.TYPE , '''' ) , coalesce( ncenter.dbo.FOLLOWUPLIST.T$USER , 0 ) , @P22 , ncenter.dbo.CUSTOMERS.IMPTERM , coalesce( ncenter.dbo.ORDERSB.IVDESTCODE , 0 ) , ncenter.dbo.CUSTOMERS.IVTYPE , ncenter.dbo.ORDERS.LCURRENCY , ncenter.dbo.ORDERS.ORD ,  ''O'' , ncenter.dbo.CUSTOMERS.MCUST , ncenter.dbo.ORDERSA.MODEL , ncenter.dbo.DEAL.NOCOPY , ncenter.dbo.ORDERS.ORD , coalesce( ncenter.dbo.NSCUST.CUSTDES , '''' ) , ncenter.dbo.CUSTOMERS.NSFLAG , coalesce( ncenter.dbo.NSCUST.TYPE , '''' ) , ncenter.dbo.ORDERSA.OBLIGOFLAG , ncenter.dbo.ORDSTATUS.OPENASSEMBLY , ncenter.dbo.ORDSTATUS.OPENDOCFLAG ,  '' '' , ncenter.dbo.ORDERS.ORD , 
ncenter.dbo.ORDERS.ORD , ncenter.dbo.LOGCONST.NAME , convert( int , round( ncenter.dbo.LOGCONST.VALUE ,0)) , ncenter.dbo.ORDERS.ORD , ncenter.dbo.ORDSTATUS.CLOSED , ncenter.dbo.ORDERS.ORDSTATUS , ncenter.dbo.ORDERS.ORDTYPE , ncenter.dbo.ORDSTATUS.PARTIALASSEMBLY , 
ncenter.dbo.ORDERS.PAY , ncenter.dbo.CUSTOMERS.PAYCUST , ncenter.dbo.ORDERS.PHONE , ncenter.dbo.ORDERS.PLIST , ncenter.dbo.ORDERS.PROF , ncenter.dbo.CPROF.PROF , ncenter.dbo.CPROFA.PROJ , ncenter.dbo.ORDERS.PROJ , ncenter.dbo.DOCUMENTS.CUST , 
ncenter.dbo.DOCUMENTS.PLIST , ncenter.dbo.CUSTOMERS.SECONDLANGTEXT , ncenter.dbo.ORDERS.SHIPTYPE , coalesce( ncenter.dbo.CPROFSTATS.STATDES , '''' ) , ncenter.dbo.CUSTOMERS.STATEID ,  ''O'' , coalesce( ncenter.dbo.STORAGEZONES.WARHS , 0 ) , coalesce( 
ncenter.dbo.ORDERSB.STZONE , 0 ) , ncenter.dbo.CPROF.SUP , ncenter.dbo.ORDERSA.TAX , ncenter.dbo.CUSTOMERS.TERRITORY , ncenter.dbo.ORDERS.UDATE , case when ( ( ncenter.dbo.CUSTOMERS.IVTYPE =  ''F'' ) ) then (  '' '' ) when ( ( coalesce( 
ncenter.dbo.CUSTOMERSA.VATPRICEFLAG , '''' ) <>  '' '' ) ) then ( case when ( ( coalesce( ncenter.dbo.CUSTOMERSA.VATPRICEFLAG , '''' ) =  ''0'' ) ) then (  '' '' ) else (  ''Y'' ) end ) when ( ( coalesce( CUSTOMERSA1.VATPRICEFLAG , '''' ) <>  '' '' ) ) then ( case when 
( ( coalesce( CUSTOMERSA1.VATPRICEFLAG , '''' ) =  ''0'' ) ) then (  '' '' ) else (  ''Y'' ) end ) when ( ( @P23 = @P24 ) ) then (  ''Y'' ) else (  '' '' ) end , ncenter.dbo.ORDERS.T$USER , ncenter.dbo.CUSTOMERS.VATFLAG , ncenter.dbo.CUSTOMERS.VATNUM , coalesce( 
ncenter.dbo.CUSTOMERSA.VATPRICEFLAG , '''' ) , coalesce( CUSTOMERSA1.VATPRICEFLAG , '''' ) , ncenter.dbo.ORDERS.WARHS , coalesce( ncenter.dbo.ORDERSB.WTASKDOCTYPE , '''' ) , ncenter.dbo.CUSTOMERS.ZIP , case when ( ( ncenter.dbo.CPROFTYPES.SUBSCRIBEFLAG =  ''Y'' ) ) 
then ( @P25 ) else ( @P26 ) end , case when ( ( ncenter.dbo.CPROFTYPES.SUBSCRIBEFLAG =  ''Y'' ) ) then ( @P27 ) else ( @P28 ) end , ncenter.dbo.DOCUMENTS.FLAG , ncenter.dbo.DOCUMENTS.TOWARHS , ncenter.dbo.ORDERSA.CPAY , ncenter.dbo.ORDERSA.CPAYDES , coalesce( ncenter.dbo.CUSTOMERSA.MAILINTERFACE , '''' ) , case when ( ( ncenter.dbo.ORDERS.REFERENCE <> @P29 ) ) then (  ''Y'' ) else (  '' '' ) end , ncenter.dbo.ORDERSA.CHANGESTATFLAG , coalesce( ncenter.dbo.CUSTOMERSA.EMAIL , '''' ) , case when ( ( ncenter.dbo.PHONEBOOK.EMAIL <> @P30 ) ) then ( ncenter.dbo.PHONEBOOK.EMAIL ) else ( coalesce( ncenter.dbo.CUSTOMERSA.EMAIL , '''' ) ) end , @P31 , @P32 ,  '' '' , - ( @P33 ) , ncenter.dbo.COMPDATA.COMP , @P34 , ncenter.dbo.COMPDATA.EMAIL ,  '' '' , (0.0 + ( convert(decimal(11,2), ncenter.dbo.ORDERSA.CPERCENT) )) ,  '' '' , ncenter.dbo.CUSTTOPICS.MAILFLAG , ncenter.dbo.CUSTTOPICS.TOPIC ,  '' '' , ncenter.dbo.ORDERSA.CC , ncenter.dbo.CUSTCAMPAIGNS.CUST , ncenter.dbo.CUSTCAMPAIGNS.CCSTATUS , ncenter.dbo.ORDERSA.FROMWARHS , ncenter.dbo.DEAL.DEAL , ncenter.dbo.ORDSTATUS.EXTERNALUPDATE , ncenter.dbo.ORDSTATUS.CANCELFLAG , ncenter.dbo.ORDSTATUS.CHLFLAG , ncenter.dbo.CPROF.SHIPTYPE , ncenter.dbo.ORDERS.CASH , ncenter.dbo.CPROFTYPES.FUTURISTIC , ncenter.dbo.CPROFTYPES.CCFLAG , @P35 , system .dbo.tabula_itoa( ncenter.dbo.ORDERS.ORD , @P36 , 0, '','') , @P37 , ncenter.dbo.CPROFTYPES.CONTCHARGE , ncenter.dbo.ORDERS.ORD , coalesce( ncenter.dbo.INCPARTEXT.TYPE , '''' ) , coalesce( ncenter.dbo.SIM_ORDERS.DOC , 0 ) , MODELS5.MODELNAME , ncenter.dbo.ORDERS.SIM_MODELQUANT , ncenter.dbo.ORDERS.SIM_PORD , ncenter.dbo.ORDERS.SIM_MODEL , @P38 , ncenter.dbo.ORDERS.NSC_EDGECUST , ncenter.dbo.ORDERS.SIM_DISTRLINE , ncenter.dbo.ORDERS.NSC_ORDREF , coalesce( ncenter.dbo.SIM_ORDERS.TEUM , 0 ) ,  '' '' , coalesce( ncenter.dbo.SIM_ORDERS.NSC_USER , 0 ) , ncenter.dbo.ORDERS.ORD , ncenter.dbo.ORDSTATUS.SIM_ORDDELAYED , ncenter.dbo.ORDSTATUS.SIM_SUSPEND 
from ncenter.dbo.LOGCONST  inner join ncenter.dbo.COMPDATA  on ( ncenter.dbo.COMPDATA.COMP = - ( @P39 ) ) inner join ncenter.dbo.CUSTTOPICS  on ( ncenter.dbo.CUSTTOPICS.TOPIC = - ( @P40 ) ) inner join ncenter.dbo.CUSTOMERS  on 1 = 1 inner join ncenter.dbo.CUSTOMERS CUSTOMERS9 on CUSTOMERS9.CUSTNAME like ltrim(rtrim( @P41 )) inner join ncenter.dbo.ORDERSA  on 1 = 1 inner join ncenter.dbo.ORDERS  on ( ncenter.dbo.ORDERS.ORD = ncenter.dbo.ORDERSA.ORD ) inner join ncenter.dbo.DOCUMENTS  on ( ncenter.dbo.DOCUMENTS.DOC = ncenter.dbo.ORDERS.PROJ ) inner join ncenter.dbo.DEAL  on ( ncenter.dbo.DEAL.DEAL = ncenter.dbo.ORDERS.DEAL ) inner join ncenter.dbo.DOCPROJ  on ( ncenter.dbo.DOCPROJ.DOC = ncenter.dbo.DOCUMENTS.DOC ) inner join ncenter.dbo.CPROF  on ( ncenter.dbo.CPROF.PROF = ncenter.dbo.ORDERS.PROF ) inner join ncenter.dbo.COSTCENTERS  on ( ncenter.dbo.COSTCENTERS.COSTC = ncenter.dbo.DOCPROJ.COSTC ) inner join ncenter.dbo.PRICELIST  on ( ncenter.dbo.PRICELIST.PLIST = ncenter.dbo.ORDERS.PLIST ) inner join ncenter.dbo.CPROFA  on ( ncenter.dbo.CPROFA.PROF = ncenter.dbo.CPROF.PROF ) inner join ncenter.dbo.ORDSTATUS  on ( ncenter.dbo.ORDSTATUS.ORDSTATUS = ncenter.dbo.ORDERS.ORDSTATUS ) inner join ncenter.dbo.DEALA  on ( ncenter.dbo.DEALA.DEAL = ncenter.dbo.DEAL.DEAL ) inner join ncenter.dbo.CSL_FIELDS  on ( ncenter.dbo.CSL_FIELDS.FIELD = ncenter.dbo.ORDERS.CSL_FIELD ) inner join ncenter.dbo.DISTRLINES DISTRLINES9 on ( DISTRLINES9.DISTRLINE = ncenter.dbo.ORDERS.SIM_DISTRLINE ) inner join system.dbo.USERS USERS9 on 1 = 1 inner join ncenter.dbo.CUSTOMERS CUSTOMERS1 on ( CUSTOMERS1.CUST = ncenter.dbo.CUSTOMERS.PAYCUST ) inner join ncenter.dbo.PHONEBOOK  on ( ncenter.dbo.PHONEBOOK.PHONE = ncenter.dbo.ORDERS.PHONE ) inner join ncenter.dbo.AGENTS  on ( ncenter.dbo.AGENTS.AGENT = ncenter.dbo.ORDERS.AGENT ) inner join ncenter.dbo.BRANCHES  on ( ncenter.dbo.BRANCHES.BRANCH = ncenter.dbo.ORDERS.BRANCH ) inner join ncenter.dbo.DESTCODES  on ( ncenter.dbo.DESTCODES.DESTCODE = ncenter.dbo.ORDERS.DESTCODE ) inner join ncenter.dbo.SHIPTYPES  on ( 
ncenter.dbo.SHIPTYPES.SHIPTYPE = ncenter.dbo.ORDERS.SHIPTYPE ) inner join ncenter.dbo.CPROFTYPES  on ( ncenter.dbo.CPROFTYPES.CPROFTYPE = ncenter.dbo.ORDERS.ORDTYPE ) inner join ncenter.dbo.PORDERS  on ( ncenter.dbo.PORDERS.ORD = ncenter.dbo.ORDERS.SIM_PORD ) inner 
join ncenter.dbo.TERRITORIES  on ( ncenter.dbo.TERRITORIES.TERRITORY = ncenter.dbo.CUSTOMERS.TERRITORY ) inner join ncenter.dbo.CUSTCAMPAIGNS  on ( ncenter.dbo.CUSTCAMPAIGNS.CC = ncenter.dbo.ORDERSA.CC ) inner join ncenter.dbo.WAREHOUSES  on ( 
ncenter.dbo.WAREHOUSES.WARHS = ncenter.dbo.ORDERS.WARHS ) inner join ncenter.dbo.WAREHOUSES WAREHOUSES1 on ( WAREHOUSES1.WARHS = ncenter.dbo.ORDERSA.FROMWARHS ) inner join ncenter.dbo.MODELS  on ( ncenter.dbo.MODELS.MODEL = ncenter.dbo.ORDERSA.MODEL ) inner join 
ncenter.dbo.TAXES  on ( ncenter.dbo.TAXES.TAX = ncenter.dbo.ORDERSA.TAX ) inner join system.dbo.USERS  on ( system.dbo.USERS.T$USER = ncenter.dbo.ORDERS.DOER ) inner join ncenter.dbo.PAY  on ( ncenter.dbo.PAY.PAY = ncenter.dbo.ORDERS.PAY ) inner join 
ncenter.dbo.ADJPRICES  on ( ncenter.dbo.ADJPRICES.ADJPRICEFLAG = ncenter.dbo.ORDERS.ADJPRICEFLAG ) inner join ncenter.dbo.CURRENCIES CURRENCIES1 on ( CURRENCIES1.CURRENCY = ncenter.dbo.ORDERS.LCURRENCY ) inner join ncenter.dbo.CURRENCIES  on ( 
ncenter.dbo.CURRENCIES.CURRENCY = ncenter.dbo.ORDERS.CURRENCY ) inner join ncenter.dbo.MODELS MODELS5 on ( MODELS5.MODEL = ncenter.dbo.ORDERS.SIM_MODEL ) left outer join ncenter.dbo.INCPARTEXT  on ( ncenter.dbo.INCPARTEXT.TYPE =  ''O'' ) and ( ncenter.dbo.INCPARTEXT.IV 
= ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.NSCUST  on ( ncenter.dbo.NSCUST.TYPE =  ''O'' ) and ( ncenter.dbo.NSCUST.IV = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.FOLLOWUPLIST  on ( ncenter.dbo.FOLLOWUPLIST.T$USER = @P42 ) and ( 
ncenter.dbo.FOLLOWUPLIST.TYPE = @P43 ) and ( ncenter.dbo.FOLLOWUPLIST.IV = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.ORDERSCHED  on ( ncenter.dbo.ORDERSCHED.ORD = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.ORDERSB  on ( ncenter.dbo.ORDERSB.ORD = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.SIM_ORDERS  on ( ncenter.dbo.SIM_ORDERS.ORD = ncenter.dbo.ORDERS.ORD ) left outer join ncenter.dbo.CONTPAYOPTIONS  on ( ncenter.dbo.CONTPAYOPTIONS.CONTPAY = coalesce( ncenter.dbo.ORDERSB.CONTPAY , 0 ) ) left outer join ncenter.dbo.SIM_TEUM  on ( ncenter.dbo.SIM_TEUM.SIM_TEUM = coalesce( ncenter.dbo.SIM_ORDERS.TEUM , 0 ) ) left outer join ncenter.dbo.DOCUMENTS DOCUMENTS9 on ( DOCUMENTS9.DOC = coalesce( ncenter.dbo.SIM_ORDERS.DOC , 0 ) ) left outer join ncenter.dbo.DESTCODES DESTCODES1 on ( DESTCODES1.DESTCODE = coalesce( ncenter.dbo.ORDERSB.IVDESTCODE , 0 ) ) left outer join ncenter.dbo.DOCTYPES  on ( ncenter.dbo.DOCTYPES.TYPE = coalesce( ncenter.dbo.ORDERSB.WTASKDOCTYPE , '''' ) ) left outer join ncenter.dbo.DISTRLINES  on ( ncenter.dbo.DISTRLINES.DISTRLINE = coalesce( ncenter.dbo.ORDERSB.DISTRLINE , 0 ) ) left outer join ncenter.dbo.STORAGEZONES  on ( ncenter.dbo.STORAGEZONES.STZONE = coalesce( ncenter.dbo.ORDERSB.STZONE , 0 ) ) left outer join ncenter.dbo.CPROFSTATS  on ( ncenter.dbo.CPROFSTATS.CPROFSTAT = ncenter.dbo.CPROF.CPROFSTAT ) left outer join ncenter.dbo.CUSTOMERSA  on ( ncenter.dbo.CUSTOMERSA.CUST = ncenter.dbo.CUSTOMERS.CUST ) left outer join ncenter.dbo.CUSTOMERSA CUSTOMERSA1 on ( CUSTOMERSA1.CUST = ncenter.dbo.CUSTOMERS.MCUST ) left outer join system.dbo.USERS USERS3 on ( USERS3.T$USER = coalesce( ncenter.dbo.ORDERSCHED.DOER3 , 0 ) ) left outer join system.dbo.USERS USERS2 on ( USERS2.T$USER = coalesce( ncenter.dbo.ORDERSCHED.DOER2 , 0 ) ) 
where ( coalesce( ncenter.dbo.SIM_ORDERS.NSC_USER , 0 ) = USERS9.T$USER ) and ( ncenter.dbo.ORDERS.CUST = ncenter.dbo.CUSTOMERS.CUST ) and ( ncenter.dbo.ORDERS.NSC_EDGECUST = CUSTOMERS9.CUST ) and ( ncenter.dbo.LOGCONST.NAME = @P44 ) 
order by 5 desc , 6 desc 
 ',N'@P1 varchar(1),@P2 bigint,@P3 varchar(1),@P4 bigint,@P5 varchar(1),@P6 bigint,@P7 varchar(1),@P8 bigint,@P9 varchar(1),@P10 varchar(1),@P11 varchar(3),@P12 bigint,@P13 varchar(1),@P14 varchar(1),@P15 varchar(1),@P16 varchar(1),@P17 varchar(1),@P18 varchar(1),@P19 varchar(1),@P20 varchar(1),@P21 varchar(1),@P22 bigint,@P23 bigint,@P24 bigint,@P25 bigint,@P26 bigint,@P27 bigint,@P28 bigint,@P29 varchar(1),@P30 varchar(1),@P31 varchar(1),@P32 varchar(1),@P33 bigint,@P34 varchar(1),@P35 bigint,@P36 bigint,@P37 varchar(1),@P38 varchar(1),@P39 bigint,@P40 bigint,@P41 varchar(8),@P42 bigint,@P43 varchar(1),@P44 varchar(9)','',0,'',0,'',0,'',0,'Y','','05%',0,'',' ','',' ',' ','','','','',0,0,1,46979,7996,46975,9677,'','','','',1,'',22,0,'','',1,7,'7676767%',1,'O','ORDPROFIT'

---

Avi.G

Answer 7

Plan quides certainly are not any fun. The text need to match exactly, so take what you see in the RPC:Completed event. That is, copy all from the opening quote to the closing quote in the call to sp_executesql and pass that to the @stmt parameter in sp_create_plan_guide. And also do the same for the parameter list.

I'm still not fully sure that it will work out, since the statement is way more than 8000 bytes.

Not sure that it helps, but I have written about plan guides here:
http://www.sommarskog.se/query-plan-mysteries.html#planguides

If the plan guide does not work out, I'm afraid there is no solution for  you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Hi Avi.G,

Has your problem been solved? If it is solved, please mark the point that you 
think is correct as an answer. This can help others who encounter similar problems.

Best Regards 
Echo   

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 9

Plan quides certainly are not any fun. The text need to match exactly, so take what you see in the RPC:Completed event. That is, copy all from the opening quote to the closing quote in the call to sp_executesql and pass that to the @stmt parameter in sp_create_plan_guide. And also do the same for the parameter list.

I'm still not fully sure that it will work out, since the statement is way more than 8000 bytes.

Not sure that it helps, but I have written about plan guides here:
http://www.sommarskog.se/query-plan-mysteries.html#planguides

If the plan guide does not work out, I'm afraid there is no solution for  you.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

this is what i did exactly but with no luck..

can i do something with the query that is in the cache to optimize it after the first compile? maybe use the sp_create_plan_guide_from_handle and take the statement_start_offset and statement_end_offset and do something that will add the OPTION  (RECOMPILE) to it.. or other creativity option?

THX

---

Avi.G

Answer 10

can i do something with the query that is in the cache to optimize it after the first compile? maybe use the sp_create_plan_guide_from_handle and take the statement_start_offset and statement_end_offset and do something that will add the OPTION  (RECOMPILE) to it.. or other creativity option?

That SP is for plan freezing. That is, "take the current plan and use it for everything", which is obviously not what you want.

However, you could try it, just to see if you get any error message. As I said, I'm a little anxious that the length of the statement may prevent the statement from being cached and creating a plan guide.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Hi Avi.G,

Do you have any updates？

Has your problem been solved? If it is solved, please mark the point that you 
think is correct as an answer. This can help others who encounter similar problems.

Best Regards 
Echo 

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.