q

upgraded one of our servers to sql 2005 SP1 a week ago and we have been troubleshooting performance problems ever since.

so far we have narrowed it down to 2 queries.

this one spikes our 8 900MHz CPU Compaq server to 100% for 10 minutes at a time.

runs in seconds on sql 2000 on much older and slower hardware. sql 2k execution plan is all index seeks on the right indexes and sql 2005 is an index scan on the PK.

MS is saying it's by design, but i've never seen an index scan do this.

they said that they changed the optimizer to do this to avoid stack overflows at high usage.

select ordUidID,ordDtmUpdated,ordDtmCreated,ordUidParentOrder,ordCdeOrderType,ordUidCustomer,ordYnNeedsReview,ordYnCanArchive,ordCdeLEC,ordVchPON,

ordVchVersion,ordCdeTranClass,ordVchTranType,ordVchLastTran,ordTnBTN,ordTnWTN,ordSetSynchronization,ordCdeFinalResult,ordCdeStatus,ordVchFileName,

ordDtmOrdered,ordDtmClosed,ordDteDueDateRequested,ordDteDueDateAssigned,ordDteInstalled,ordIntConnectionUsed,ordVchLastMessage,ordDtmMiscDate0,

ordDtmMiscDate1,ordDtmMiscDate2,ordDtmMiscDate3,ordCdeMiscCode0,ordCdeMiscCode1,ordCdeMiscCode2,ordCdeMiscCode3,ordVchMiscData0,ordVchMiscData1,

ordVchMiscData2,ordVchMiscData3,ordVchMiscData4,ordVchMiscData5,ordVchMiscData6,ordVchMiscData7,ordVchUserID,ordCdeUserType,ordUIDIncidentID,

ordVchIncidentType,ordIntRecordUpdateCount,ordIntRecordProcessedCount,ordVchSubTrans,ordUidResellerId from tblLECOrder where ( ordTnBTN in ('000WLK0000','2122283937','2122344229','2122810515','2122818210','2122837617','2122858679','2122893175','2123162861','2123693789','2124107190','2124263225',

'2124278119','2125295465','2125343989','2125447195','2125671073','2125677808','2125678098','2125680722','2125683939','2125684463','2125687396','2125687508','

2125689745','2125689820','2126638480','2126784974','2126904871','2127228356','2127243395','2127813907','2127816515','2127817636','2127955370','2127955862',

'2127957834','2129239577','2129270685','2129273060','2129274728','2129275933','2129276137','2129276468','2129276845','2129277476','2129278126','2129279249',

'2129424067','2129873669','2129879069','2152210772','2152280240','2152321190','2152711041','2152882261','2152910690','2152919646','2153240243','2153333119',

'2154235057','2154235142','2154239175','2154239616','2154250293','2154266674','2154267635','2154268312','2154269081','2154271883','2154571323','2154572414','

2154688652','2156340253','2156342734','2157393712','2157441452','3155364195','5162920290','5163331988','5163778609','5164884993','5165055606','5165360859',

'5165381376','5165384630','5165466810','5165617488','5166239609','5168295375','5184894605','5184922523','5184991914','6102688232','6102728614','6102775286',

'6103721456','6103723790','6103724604','6103729470','6103732079','6103732752','6103735651','6103742625','6103754045','6103765831','6103766395','6104324238',

'6104325988','6104327258','6104335863','6104346703','6104372198','6104391609','6104445140','6104448194','6107749810','6108207582','6108612952','6108662412',

'6108697936','6108699214','6109250219','6109250316','6313852195','6314211097','6314231374','6314778227','6314778291','6315491328','6315493804','6315494720',

'6315498235','6316736552','6316738351','6317544591','6318594984','7168530368','7168562635','7182200643','7182201079','7182203949','7182206032','7182206124',

'7182209706','7182252502','7182353598','7182381315','7182383716','7182520828','7182560894','7182630419','7182711657','7182743419','7182770168','7182772714',

'7182774379','7182774726','7182775216','7182930531','7182931421','7182932314','7182934615','7182935547','7182936379','7182936489','7182944188','7182950607',

'7182953419','7182954264','7182955065','7182955913','7182957192','7182957980','7182967315','7183222109','7183287582','7183293615','7183299730','7183330763',

'7183493722','7183533459','7183645849','7183646794','7183647088','7183647571','7183657547','7183664851','7183672666','7183679345','7183780195','7183783144',

'7183784405','7183785086','7183786437','7183795271','7183820524','7183833545','7183860947','7183863787','7183971152','7184100414','7184102691','7184181232',

'7184184534','7184186657','7184188365','7184188485','7184189682','7184244219','7184260613','7184263054','7184297079','7184352078','7184415451','7184431673',

'7184431978','7184438072','7184452145','7184461815','7184531441','7184533981','7184534089','7184538743','7184553469','7184563259','7184563916','7184763834',

'7184865457','7184866489','7184920615','7184922149','7184971269','7184971876','7184975709','7184977375','7185058171','7185196413','7185196550','7185233417',

'7185238042','7185258323','7185263859','7185265019','7185266713','7185271890','7185280091','7185291314','7185339073','7185372582','7185378203','7185381866',

'7185382967','7185383271','7185383694','7185383794','7185385027','7185387693','7185388697','7185389418','7185392568','7185421930','7185423664','7185426571',

'7185427232','7185428637','7185428742','7185428953','7185430852','7185450174','7185470274','7185475970','7185476028','7185478451','7185491560','7185610521',

'7185611519','7185613654','7185615696','7185615863','7185617340','7185618094','7185618219','7185618486','7185621326','7185621640','7185629364','7185629692',

'7185632531','7185633531','7185733564','7185746632','7185844011','7185844746','7185844836','7185881074','7185886683','7185889899','7185890125','7185894623',

'7185896208','7185900541','7185900828','7185904091','7185904858','7185994193','7186090497','7186171937','7186172711','7186205203','7186332470','7186347884',

'7186392938','7186451907','7186472645','7186472713','7186477636','7186521715','7186522908','7186552248','7186558625','7186682396','7186683433','7186686857',

'7186689161','7186689547','7186711542','7186720486','7186721229','7186721434','7186721469','7186721952','7186722551','7186722592','7186722870','7186723055',

'7186723233','7186724169','7186724835','7186724975','7186725062','7186725799','7186725920','7186726040','7186726621','7186726740','7186726948','7186728309',

'7186728420','7186729408','7186729697','7186756401','7186756779','7186756942','7186771445','7186773330','7186777645','7186778437','7186779455','7186800316',

'7186802749','7186803348','7186803405','7186804038','7186805162','7186805387','7186805459','7186805590','7186806846','7186807136','7186808476','7186808816',

'7186810934','7186811454','7186811648','7186812053','7186812065','7186813478','7186814281','7186815360','7186815361','7186815575','7186816570','7186818268',

'7186818325','7186818403','7186819353','7186861065','7186861543','7186862969','7186866117','7186866617','7186866937','7186866947','7186867179','7186867334',

'7186920497','7186922776','7186927257','7186932954','7186937158','7186949409','7186980457','7186980920','7186980940','7186981811','7186984233','7186986257',

'7186986612','7186987684','7186990841','7186990905','7186991184','7186991329','7186992379','7186992734','7186992876','7186994389','7186995289','7186995341',

'7186997162','7186997361','7186999147','7187034172','7187067578','7187068410','7187068465','7187068471','7187141153','7187144035','7187144291','7187146328',

'7187146758','7187146810','7187149845','7187161933','7187161941','7187162663','7187165245','7187165380','7187206825','7187206887','7187207200','7187207345',

'7187207350','7187210139','7187210350','7187210458','7187210608','7187210643','7187210949','7187211269','7187211638','7187211805','7187211810','7187211904',

'7187211938','7187212099','7187212723','7187214662','7187214693','7187214974','7187215018','7187215202','7187215260','7187215726','7187216790','7187217078',

'7187217415','7187217652','7187230780','7187260513','7187261057','7187261364','7187261847','7187262398','7187262519','7187262573','7187262870','7187264887',

'7187265185','7187265424','7187265551','7187266259','7187266489','7187268008','7187271050','7187277766','7187278594','7187280271','7187280664','7187281954',

'7187282837','7187282877','7187283342','7187284730','7187285104','7187285215','7187285669','7187286478','7187287353','7187287542','7187287593','7187287825',

'7187288005','7187288372','7187288686','7187288808','7187290106','7187291778','7187293062','7187293310','7187293841','7187295612','7187295869','7187297627',

'7187299179','7187299911','7187310861','7187312426','7187313202','7187314421','7187317164','7187317694','7187331410','7187331609','7187332806','7187333146',

'7187333536','7187333765','7187334323','7187334420','7187335228','7187337393','7187337642','7187338725','7187382564','7187382750','7187383647','7187384962',

'7187386413','7187387307','7187388430','7187389395','7187393233','7187397938','7187400244','7187400271','7187401751','7187422142','7187424409','7187426079',

'7187429032','7187429353','7187430854','7187431763','7187431944','7187432321','7187432587','7187432612','7187432646','7187433542','7187433646','7187434553',

'7187435268','7187435814','7187436289','7187436603','7187436874','7187437597','7187438465','7187438527','7187439834','7187450063','7187450844','7187451504',

'7187451728','7187451821','7187453029','7187453212','7187453629','7187453861','7187453964','7187454062','7187454287','7187454354','7187454674','7187455407',

'7187455579','7187456854','7187456970','7187457167','7187457320','7187457946','7187458410','7187460042','7187460871','7187460962','7187461205','7187461252',

'7187463048','7187463553','7187464128','7187464172','7187464384','7187465007','7187465669','7187465760','7187466080','7187468291','7187469464','7187473042',

'7187475607','7187476117','7187476139','7187480944','7187481208','7187482091','7187482464','7187483068','7187483147','7187483659','7187483796','7187483952',

'7187485133','7187486602','7187489896','7187520567','7187521815','7187529750','7187568064','7187590580','7187590925','7187591230','7187599425','7187599662',

'7187600238','7187600614','7187600733','7187600849','7187600873','7187601138','7187601334','7187601527','7187601549','7187601895','7187602377','7187602868',

'7187603285','7187603550','7187603876','7187604804','7187604845','7187605728','7187607406','7187607473','7187608939','7187613931','7187614612','7187615683',

'7187615717','7187616184','7187617711','7187618557','7187620539','7187622317','7187625542','7187628146','7187633771','7187635291','7187650203','7187650597',

'7187670343','7187670370','7187671309','7187671376','7187671516','7187672036','7187672363','7187672473','7187672773','7187672896','7187673849','7187674365',

'7187676549','7187679158','7187681055','7187682238','7187682254','7187687428','7187690619','7187690849','7187691545','7187692238','7187692502','7187693026',

'7187693125','7187693569','7187693707','7187693771','7187694311','7187694751','7187697063','7187697380','7187697880','7187698011','7187698379','7187698981',

'7187699471','7187714204','7187719081','7187730463','7187740128','7187762371','7187770041','7187770758','7187771640','7187771692','7187772414','7187772781',

'7187772868','7187773933','7187775504','7187775560','7187775724','7187775866','7187777576','7187780477','7187781954','7187790469','7187790618','7187790847',

'7187790865','7187791095','7187792267','7187792367','7187792948','7187793777','7187793872','7187794198','7187794582','7187794615','7187794960','7187795132',

'7187795222','7187795508','7187796043','7187796415','7187796844','7187797622','7187797681','7187797758','7187797835','7187798578','7187798794','7187798952',

'7187799293','7187820677','7187820716','7187820757','7187821174','7187821493','7187822103','7187822479','7187822492','7187822540','7187822702','7187823024',

'7187823204','7187823405','7187823581','7187823668','7187823989','7187824599','7187824778','7187825558','7187826351','7187826894','7187826983','7187827057',

'7187827815','7187827862','7187828313','7187829749','7187829884','7187830214','7187830643','7187838148','7187841041','7187842714','7187842877','7187860154',

'7187860348','7187861638','7187861863','7187861990','7187863444','7187864397','7187868894','7187870959','7187871492','7187880717','7187883321','7187884417',

'7187887402','7187889092','7187892980','7187895061','7187895240','7187895769','7187923459','7187924158','7187924204','7187926134','7187926760','7187929594',

'7187930707','7187933729','7187935623','7187935874','7187936547','7187939188','7187949210','7187960476','7187962195','7187968979','7187970769','7187971374',

'7187975362','7187983651','7187983994','7187984130','7187984495','7187989773','7188030981','7188031494','7188031811','7188032538','7188032905','7188033271',

'7188033606','7188033753','7188038130','7188050721','7188051083','7188051246','7188051305','7188051359','7188051875','7188054625','7188055706','7188057438',

'7188057519','7188150474','7188159197','7188161327','7188165502','7188167467','7188169894','7188180836','7188200638','7188211316','7188211496','7188211845',

'7188211878','7188211913','7188212220','7188212592','7188213779','7188214285','7188214610','7188214615','7188215109','7188215236','7188215646','7188216180',

'7188216483','7188216485','7188218034','7188218915','7188219076','7188220092','7188222805','7188225839','7188226323','7188230537','7188234855','7188236617',

'7188240437','7188241859','7188245305','7188260628','7188262346','7188263856','7188270971','7188271302','7188272895','7188273061','7188273870','7188274792',

'7188275066','7188275605','7188275716','7188276649','7188277330','7188277350','7188278145','7188278293','7188278323','7188278492','7188280950','7188285516',

'7188285973','7188288197','7188292735','7188293942','7188297135','7188298399','7188299337','7188300715','7188320402','7188321706','7188321916','7188321926',

'7188322075','7188322262','7188323006','7188323319','7188323354','7188324683','7188324870','7188325182','7188326340','7188329048','7188331311','7188331714',

'7188331730','7188331742','7188332505','7188333556','7188333610','7188333736','7188334762','7188336049','7188336374','7188338980','7188346173','7188351089',

'7188352162','7188352666','7188353017','7188354168','7188360337','7188360435','7188360917','7188361221','7188361278','7188361587','7188364811','7188367087',

'7188367642','7188368733','7188369328','7188369418','7188370515','7188371222','7188371429','7188371963','7188372318','7188372461','7188373207','7188374629',

'7188375153','7188375708','7188375788','7188378334','7188378631','7188378678','7188379737','7188379850','7188420088','7188420108','7188420487','7188421012',

'7188421940','7188422392','7188423238','7188423841','7188424418','7188425904','7188426274','7188427455','7188428037','7188428341','7188433641','7188433675',

'7188435308','7188435869','7188436064','7188436720','7188439150','7188451248','7188452660','7188452896','7188453751','7188455377','7188457478','7188458214',

'7188460012','7188460153','7188460335','7188460478','7188460793','7188462324','7188462862','7188463861','7188463905','7188464068','7188464123','7188464589',

'7188464839','7188464912','7188464983','7188465257','7188465355','7188465383','7188465390','7188465479','7188467469','7188467561','7188467943','7188470216',

'7188470403','7188470427','7188471608','7188472683','7188472734','7188472842','7188473326','7188474837','7188475347','7188477441','7188478426','7188478513',

'7188478664','7188478881','7188479756','7188479790','7188479921','7188479966','7188480420','7188481534','7188483836','7188491672','7188491721','7188492080',

'7188492801','7188492812','7188492904','7188494752','7188495512','7188497501','7188499599','7188500283','7188501571','7188502036','7188502046','7188502162','7188502915','7188503409','7188503490','7188506568','7188507608','7188508397','7188510494','7188512384','7188512517','7188512752','7188512775','7188513642','7188513659','7188513966','7188514081','7188514201','7188514567','7188514652','7188514756','7188516488','7188517267','7188520478','7188522314','7188523971','7188524628','7188525619','7188526316','7188527171','7188527903','7188528148','7188530195','7188530207','7188530519','7188531095','7188531825','7188532481','7188533620','7188533809','7188534256','7188534346','7188535269','7188535693','7188535846','7188536243','7188536264','7188536374','7188536626','7188536787','7188537175','7188537215','7188538059','7188538090','7188538206','7188538298','7188538749','7188538787','7188538804','7188538870','7188539238','7188539773','7188540693','7188540809','7188540910','7188540972','7188541032','7188541275','7188541328','7188541434','7188542399','7188543016','7188543290','7188543545','7188543699','7188543961','7188544302','7188544428','7188544567','7188544745','7188546417','7188547687','7188548831','7188549408','7188552037','7188555438','7188560453','7188561870','7188563263','7188566710','7188568218','7188568343','7188568547','7188571368','7188590157','7188592193','7188592547','7188592974','7188593932','7188595748','7188597452','7188597627','7188599351','7188599774','7188600033','7188600721','7188600815','7188601038','7188601292','7188604393','7188604940','7188606172','7188606349','7188606724','7188606919','7188611747','7188611982','7188613718','7188614261','7188618091','7188618488','7188618711','7188619607','7188630365','7188630429','7188630857','7188632954','7188635415','7188636764','7188637062','7188637288','7188637596','7188637637','7188638440','7188639551','7188680267','7188710727','7188710817','7188711820','7188712965','7188713314','7188713874','7188716101','7188716296','7188717109','7188717438','7188718310','7188753424','7188754140','7188757634','7188760654','7188765210','7188765337','7188810912','7188813925','7188818175','7188824340','7188826326','7188841344','7188842157','7188845525','7188860274','7188860707','7188861957','7188862130','7188862730','7188863048','7188865992','7188869528','7188880096','7188889015','7188911470','7188911724','7188912513','7188913924','7188914378','7188914489','7188914526','7188915048','7188915266','7188916510','7188917352','7188918005','7188918473','7188918491','7188919282','7188919420','7188919452','7188920591','7188922806','7188922867','7188925345','7188929381','7188930361','7188930765','7188930769','7188930781','7188931739','7188934587','7188936794','7188936844','7188936920','7188937595','7188937987','7188939204','7188940023','7188940245','7188940583','7188941210','7188942021','7188942576','7188942739','7188944359','7188944923','7188945419','7188945542','7188947098','7188962071','7188964074','7188965090','7188965674','7188968370','7188971265','7188973159','7188973325','7188973396','7188973472','7188973791','7188973798','7188974660','7188974948','7188976532','7188979451','7188980374','7188980469','7188980983','7188981086','7188981279','7188982197','7188982843','7188983040','7188983432','7188983705','7188983752','7188985279','7188985767','7188986009','7188986040','7188986610','7188987079','7188987865','7188988085','7188988198','7188988573','7188988864','7188989665','7188990103','7188990119','7188990156','7188990751','7188991615','7188992085','7188993414','7188994676','7188995413','7188996403','7188997089','7188997463','7188998036','7188998062','7188999089','7188999457','7188999616','7189010817','7189010869','7189010950','7189011842','7189012184','7189013604','7189015201','7189015670','7189040568','7189180251','7189190849','7189191573','7189191811','7189192171','7189192762','7189193202','7189193619','7189195840','7189198817','7189211632','7189213149','7189215906','7189216319','7189221817','7189311472','7189312569','7189314304','7189314578','7189314783','7189315109','7189320273','7189320839','7189321812','7189321813','7189321823','7189323181','7189323583','7189323947','7189324076','7189324711','7189325322','7189325424','7189325629','7189327421','7189329017','7189330115','7189330291','7189333740','7189336140','7189336406','7189337682','7189338394','7189339274','7189339377','7189340348','7189340996','7189341326','7189341345','7189341549','7189342218','7189342516','7189342833','7189342979','7189343378','7189344788','7189346184','7189346884','7189347947','7189348049','7189348614','7189371547','7189371984','7189372573','7189374692','7189376179','7189390029','7189390987','7189391505','7189393244','7189393318','7189395509','7189395651','7189396250','7189396727','7189400327','7189400688','7189401746','7189402512','7189404481','7189404650','7189412860','7189414356','7189414503','7189417093','7189417241','7189422285','7189422525','7189422650','7189430175','7189430196','7189430199','7189430288','7189430314','7189430371','7189430372','7189435996','7189436410','7189437530','7189450538','7189451102','7189451257','7189451832','7189453608','7189453710','7189455023','7189455172','7189455865','7189456189','7189457131','7189457501','7189457510','7189459661','7189459708','7189460560','7189461476','7189462124','7189462189','7189462805','7189463256','7189463486','7189463656','7189464256','7189464494','7189464791','7189466104','7189467038','7189467307','7189468229','7189470546','7189470582','7189470585','7189470597','7189470605','7189470607','7189470613','7189470615','7189470628','7189470642','7189470644','7189470658','7189470669','7189470675','7189470682','7189470698','7189470710','7189470721','7189470727','7189470728','7189470730','7189470732','7189470733','7189470749','7189470751','7189470752','7189470757','7189470758','7189470760','7189470762','7189470767','7189470780','7189470783','7189470786','7189470794','7189470796','7189470797','7189470807','7189470826','7189470828','7189470830','7189470834','7189470838','7189470840','7189470841','7189470843','7189470845','7189470873','7189470876','7189470884','7189470887','7189470889','7189470907','7189470916','7189470925','7189470926','7189470928','7189470932','7189470936','7189470939','7189470957','7189470963','7189470964','7189470969','7189471052','7189471057','7189471058','7189471059','7189471082','7189471098','7189471105','7189471138','7189471145','7189471151','7189471154','7189471244','7189471251','7189471255','7189471261','7189471262','7189471275','7189471278','7189471282','7189471292','7189471328','7189471362','7189471363','7189471364','7189471365','7189471386','7189471392','7189471393','7189471422','7189471433','7189471461','7189471464','7189471491','7189471494','7189471570','7189471606','7189471765','7189485548','7189486706','7189492489','7189494656','7189511069','7189512276','7189512277','7189517838','7189518087','7189518760','7189518884','7189518984','7189519303','7189519836','7189530975','7189560093','7189560231','7189560238','7189560320','7189560503','7189561005','7189561047','7189561048','7189561272','7189561348','7189561556','7189561794','7189562168','7189563981','7189564479','7189564486','7189564528','7189565627','7189565739','7189565788','7189565843','7189566347','7189566542','7189567186','7189567558','7189568321','7189568330','7189568564','7189569490','7189610502','7189612956','7189619150','7189630168','7189630788','7189631570','7189632013','7189632074','7189632976','7189633192','7189633751','7189633822','7189634004','7189639794','7189650315','7189651103','7189651884','7189652083','7189661111','7189662418','7189665849','7189667528','7189687057','7189688964','7189689587','7189690185','7189690885','7189692709','7189693115','7189693601','7189699311','7189721139','7189721219','7189721390','7189721450','7189721459','7189722897','7189722954','7189722960','7189723002','7189723146','7189723182','7189723779','7189723819','7189723829','7189724498','7189724815','7189725504','7189726549','7189727009','7189727643','7189728304','7189792450','7189793350','7189794053','7189794743','7189798380','7189800198','7189800622','7189800792','7189805913','7189811154','7189812789','7189813418','7189815888','7189820166','7189825750','7189827859','7189828035','7189829217','7189831741','7189838329','7189838380','7189841062','7189842845','7189846914','7189848552','7189870577','7189874293','7189875789','7189876222','7189879213','7189879681','7189911639','7189912579','7189913073','7189913094','7189913873','7189915321','7189915984','7189916941','7189918551','7189919151','7189920641','7189920735','7189921121','7189921509','7189921734','7189921834','7189922143','7189923580','7189924081','7189925319','7189925891','7189925905','7189928280','7189929503','7189930243','7189930975','7189933109','7189934464','7189934547','7189934572','7189935734','7189937486','7189944713','7189946524','7189960318','7189960545','7189960549','7189960625','7189960729','7189961235','7189961586','7189962444','7189962713','7189963977','7189964152','7189964676','7189965292','7189965389','7189966035','7189966784','7189967140','7189968101','7189968183','7189968213','7189968741','7189970170','7189971087','7189976952','7189977314','7189979058','7189979624','7189981124','7189982008','7189982855','7189982959','7189986214','7189988389','7189988460','8452259089','8452468943','8452791399','8452925045','8453521785','8453522864','8453523813','8453524211','8453524484','8453527924','8453528034','8453528172','8453562773','8453565029','8453567444','8453580273','8453580760','8453585911','8453712440','8453712620','8453716119','8454250613','8454251421','8454254079','8454254784','8454254854','8454261269','8454262599','8454266457','8454267459','8454291796','8454340773','8454341805','8454344789','8454542436','8454625953','8454837658','8455284094','8455343415','8455616294','8455651283','8455666970','8455690205','8455697032','8455730144','8456213863','8456265009','8456265515','8456271377','8456473742','8456474340','8457271173','8457277745','8457532472','8457866080','8457867348','8457886780','8457916472','8457918483','8457940405','8458964868','8459477094','9142321846','9142351023','9142355342','9142356043','9142378590','9142411651','9142412108','9142412336','9142412908','9142414382','9142421759','9142500167','9142500271','9142500272','9142713418','9142767666','9142767667','9143321390','9143324572','9143324785','9143328172','9143371090','9143472664','9143750640','9143750681','9143752606','9143752878','9143752980','9143755930','9143756064','9143759018','9143761319','9143761966','9143763954','9143765804','9143765899','9143767153','9143769526','9143789171','9143817430','9144222671','9144233363','9144233917','9144234068','9144234593','9144234721','9144234761','9144237450','9144237869','9144725032','9144725297','9144763768','9144764828','9144769317','9144769580','9144769844','9144780514','9144783532','9144785083','9144787557','9144791298','9145240559','9145280867','9145286518','9145762553','9145763182','9145763890','9145763975','9145764004','9145765023','9145765828','9145768225','9145769186','9145919586','9146311708','9146322303','9146324887','9146328092','9146329447','9146331515','9146333927','9146360297','9146541848','9146632612','9146643301','9146650671','9146657046','9146678126','9146684242','9146684762','9146830825','9146841842','9146847041','9146869786','9146901033','9146909817','9146931130','9146931542','9146982426','9146987134','9147090115','9147120857','9147349202','9147362948','9147367630','9147369786','9147371431','9147378154','9147379064','9147382217','9147388319','9147391652','9147398481','9147472024','9147473258','9147477573','9147612357','9147620328','9147623299','9147631377','9147632100','9147693136','9147773590','9147885645','9148332108','9149210097','9149229331','9149237593','9149238364','9149340694','9149342309','9149349245','9149375812','9149379796','9149390280','9149391344','9149399174','9149399237','9149417746','9149447059','9149464715','9149485639','9149492004','9149493137','9149615666','9149629652','9149632051','9149636257','9149650453','9149650712','9149652521','9149653445','9149663621','9149680803','9149682051','9149682130','9149682392','9149684868','9149687023','9149690418','9149690661','9149693021','9149693852','9149695716','9173268901','9174922134','9174929446','9175211440','9175212118','9175212147','9175217228','9175217252','NEW1007818','NEW1009805','NEW1009942','NEW1009946','NEW1010040','NEW1010090') or ordUidCustomer = 277173336)

I have seen similar performance problems in 2005 when using huge lists of IN values in the where clause. I am not able to change the SQL or the length of the list because it is generated by a report writer and I have no control over what the user selects.

Try 2005 SP2 (on a test machine) and see if that helps.
|||

Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.

Dan

|||SQL2005 do have performance problem for IN and NOT IN. Here is another thread talking about this issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990564&SiteID=1

You can use left join or right join to implement same behavior with IN and NOT IN. That will improve your query performance.
|||

>>Maybe there could be some performance gains by putting that huge "IN" list into a temporary table, and then indexing the temporary table.<<

This is what I was thinking. I have a query like this but it passes it as a delimited an I run a split function on it like this:

Code Snippet

declare @.listIds varchar(max)
set @.listIds = '1,2,3,4,5,6,7,8,9'

;with digits as(
select 1 as i union all select 2 as i union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 0),

sequence as (
SELECT distinct D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i) + (10000*D5.i) + (100000*D6.i) as i
FROM digits AS D1, digits AS D2,
digits AS D3,digits as D4, digits as D5, digits as D6
),
list as (
SELECT substring(',' + @.listIds + ',', i + 1,
charindex(',', ',' + @.listIds + ',', i + 1) - i - 1) AS responseActivityWorkId
FROM sequence
WHERE i <= len(',' + @.listIds + ',') - 1
AND substring(',' + @.listIds + ',', i, 1) = ',')
select *
from list

The select * from list would just be replaced by your query with a join to this list CTE, and remove the IN (big old list). Then format the list as 'value,value,value' and go for it. I support 999999 as the max length of the parameter, but just adding a D7 (following the pattern) and you could go even higher. (I would like to do your data, but the rotation around the screen would take me a long time to reformat

If you have CTE performance issues, just put the results of the list query into a temp table, add an index and try that as a join or IN clause. Then it should be as fast as you can get then.

If you would like to read more about the subject of arrays and lists in SQL Server, please try the excellent article: http://www.sommarskog.se/arrays-in-sql-2005.html

|||

they got around this by using temp tables. i tried this on some new 64 bit servers we just bought with 20GB RAM and same result. 100% CPU spike but execution time is around 30 seconds instead of 10 minutes

Push Subscription Wizard

Hello,
I am trying to setup a push subscription between two SQL 2000 Servers using
the Push Subscription Wizard through Enterprise Manager. After clicking
"Finish" at the end of the wizard, Enterprise Manager hangs at the first
step "Enabling this server as a subscriber". Can anyone help me identify
the problem, I can't see anything in the logs.
Thanks,
Ben Gibson
Ben,
additionally, you could use sp_who2 and dbcc inputbuffer(spid) as per usual
to get some more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

push snapshot

Hi I'm trying to setup a push replication between 2 servers on different
networks. both machines can ping eachother using there machine names. I setup
the publication and the distributor on the push server and generate a
snapshot. I then run the distributor but it fails to connect to the
subscriber.
thanks for the help.
Mark
You need to ensure the @.@.servername of the subscriber is how the
publisher/distributor refer to it and vice versa. If you have to use IP
address, you can setup client side alias on the distributor that maps the IP
address to the @.@.servername of the subscriber
“This posting is provided "AS IS" with no warranties, and confers no rights.”
"ACDMark" wrote:

> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark
|||hi ,
be sure that your sql agent services is
running on a domain account
that has permission to your snapshot folder
and the database
thanks,
joey
"ACDMark" wrote:

> Hi I'm trying to setup a push replication between 2 servers on different
> networks. both machines can ping eachother using there machine names. I setup
> the publication and the distributor on the push server and generate a
> snapshot. I then run the distributor but it fails to connect to the
> subscriber.
> thanks for the help.
> Mark

Pulling data from two different servers

Hi,
I have some data I'd like to retrieve and run a report from, but the data is
one 2 different servers. I don't know how to set this up in report manager
or create a query that says something like
select server.database.field, differentserver.database.field etc.
Any help would be greatly appreciated.
Thanks
JillJill,
The solution to your question lies solely within your SQL query and
Reporting Services will not affect how you solve this problem.
The easiest way to perform this task is to use a four-part query that
specifies the tables you want to pull from your remote server.
The format for the reference to the table is
"Servername.databasename.owner.table"
For example
select *
from myserver1.pubs.dbo.authors as A
join myserver2.pubs.dbo.titleauthor as B
on A.au_id = b.au_id
To perform this type of query you'll need to create a linked server. For
instance, if your connection is "myserver1" you need to create a linked
server on the myserver1 server to myserver2. If you Google "Linked Server
SQL Security" you will find articles instructing you how to do so. While
explaining the query is fairly straight forward, explaining Linked Server
security and permissions is best left up to people who have already written
about it.
Hope this helps.
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> Hi,
> I have some data I'd like to retrieve and run a report from, but the data
> is
> one 2 different servers. I don't know how to set this up in report
> manager
> or create a query that says something like
> select server.database.field, differentserver.database.field etc.
> Any help would be greatly appreciated.
> Thanks
> Jill|||If you tables are large you need to be very careful with how you use the 4
part naming method of using linked servers. You can end up where an
incredible amount of data is brought locally to do the join. You are better
off to do this in two parts. First use openquery instead of 4 part naming.
Have two temp tables that two separate queries put their results into. Then
join the two temp tables together. The performance will be much much better.
If your tables are of any size I strongly recommend this approach (speaking
from painful learning experience).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Jill,
> The solution to your question lies solely within your SQL query and
> Reporting Services will not affect how you solve this problem.
> The easiest way to perform this task is to use a four-part query that
> specifies the tables you want to pull from your remote server.
> The format for the reference to the table is
> "Servername.databasename.owner.table"
> For example
> select *
> from myserver1.pubs.dbo.authors as A
> join myserver2.pubs.dbo.titleauthor as B
> on A.au_id = b.au_id
> To perform this type of query you'll need to create a linked server. For
> instance, if your connection is "myserver1" you need to create a linked
> server on the myserver1 server to myserver2. If you Google "Linked Server
> SQL Security" you will find articles instructing you how to do so. While
> explaining the query is fairly straight forward, explaining Linked Server
> security and permissions is best left up to people who have already
written
> about it.
> Hope this helps.
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> > Hi,
> > I have some data I'd like to retrieve and run a report from, but the
data
> > is
> > one 2 different servers. I don't know how to set this up in report
> > manager
> > or create a query that says something like
> > select server.database.field, differentserver.database.field etc.
> > Any help would be greatly appreciated.
> >
> > Thanks
> > Jill
>|||While not as robust as the other solutions, you could also use a subreport
to accomplish the same thing.
Mike G.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> If you tables are large you need to be very careful with how you use the 4
> part naming method of using linked servers. You can end up where an
> incredible amount of data is brought locally to do the join. You are
> better
> off to do this in two parts. First use openquery instead of 4 part naming.
> Have two temp tables that two separate queries put their results into.
> Then
> join the two temp tables together. The performance will be much much
> better.
> If your tables are of any size I strongly recommend this approach
> (speaking
> from painful learning experience).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
>> Jill,
>> The solution to your question lies solely within your SQL query and
>> Reporting Services will not affect how you solve this problem.
>> The easiest way to perform this task is to use a four-part query that
>> specifies the tables you want to pull from your remote server.
>> The format for the reference to the table is
>> "Servername.databasename.owner.table"
>> For example
>> select *
>> from myserver1.pubs.dbo.authors as A
>> join myserver2.pubs.dbo.titleauthor as B
>> on A.au_id = b.au_id
>> To perform this type of query you'll need to create a linked server. For
>> instance, if your connection is "myserver1" you need to create a linked
>> server on the myserver1 server to myserver2. If you Google "Linked Server
>> SQL Security" you will find articles instructing you how to do so. While
>> explaining the query is fairly straight forward, explaining Linked Server
>> security and permissions is best left up to people who have already
> written
>> about it.
>> Hope this helps.
>>
>> "Jill" <Jill@.discussions.microsoft.com> wrote in message
>> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
>> > Hi,
>> > I have some data I'd like to retrieve and run a report from, but the
> data
>> > is
>> > one 2 different servers. I don't know how to set this up in report
>> > manager
>> > or create a query that says something like
>> > select server.database.field, differentserver.database.field etc.
>> > Any help would be greatly appreciated.
>> >
>> > Thanks
>> > Jill
>>
>|||True. I should have mentioned this. If it is master/detail type of
relationship the subreports are the way to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:OMxYhDTFFHA.1968@.tk2msftngp13.phx.gbl...
> While not as robust as the other solutions, you could also use a subreport
> to accomplish the same thing.
> Mike G.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> > If you tables are large you need to be very careful with how you use the
4
> > part naming method of using linked servers. You can end up where an
> > incredible amount of data is brought locally to do the join. You are
> > better
> > off to do this in two parts. First use openquery instead of 4 part
naming.
> > Have two temp tables that two separate queries put their results into.
> > Then
> > join the two temp tables together. The performance will be much much
> > better.
> > If your tables are of any size I strongly recommend this approach
> > (speaking
> > from painful learning experience).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> > news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> >> Jill,
> >>
> >> The solution to your question lies solely within your SQL query and
> >> Reporting Services will not affect how you solve this problem.
> >>
> >> The easiest way to perform this task is to use a four-part query that
> >> specifies the tables you want to pull from your remote server.
> >>
> >> The format for the reference to the table is
> >> "Servername.databasename.owner.table"
> >>
> >> For example
> >>
> >> select *
> >> from myserver1.pubs.dbo.authors as A
> >> join myserver2.pubs.dbo.titleauthor as B
> >> on A.au_id = b.au_id
> >>
> >> To perform this type of query you'll need to create a linked server.
For
> >> instance, if your connection is "myserver1" you need to create a linked
> >> server on the myserver1 server to myserver2. If you Google "Linked
Server
> >> SQL Security" you will find articles instructing you how to do so.
While
> >> explaining the query is fairly straight forward, explaining Linked
Server
> >> security and permissions is best left up to people who have already
> > written
> >> about it.
> >>
> >> Hope this helps.
> >>
> >>
> >> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> >> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> >> > Hi,
> >> > I have some data I'd like to retrieve and run a report from, but the
> > data
> >> > is
> >> > one 2 different servers. I don't know how to set this up in report
> >> > manager
> >> > or create a query that says something like
> >> > select server.database.field, differentserver.database.field etc.
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks
> >> > Jill
> >>
> >>
> >
> >
>

publishing sql 2005

Hi all,
I need to publish my SQL2005 cluster in ISA 2004 for access to my DMZ Web
servers. But I need to know which TCP port to publish. When I check SQL
Config Manager it says dynamic ports?
TIA!Hello param@.community.nospam,
Start with 1433, and make sure you use the SAC to enable TCP/IP.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels

publishing a subscription ( 3 servers )

I have a publisher that publishes a subset of master data ( transactional ).
The subscriber then publishes the same tables to dumb, live servers.
Transactional again. The master server ( the first one in the chain) is the
distributor. Replication is initiated via the Replication ActiveX objects
from a .NET application.
Once I have applied the snapshots in order and configured the servers,
replication breaks after about a day with errors about not being able to
drop tables on the intermediate server because they are participating in
replication. No surprise about the error, except since I am using
transactional replication everywhere, why is it trying to drop the table?
it is a push-only publication, I do not need merge replication, conflict
resolution, or updating from the subscriber. How can I prevent my error.
Surely what I want to do can't be so unusual?
Think Content Management Systems.
Admin database -> publishes to - > staging database -> publishes to -> live
database.
This should be straight-forward, no?
thanks for any help guys.
Leon
Leon,
your @.pre_creation_cmd is drop. If you create the first publication and
subscription then set it going (initialize it), then create the second one -
the republisher, it should be ok. You will have problems if you need to
reinitialize the first publisher/subscriber pair and will receive the
message you saw, so in this case you'll first need to drop the second
publication, and readd it later.
HTH,
Paul Ibison
|||( whoops - must remember to Reply Group!! )
thanks Paul. I know this happens when I run the initial snapshot, and I
create the publications very carefully as a result ( I ran a generate sql on
the pubs to save time )
The first time I set this up, everything was fine, except I needed to drop
and recreate the second publication on account of a change in configuration.
then the problems started. It all works fine for about half a day, then I
get the error - so I wonder if I haven't properly removed the publications
that first time... I'm going to clear everything, diable replication and run
from a clean slate here, and keep in mind what you're saying. Lucky that I
can eh!
Are there any other gotchas I need to be aware of when re-publishing a
subscribed table?
Thanks again
Leon
-- Original Message --
From: "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
Newsgroups: microsoft.public.sqlserver.replication
Sent: Monday, May 17, 2004 11:00 AM
Subject: Re: publishing a subscription ( 3 servers )

> Leon,
> your @.pre_creation_cmd is drop. If you create the first publication and
> subscription then set it going (initialize it), then create the second
one -
> the republisher, it should be ok. You will have problems if you need to
> reinitialize the first publisher/subscriber pair and will receive the
> message you saw, so in this case you'll first need to drop the second
> publication, and readd it later.
> HTH,
> Paul Ibison
>
|||I think you need to
1) script out the schema you want replicated on your publisher, run it on the first subscriber/publisher and the second subscriber
2) when you create your publication on both your publisher and subscriber/publisher in the article properties selection delete all the data in the existing table in the name conflicts section.
3) create your publication and subscription from your publisher to your publisher subscriber.
4) generate your snapshot and replicate it from your publisher to your publisher/susbcriber.
5) when 4 is complete, repeat steps 3 and 4 for your publisher/subscriber to your subscriber.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Publishers disappeared from under replication monitor

We have this problem on one of our servers. We tried restarting the agent
service but that did not resolve this. Is is so crucial that we identify a
maintenance window asap to take care of this or do you think that it can
wait a few days? It does not seem to be causing any problems. The machine
will most likely be rebooted in the near future for security patches,
anyway.
Michelle
You hit the nail on the head. Restarting the SQL agent cured the problem.
Many thanks!
Ray
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O96$4HLVEHA.2992@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> that's your problem. Bounce sql server agent. If this does not solve it,
> bounce sql server.
> "Ray Price" <ray.price@.gartner.com> wrote in message
> news:%23dff%237HVEHA.3944@.tk2msftngp13.phx.gbl...
> have
> Enterprise
though.
>
what happens when you issue this command?
sp_MSload_replication_status
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"michelle" <michelle@.nospam.com> wrote in message
news:%23UIngtjfEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> We have this problem on one of our servers. We tried restarting the agent
> service but that did not resolve this. Is is so crucial that we identify a
> maintenance window asap to take care of this or do you think that it can
> wait a few days? It does not seem to be causing any problems. The machine
> will most likely be rebooted in the near future for security patches,
> anyway.
> Michelle
>
> You hit the nail on the head. Restarting the SQL agent cured the problem.
> Many thanks!
> Ray
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O96$4HLVEHA.2992@.TK2MSFTNGP12.phx.gbl...
publications
> though.
>
>
|||Same as Ray:
Server: Msg 208, Level 16, State 1, Procedure sp_MSload_replication_status,
Line 14
Invalid object name 'tempdb.dbo.MSreplication_agent_status'.
Which is why I restarted the agent service. It didin't help me out though.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ewTOZxjfEHA.2468@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> what happens when you issue this command?
> sp_MSload_replication_status
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:%23UIngtjfEHA.632@.TK2MSFTNGP12.phx.gbl...
agent[vbcol=seagreen]
a[vbcol=seagreen]
machine[vbcol=seagreen]
problem.[vbcol=seagreen]
it,[vbcol=seagreen]
helps?
> publications
>
|||Can you give me a history of this problem.
Note that if you script out publications and recreate them on the same
server but in different databases and have the same snapshot agent and
distribution agent name you can get a condition like this.
By chance did you do something like this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"michelle" <michelle@.nospam.com> wrote in message
news:u$PVj7jfEHA.720@.TK2MSFTNGP11.phx.gbl...
> Same as Ray:
> Server: Msg 208, Level 16, State 1, Procedure
sp_MSload_replication_status,[vbcol=seagreen]
> Line 14
> Invalid object name 'tempdb.dbo.MSreplication_agent_status'.
> Which is why I restarted the agent service. It didin't help me out though.
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ewTOZxjfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> agent
identify[vbcol=seagreen]
> a
can
> machine
> problem.
> it,
> helps?
>
|||Not exactly but what was done could have had the same effect...
We had a problem a couple of weeks ago. I'll try to make a long story short.
Basically the only database on the server being replicated suddenly had 'no'
tables showing up. This happened at the same time that the job that backup
the transaction logs failed (SQL LiteSpeed). We have a support case open
with MS but at this point they are asking that we pursue it with LiteSpeed
(yet we do not have a support contract). We were getting assertion errors.
Not sure if you care to hear about the details of that...
This seemed like some kind of corruption in the database. We thought that we
would first try to detach and re-attach the files but since it was being
replicated, we were unable to do so because it was being replicated. One of
the other DBAs here 'deleted everything from the distribution database' (I
think that's what he said). I explained that the user database kept
replication 'stuff' in it, too so that would not do any good. Too Late.
Even a restore would require that we get rid of the existing database so we
restarted the sql server service to see what happened. Lo-and-behold, the
database came back up.
The next day we set up transactional replication again (Yes, probably used
the same name). It seemed to be fine. I don't actually recall if the
publisher was showing up at that time or not. Someone later rebooted the
server with the subscriber on it and the jobs did not start back up on the
publisher (couldn't authenticate job owner, have now changed job owner).
That's the only reason why I noticed that there was even a problem. When I
tried to get it running again, it had to retry numerous times: The process
could not execute 'sp_replcmds' on 'servername'. But, I did get it running.
Due to all of this mess, I was going to try to use the validate subscription
thing but I couldn't find it. I ended up just doing row counts. That
combined with my understanding of the OLTP activity and checking with the
users of the subscriber, it seems to be fine - except that it is missing
from the replication monitor folder.
Plus, the conflict tables are called aonflict instead of conflict. I had
already researched this and believe that it is not anything to worry about.
I was thinking of waiting until after the sql server service was restarted
and if it is still not right, setting up a maintenance window to drop the
publication, disable this server as a publisher, drop the distribution
database, and start over.
What do you think?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u%23u8dCkfEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Can you give me a history of this problem.
> Note that if you script out publications and recreate them on the same
> server but in different databases and have the same snapshot agent and
> distribution agent name you can get a condition like this.
> By chance did you do something like this?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:u$PVj7jfEHA.720@.TK2MSFTNGP11.phx.gbl...
> sp_MSload_replication_status,
though.[vbcol=seagreen]
> identify
> can
patches,[vbcol=seagreen]
solve[vbcol=seagreen]
replication/publications
>
|||I think you are hosed.
You are correct in your assessment of the conflict table. The first conflict
table is named conflict_publicationName_tableName. The next conflict table
for that table is named aonflict_publicationName_tableName, the third
bonflict_publicationName_tableName, the 4th
donflict_publicationName_tableName, all the way up till you hit z, then its
zzonflict_publicationName_tableName, zzzonflict_publicationName_tableName,
etc.
I think your best bet is to remove replication using the wizards, and then
try to re-install it to get to a fresh base. Then recreate your publications
and subscriptions.
Is it possible for you to redistribute your snapshot? This is the safest way
to get your replication solution back to some level of consistency.
Is it also possible for you to restore your distribution database back?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"michelle" <michelle@.nospam.com> wrote in message
news:%23849jPkfEHA.2916@.TK2MSFTNGP12.phx.gbl...
> Not exactly but what was done could have had the same effect...
> We had a problem a couple of weeks ago. I'll try to make a long story
short.
> Basically the only database on the server being replicated suddenly had
'no'
> tables showing up. This happened at the same time that the job that backup
> the transaction logs failed (SQL LiteSpeed). We have a support case open
> with MS but at this point they are asking that we pursue it with LiteSpeed
> (yet we do not have a support contract). We were getting assertion errors.
> Not sure if you care to hear about the details of that...
> This seemed like some kind of corruption in the database. We thought that
we
> would first try to detach and re-attach the files but since it was being
> replicated, we were unable to do so because it was being replicated. One
of
> the other DBAs here 'deleted everything from the distribution database' (I
> think that's what he said). I explained that the user database kept
> replication 'stuff' in it, too so that would not do any good. Too Late.
> Even a restore would require that we get rid of the existing database so
we
> restarted the sql server service to see what happened. Lo-and-behold, the
> database came back up.
> The next day we set up transactional replication again (Yes, probably used
> the same name). It seemed to be fine. I don't actually recall if the
> publisher was showing up at that time or not. Someone later rebooted the
> server with the subscriber on it and the jobs did not start back up on the
> publisher (couldn't authenticate job owner, have now changed job owner).
> That's the only reason why I noticed that there was even a problem. When I
> tried to get it running again, it had to retry numerous times: The process
> could not execute 'sp_replcmds' on 'servername'. But, I did get it
running.
> Due to all of this mess, I was going to try to use the validate
subscription
> thing but I couldn't find it. I ended up just doing row counts. That
> combined with my understanding of the OLTP activity and checking with the
> users of the subscriber, it seems to be fine - except that it is missing
> from the replication monitor folder.
> Plus, the conflict tables are called aonflict instead of conflict. I had
> already researched this and believe that it is not anything to worry
about.[vbcol=seagreen]
> I was thinking of waiting until after the sql server service was restarted
> and if it is still not right, setting up a maintenance window to drop the
> publication, disable this server as a publisher, drop the distribution
> database, and start over.
> What do you think?
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u%23u8dCkfEHA.644@.tk2msftngp13.phx.gbl...
> though.
the[vbcol=seagreen]
it[vbcol=seagreen]
> patches,
> solve
in
> replication/publications
>
|||Thanks for your advice. Not so sure about restoring the distribution
database. This is the only publication and yes, we would start the
subscription from scratch with a new snapshot. The subscription is just for
reporting so we can work it out.
Michelle
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u$m6VgkfEHA.252@.TK2MSFTNGP11.phx.gbl...
> I think you are hosed.
> You are correct in your assessment of the conflict table. The first
conflict
> table is named conflict_publicationName_tableName. The next conflict table
> for that table is named aonflict_publicationName_tableName, the third
> bonflict_publicationName_tableName, the 4th
> donflict_publicationName_tableName, all the way up till you hit z, then
its
> zzonflict_publicationName_tableName, zzzonflict_publicationName_tableName,
> etc.
> I think your best bet is to remove replication using the wizards, and then
> try to re-install it to get to a fresh base. Then recreate your
publications
> and subscriptions.
> Is it possible for you to redistribute your snapshot? This is the safest
way[vbcol=seagreen]
> to get your replication solution back to some level of consistency.
> Is it also possible for you to restore your distribution database back?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:%23849jPkfEHA.2916@.TK2MSFTNGP12.phx.gbl...
> short.
> 'no'
backup[vbcol=seagreen]
LiteSpeed[vbcol=seagreen]
errors.[vbcol=seagreen]
that[vbcol=seagreen]
> we
> of
(I[vbcol=seagreen]
> we
the[vbcol=seagreen]
used[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
process[vbcol=seagreen]
> running.
> subscription
the[vbcol=seagreen]
> about.
restarted[vbcol=seagreen]
the[vbcol=seagreen]
> the
that[vbcol=seagreen]
> it
The[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
monitor/publishers
> in
>

Publishers disappeared from under replication monitor

Strange thing has happened on one of our servers. The publications have
disappeared from under the replication monitor/publishers in Enterprise
Manager. We can still see them under replication/publications though.
Any ideas what may cause this?
Thanks
Ray
can you issue a sp_MSload_replication_status and see if this helps?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ray Price" <ray.price@.gartner.com> wrote in message
news:uF%23AOF8UEHA.760@.TK2MSFTNGP12.phx.gbl...
> Strange thing has happened on one of our servers. The publications have
> disappeared from under the replication monitor/publishers in Enterprise
> Manager. We can still see them under replication/publications though.
> Any ideas what may cause this?
> Thanks
> Ray
>
|||It says...
Invalid object name 'tempdb.dbo.msreplication_agent_status'
Ray
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OoTpVGBVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> can you issue a sp_MSload_replication_status and see if this helps?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Ray Price" <ray.price@.gartner.com> wrote in message
> news:uF%23AOF8UEHA.760@.TK2MSFTNGP12.phx.gbl...
>
|||You hit the nail on the head. Restarting the SQL agent cured the problem.
Many thanks!
Ray
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O96$4HLVEHA.2992@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> that's your problem. Bounce sql server agent. If this does not solve it,
> bounce sql server.
> "Ray Price" <ray.price@.gartner.com> wrote in message
> news:%23dff%237HVEHA.3944@.tk2msftngp13.phx.gbl...
> have
> Enterprise
though.
>

Publisher and Subscriber with different service packs

We have several SQL Server 2000 databases servers. We would like to do
snapshot replication to a central server. The central server would be sp3a
and most of the servers are sp3a. We still have a couple of machines at sp2
and will be upgrading shortly.. My question is: Can snapshot replication
work between Servers with different service packs. Thanks... in advance
tiny
Snapshot replication is the most forgiving replication type when it comes to
differencing service pack levels.
You should have no problems.
"Tiny13" <Tiny13@.discussions.microsoft.com> wrote in message
news:A8607009-C020-4125-B01E-EF2DB5F334E7@.microsoft.com...
> We have several SQL Server 2000 databases servers. We would like to do
> snapshot replication to a central server. The central server would be
> sp3a
> and most of the servers are sp3a. We still have a couple of machines at
> sp2
> and will be upgrading shortly.. My question is: Can snapshot replication
> work between Servers with different service packs. Thanks... in advance
> tiny

publisher and subscriber database differ in size (newbie question)

Hi there
I have 2 SQL servers. One of them is set up as publisher and the other as
subscriber. I have a database set up for replication so the changes gets
copied to the subscriber. I wish to use the subscriber in the event that the
publisher becomes unavaible (blue screen, hardware error, whatever)
However when I look on the disk the 2 databases differ greatly in size, the
one on the publisher is 450 Mb and the subscriber is 260 Mb.
Aren't these 2 supposed to be exactly the same size? It seems as if the
transaction log gets flushed weekly when I run a full backup of the
database, but the subscriber database is still consistently smaller.
Is this normal behaviour?
Thanks in advance for any input
Ib"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Hi there
> However when I look on the disk the 2 databases differ greatly in size,
> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
Perhaps the larger database has a lot of free space and the smaller one
doesn't, or the transaction log on the subscriber is smaller as it isn't
handling the client transactions directly. Have a look at each database's
properties in Management Studio. Check the amount of free space on the
general tab, and check the size of the data files and transaction logs in
the Files tab.|||Thanks for your reply.
Should data size + available space (in the Enterprise Manager) equal size on
disk?
I am running SQL 2000 by the way, if that makes a difference.
"Leon Mayne" <leon@.rmv_me.mvps.org> wrote in message
news:53744401-9495-4913-B1BF-A19AA3850C58@.microsoft.com...
> "Ib Schrader" <ibschrader@.gmail.com> wrote in message
> news:ea1chVrVIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Hi there
>> However when I look on the disk the 2 databases differ greatly in size,
>> the one on the publisher is 450 Mb and the subscriber is 260 Mb.
> Perhaps the larger database has a lot of free space and the smaller one
> doesn't, or the transaction log on the subscriber is smaller as it isn't
> handling the client transactions directly. Have a look at each database's
> properties in Management Studio. Check the amount of free space on the
> general tab, and check the size of the data files and transaction logs in
> the Files tab.|||"Ib Schrader" <ibschrader@.gmail.com> wrote in message
news:OkNC2vrVIHA.5160@.TK2MSFTNGP05.phx.gbl...
> Thanks for your reply.
> Should data size + available space (in the Enterprise Manager) equal size
> on disk?
I think Data Size is the actual size on disk inclusing the free space, but
I'm not sure.
I do remember there is a system stored procedure to give you all the details
about either the data file or log file, but I can't for the life of me
remember what it is.

Publish reports to SQL 2000 RS using VS2005

We are migrating to SQL Server 2005 (and also RS shipped with SQL 2005) but
in the middle of the process we have still some servers still not upgraded
(and running SQL Server 2000 and RS2000).
Can we deploy reports to RS servers running SQL Server 2000 using Visual
Studio 2005? It seems we cannot becasue we receive the following error
message. Is any workaround to this or do we need to mantain a copy of both
RS2000 and RS2005 rdl files and deploy each group of them using VS2003 and
VS2005 respectively? This is somewhat repeat the same job twice, and might
lead to mistakes and differences between the same file of each version.
The error messages are in spanish but an approximated translation is
provided (It probably does not match the real error message in native
english).
Thanks a lot.
===================================
No se pudo establecer una conexión al servidor de informes
https://www.mydomain.com/ReportServer. (Diseñador de informes de Microsoft)
A connection to reports server at https://www.mydomain.com/ReportServer
could not be stablished (Microsoft Form Designer)
===================================
Error al intentar conectar con el servidor de informes. Compruebe la
información de conexión y que la versión del servidor de informes sea
compatible. (Microsoft.ReportingServices.Designer)
An error occurred while trying to connect to reports server. Check the
connection information and reports server version be compatable
(Microsoft.ReportingServices.Designer)
--
Ubicación del programa:
en
Microsoft.SqlServer.ReportingServices2005.RSConnection.MissingEndpointException.ThrowIfEndpointMissing(WebException
e)
en
Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String
Item)
en
Microsoft.ReportDesigner.Project.ReportServiceClient.GetItemType(String
item)
en Microsoft.ReportDesigner.Project.ReportServiceClient.CheckAuthorized()
en
Microsoft.ReportDesigner.Project.ReportClientManager.GetCredentials(String
url)
en Microsoft.ReportDesigner.Project.ReportProjectDeployer.PrepareDeploy()
===================================
Error de la solicitud con el código de estado HTTP 404: Not Found.
(System.Web.Services)
--
Ubicación del programa:
en
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage
message, WebResponse response, Stream responseStream, Boolean asyncCall)
en System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
methodName, Object[] parameters)
en
Microsoft.SqlServer.ReportingServices2005.ReportingService2005.GetItemType(String
Item)
en
Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String
Item)You cannot deploy 2005 reports to RS 2000 server. However, you can deploy RS
2000 reports to a RS 2005 server. One caveat. When I did this I had a few
cases of some minor buggy behavior that went away when I converted to RS
2005. But, you might want to try that out.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jagb" <jagb@.NOSPAM.com> wrote in message
news:utyRqIwfGHA.2456@.TK2MSFTNGP04.phx.gbl...
> We are migrating to SQL Server 2005 (and also RS shipped with SQL 2005)
> but in the middle of the process we have still some servers still not
> upgraded (and running SQL Server 2000 and RS2000).
> Can we deploy reports to RS servers running SQL Server 2000 using Visual
> Studio 2005? It seems we cannot becasue we receive the following error
> message. Is any workaround to this or do we need to mantain a copy of both
> RS2000 and RS2005 rdl files and deploy each group of them using VS2003 and
> VS2005 respectively? This is somewhat repeat the same job twice, and might
> lead to mistakes and differences between the same file of each version.
> The error messages are in spanish but an approximated translation is
> provided (It probably does not match the real error message in native
> english).
> Thanks a lot.
> ===================================> No se pudo establecer una conexión al servidor de informes
> https://www.mydomain.com/ReportServer. (Diseñador de informes de
> Microsoft)
> A connection to reports server at https://www.mydomain.com/ReportServer
> could not be stablished (Microsoft Form Designer)
> ===================================> Error al intentar conectar con el servidor de informes. Compruebe la
> información de conexión y que la versión del servidor de informes sea
> compatible. (Microsoft.ReportingServices.Designer)
> An error occurred while trying to connect to reports server. Check the
> connection information and reports server version be compatable
> (Microsoft.ReportingServices.Designer)
> --
> Ubicación del programa:
> en
> Microsoft.SqlServer.ReportingServices2005.RSConnection.MissingEndpointException.ThrowIfEndpointMissing(WebException
> e)
> en
> Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String
> Item)
> en
> Microsoft.ReportDesigner.Project.ReportServiceClient.GetItemType(String
> item)
> en
> Microsoft.ReportDesigner.Project.ReportServiceClient.CheckAuthorized()
> en
> Microsoft.ReportDesigner.Project.ReportClientManager.GetCredentials(String
> url)
> en
> Microsoft.ReportDesigner.Project.ReportProjectDeployer.PrepareDeploy()
> ===================================> Error de la solicitud con el código de estado HTTP 404: Not Found.
> (System.Web.Services)
> --
> Ubicación del programa:
> en
> System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage
> message, WebResponse response, Stream responseStream, Boolean asyncCall)
> en System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String
> methodName, Object[] parameters)
> en
> Microsoft.SqlServer.ReportingServices2005.ReportingService2005.GetItemType(String
> Item)
> en
> Microsoft.SqlServer.ReportingServices2005.RSConnection.GetItemType(String
> Item)
>

Publish between servers

Is it possible to publish a report from a DEV reporting server to a
PRODUCTION reporting server?
We would like our developer to deploy to the DEV server and then
someone publish it to production
gertYou can use RSScripter to do this
http://www.sqldbatips.com/showarticle.asp?ID=62
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gert Conradie" <gert.conradie@.gmail.com> wrote in message
news:1125402536.289327.24680@.f14g2000cwb.googlegroups.com...
> Is it possible to publish a report from a DEV reporting server to a
> PRODUCTION reporting server?
> We would like our developer to deploy to the DEV server and then
> someone publish it to production
> gert
>|||Perfect, really really handy!
Wish list: Specify destination server so that one dont have to run the
bat file in two step process. But still as it is - really hande.
Thanks.
gert
Jasper Smith wrote:
> You can use RSScripter to do this
> http://www.sqldbatips.com/showarticle.asp?ID=62
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Gert Conradie" <gert.conradie@.gmail.com> wrote in message
> news:1125402536.289327.24680@.f14g2000cwb.googlegroups.com...
> > Is it possible to publish a report from a DEV reporting server to a
> > PRODUCTION reporting server?
> >
> > We would like our developer to deploy to the DEV server and then
> > someone publish it to production
> >
> > gert
> >