MS Service Pack frustrations: February 2012

Saturday, February 25, 2012

ms sql 2005 developer edition

is it possible to install ms sql 2005 developer edition databse server
on window XP professional version 2002 with SP2 or just allows install
client version?On Oct 3, 10:39 am, HandersonVA <handerso...@.hotmail.comwrote:

Quote:

Originally Posted by

is it possible to install ms sql 2005 developer edition databse server
on window XP professional version 2002 with SP2 or just allows install
client version?

Of course you can - the only version you can't install on a client
machine is the SQL Server Enterprise Edition!

JC
xSQL Software
http://www.xsqlsoftware.com|||On Oct 3, 12:23 pm, xman <x...@.xsqlsoftware.comwrote:

Quote:

Originally Posted by

On Oct 3, 10:39 am, HandersonVA <handerso...@.hotmail.comwrote:
>

Quote:

Originally Posted by

is it possible to install ms sql 2005 developer edition databse server
on window XP professional version 2002 with SP2 or just allows install
client version?

>
Of course you can - the only version you can't install on a client
machine is the SQL Server Enterprise Edition!
>
JC
xSQL Softwarehttp://www.xsqlsoftware.com

thank you for your comments!
I just tried to install ms sql 2005 developer edition on my home
computer which has win XP professional, but it says I can only install
the client version to connect the database server. It does not allow
me to install whole database server.|||HandersonVA (handersonva@.hotmail.com) writes:

Quote:

Originally Posted by

I just tried to install ms sql 2005 developer edition on my home
computer which has win XP professional, but it says I can only install
the client version to connect the database server. It does not allow
me to install whole database server.

Then I would guess you need to look twice on the CD/DVD. It sounds as
if you have a copy of Enterprise Edition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ms sql 2005 delete record problem

I'm facing problem when delete a record in the ms sql 2005 .

The error message i got is stated below:

No rows were deleted

A problem occured attempting to delete row 1304.

Error: .Net Sql Clint Data Provider

Error Message: Possible index corruption detected. Run DBCC CHECKDB.

Correct the errors and atempt to delete the row again or press ESC to cancel the changes.

just certain record i can not delete and face this problem.

i detach the database and attached in the ms sql 2000, and select the same record to delete. there is no problem at all.

i wander is it ms sql 2005 bugs? can anyone tell me how to fix it .. how to to use the DBCC CHECKDB ?

thanks alot....

P/s : if i posted at the wrong section just let me know, I'm beginner in this forum here ...

thanks...

Just like the error message tells you:

Use a SqlCommand to execute "DBCC CHECKDB" or use SQL Management Studio, create a new empty query and execute DBCC CHECKDB

--
SvenC

|||

so i just type DBCC CHECKDB in SQL Management Studio..... amd i got this result

what it means ? i still can not delete the record in the table. can some one tell me how to solve it ? index corruption error....

DBCC results for 'CPMLife'.

Service Broker Msg 9675, State 1: Message Types analyzed: 14.

Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.

Service Broker Msg 9667, State 1: Services analyzed: 3.

Service Broker Msg 9668, State 1: Service Queues analyzed: 3.

Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.

Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.

Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.

DBCC results for 'sys.sysrowsetcolumns'.

There are 2294 rows in 15 pages for object "sys.sysrowsetcolumns".

DBCC results for 'sys.sysrowsets'.

There are 199 rows in 2 pages for object "sys.sysrowsets".

DBCC results for 'sysallocunits'.

There are 217 rows in 3 pages for object "sysallocunits".

DBCC results for 'sys.sysfiles1'.

There are 2 rows in 1 pages for object "sys.sysfiles1".

DBCC results for 'sys.syshobtcolumns'.

There are 2294 rows in 17 pages for object "sys.syshobtcolumns".

DBCC results for 'sys.syshobts'.

There are 199 rows in 2 pages for object "sys.syshobts".

DBCC results for 'sys.sysftinds'.

There are 0 rows in 0 pages for object "sys.sysftinds".

DBCC results for 'sys.sysserefs'.

There are 217 rows in 1 pages for object "sys.sysserefs".

DBCC results for 'sys.sysowners'.

There are 14 rows in 1 pages for object "sys.sysowners".

DBCC results for 'sys.sysprivs'.

There are 156 rows in 1 pages for object "sys.sysprivs".

DBCC results for 'sys.sysschobjs'.

There are 436 rows in 6 pages for object "sys.sysschobjs".

DBCC results for 'sys.syscolpars'.

There are 3114 rows in 33 pages for object "sys.syscolpars".

DBCC results for 'sys.sysnsobjs'.

There are 1 rows in 1 pages for object "sys.sysnsobjs".

DBCC results for 'sys.syscerts'.

There are 0 rows in 0 pages for object "sys.syscerts".

DBCC results for 'sys.sysxprops'.

There are 39 rows in 20 pages for object "sys.sysxprops".

DBCC results for 'sys.sysscalartypes'.

There are 27 rows in 1 pages for object "sys.sysscalartypes".

DBCC results for 'sys.systypedsubobjs'.

There are 0 rows in 0 pages for object "sys.systypedsubobjs".

DBCC results for 'sys.sysidxstats'.

There are 1143 rows in 14 pages for object "sys.sysidxstats".

DBCC results for 'sys.sysiscols'.

There are 2391 rows in 10 pages for object "sys.sysiscols".

DBCC results for 'sys.sysbinobjs'.

There are 23 rows in 1 pages for object "sys.sysbinobjs".

DBCC results for 'sys.sysobjvalues'.

There are 1288 rows in 280 pages for object "sys.sysobjvalues".

DBCC results for 'sys.sysclsobjs'.

There are 14 rows in 1 pages for object "sys.sysclsobjs".

DBCC results for 'sys.sysrowsetrefs'.

There are 0 rows in 0 pages for object "sys.sysrowsetrefs".

DBCC results for 'sys.sysremsvcbinds'.

There are 0 rows in 0 pages for object "sys.sysremsvcbinds".

DBCC results for 'sys.sysxmitqueue'.

There are 0 rows in 0 pages for object "sys.sysxmitqueue".

DBCC results for 'sys.sysrts'.

There are 1 rows in 1 pages for object "sys.sysrts".

DBCC results for 'sys.sysconvgroup'.

There are 0 rows in 0 pages for object "sys.sysconvgroup".

DBCC results for 'sys.sysdesend'.

There are 0 rows in 0 pages for object "sys.sysdesend".

DBCC results for 'sys.sysdercv'.

There are 0 rows in 0 pages for object "sys.sysdercv".

DBCC results for 'sys.syssingleobjrefs'.

There are 133 rows in 1 pages for object "sys.syssingleobjrefs".

DBCC results for 'sys.sysmultiobjrefs'.

There are 1331 rows in 9 pages for object "sys.sysmultiobjrefs".

DBCC results for 'sys.sysdbfiles'.

There are 2 rows in 1 pages for object "sys.sysdbfiles".

DBCC results for 'sys.sysguidrefs'.

There are 0 rows in 0 pages for object "sys.sysguidrefs".

DBCC results for 'sys.sysqnames'.

There are 92 rows in 1 pages for object "sys.sysqnames".

DBCC results for 'sys.sysxmlcomponent'.

There are 93 rows in 1 pages for object "sys.sysxmlcomponent".

DBCC results for 'sys.sysxmlfacet'.

There are 97 rows in 1 pages for object "sys.sysxmlfacet".

DBCC results for 'sys.sysxmlplacement'.

There are 17 rows in 1 pages for object "sys.sysxmlplacement".

DBCC results for 'sys.sysobjkeycrypts'.

There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".

DBCC results for 'sys.sysasymkeys'.

There are 0 rows in 0 pages for object "sys.sysasymkeys".

DBCC results for 'sys.syssqlguides'.

There are 0 rows in 0 pages for object "sys.syssqlguides".

DBCC results for 'sys.sysbinsubobjs'.

There are 0 rows in 0 pages for object "sys.sysbinsubobjs".

DBCC results for 'IvItemStatus'.

There are 7 rows in 1 pages for object "IvItemStatus".

DBCC results for 'PrShfPlan'.

There are 127 rows in 1 pages for object "PrShfPlan".

DBCC results for 'IvLabor'.

There are 1 rows in 1 pages for object "IvLabor".

DBCC results for 'PrShfPlanDtl'.

There are 181 rows in 1 pages for object "PrShfPlanDtl".

DBCC results for 'PlMRNDtl'.

There are 306 rows in 3 pages for object "PlMRNDtl".

DBCC results for 'PlMRN'.

There are 60 rows in 5 pages for object "PlMRN".

DBCC results for 'sys.queue_messages_121767491'.

There are 0 rows in 0 pages for object "sys.queue_messages_121767491".

DBCC results for 'IvClass'.

There are 18 rows in 1 pages for object "IvClass".

DBCC results for 'IvMthEnd'.

There are 46731 rows in 1252 pages for object "IvMthEnd".

DBCC results for 'sys.queue_messages_153767605'.

There are 0 rows in 0 pages for object "sys.queue_messages_153767605".

DBCC results for 'IvTrxType'.

There are 0 rows in 0 pages for object "IvTrxType".

DBCC results for 'sys.queue_messages_185767719'.

There are 0 rows in 0 pages for object "sys.queue_messages_185767719".

DBCC results for 'IvMachine'.

There are 10 rows in 1 pages for object "IvMachine".

DBCC results for 'PlSchProcessHis'.

There are 0 rows in 0 pages for object "PlSchProcessHis".

DBCC results for 'PlMRP'.

There are 5890 rows in 353 pages for object "PlMRP".

DBCC results for 'PrShift'.

There are 9 rows in 1 pages for object "PrShift".

DBCC results for 'PlMRPMsg'.

There are 58 rows in 8 pages for object "PlMRPMsg".

DBCC results for 'IvICust'.

There are 763 rows in 20 pages for object "IvICust".

DBCC results for 'ExportLog'.

There are 21010 rows in 577 pages for object "ExportLog".

DBCC results for 'PlSchProcess'.

There are 3041 rows in 73 pages for object "PlSchProcess".

DBCC results for 'PlSchDetailHis'.

There are 0 rows in 0 pages for object "PlSchDetailHis".

DBCC results for 'PlSchHis'.

There are 0 rows in 0 pages for object "PlSchHis".

DBCC results for 'PlCRP'.

There are 4877 rows in 132 pages for object "PlCRP".

DBCC results for 'IvTrxBatchPSize'.

There are 1300 rows in 117 pages for object "IvTrxBatchPSize".

DBCC results for 'PlSchDetail'.

There are 9644 rows in 233 pages for object "PlSchDetail".

DBCC results for 'IvIBalPSize'.

There are 2021 rows in 68 pages for object "IvIBalPSize".

DBCC results for 'PlMRPDtl'.

There are 12848 rows in 374 pages for object "PlMRPDtl".

DBCC results for 'SaInvoiceDtl'.

There are 1482 rows in 45 pages for object "SaInvoiceDtl".

DBCC results for 'PrDate'.

There are 0 rows in 0 pages for object "PrDate".

DBCC results for 'IvTrxHisPSize'.

There are 143966 rows in 3351 pages for object "IvTrxHisPSize".

DBCC results for 'PrDefAttachment'.

There are 0 rows in 0 pages for object "PrDefAttachment".

DBCC results for 'PrDefDtl'.

There are 7986 rows in 297 pages for object "PrDefDtl".

DBCC results for 'PlFloor'.

There are 1201 rows in 32 pages for object "PlFloor".

DBCC results for 'IvSubClass'.

There are 81 rows in 3 pages for object "IvSubClass".

DBCC results for 'PrDailyPrdDefect'.

There are 4 rows in 1 pages for object "PrDailyPrdDefect".

DBCC results for 'PODetail'.

There are 8261 rows in 237 pages for object "PODetail".

DBCC results for 'PrDefType'.

There are 1 rows in 1 pages for object "PrDefType".

DBCC results for 'IvMachineDtl'.

There are 10 rows in 1 pages for object "IvMachineDtl".

DBCC results for 'PrPreventive'.

There are 0 rows in 1 pages for object "PrPreventive".

DBCC results for 'PrSemiFGRecover'.

There are 0 rows in 0 pages for object "PrSemiFGRecover".

DBCC results for 'PrShfCal'.

There are 675 rows in 8 pages for object "PrShfCal".

DBCC results for 'ROPathList'.

There are 1 rows in 1 pages for object "ROPathList".

DBCC results for 'PlForecast'.

There are 282 rows in 17 pages for object "PlForecast".

DBCC results for 'POOrder'.

There are 2789 rows in 112 pages for object "POOrder".

DBCC results for 'PrShiftGrp'.

There are 28 rows in 1 pages for object "PrShiftGrp".

DBCC results for 'PrWrkCtr'.

There are 8 rows in 1 pages for object "PrWrkCtr".

DBCC results for 'PrDailyPrd'.

There are 19 rows in 1 pages for object "PrDailyPrd".

DBCC results for 'ROImportLog'.

There are 0 rows in 0 pages for object "ROImportLog".

DBCC results for 'ExportInvToOSK'.

There are 35 rows in 1 pages for object "ExportInvToOSK".

DBCC results for 'ROTemp'.

There are 3067 rows in 104 pages for object "ROTemp".

DBCC results for 'IvCyCnt'.

There are 25083 rows in 880 pages for object "IvCyCnt".

DBCC results for 'CoActFreightOutwards'.

There are 1 rows in 1 pages for object "CoActFreightOutwards".

DBCC results for 'CoIvMthEndWIP'.

There are 1 rows in 1 pages for object "CoIvMthEndWIP".

DBCC results for 'SmBuyer'.

There are 3 rows in 1 pages for object "SmBuyer".

DBCC results for 'CoLaborCostAct'.

There are 2 rows in 1 pages for object "CoLaborCostAct".

DBCC results for 'SmCalendar'.

There are 913 rows in 7 pages for object "SmCalendar".

DBCC results for 'PlSch'.

There are 805 rows in 31 pages for object "PlSch".

DBCC results for 'CYReconIvTrxHis'.

There are 1685 rows in 55 pages for object "CYReconIvTrxHis".

DBCC results for 'CoOverhead'.

There are 7 rows in 1 pages for object "CoOverhead".

DBCC results for 'SmCompany'.

There are 1 rows in 1 pages for object "SmCompany".

DBCC results for 'IMSVariance'.

There are 1388 rows in 19 pages for object "IMSVariance".

DBCC results for 'CoOverheadAct'.

There are 12 rows in 1 pages for object "CoOverheadAct".

DBCC results for 'SmCountry'.

There are 18 rows in 1 pages for object "SmCountry".

DBCC results for 'SmCur'.

There are 8 rows in 1 pages for object "SmCur".

DBCC results for 'IvIVendor'.

There are 613 rows in 21 pages for object "IvIVendor".

DBCC results for 'CoPrDefDtlCSim'.

There are 70 rows in 2 pages for object "CoPrDefDtlCSim".

DBCC results for 'SmDept'.

There are 12 rows in 1 pages for object "SmDept".

DBCC results for 'CPMDelSch'.

There are 154 rows in 8 pages for object "CPMDelSch".

DBCC results for 'SmRegister'.

There are 69521 rows in 1040 pages for object "SmRegister".

DBCC results for 'SaCDNDtl'.

There are 0 rows in 0 pages for object "SaCDNDtl".

DBCC results for 'CoPrDefProcessCSim'.

There are 17 rows in 1 pages for object "CoPrDefProcessCSim".

DBCC results for 'SmGroup'.

There are 6 rows in 1 pages for object "SmGroup".

DBCC results for 'SaCDNHdr'.

There are 0 rows in 0 pages for object "SaCDNHdr".

DBCC results for 'CoStdFreightOutwards'.

There are 3 rows in 1 pages for object "CoStdFreightOutwards".

DBCC results for 'IvMthEndStdCost'.

There are 1455 rows in 10 pages for object "IvMthEndStdCost".

DBCC results for 'SaInvoiceHdr'.

There are 849 rows in 66 pages for object "SaInvoiceHdr".

DBCC results for 'SmNumCuz'.

There are 64 rows in 6 pages for object "SmNumCuz".

DBCC results for 'SaPayTerms'.

There are 0 rows in 0 pages for object "SaPayTerms".

DBCC results for 'SaTraderTax'.

There are 21 rows in 3 pages for object "SaTraderTax".

DBCC results for 'SmPeriod'.

There are 48 rows in 1 pages for object "SmPeriod".

DBCC results for 'SmCurrExRate'.

There are 15 rows in 3 pages for object "SmCurrExRate".

DBCC results for 'PrDefMas'.

There are 561 rows in 19 pages for object "PrDefMas".

DBCC results for 'SmReason'.

There are 55 rows in 1 pages for object "SmReason".

DBCC results for 'SmCurrRate'.

There are 23 rows in 2 pages for object "SmCurrRate".

DBCC results for 'SmSaRep'.

There are 0 rows in 0 pages for object "SmSaRep".

DBCC results for 'SmTraderContact'.

There are 351 rows in 8 pages for object "SmTraderContact".

DBCC results for 'SmTaxCat'.

There are 1 rows in 1 pages for object "SmTaxCat".

DBCC results for 'SmUOM'.

There are 18 rows in 1 pages for object "SmUOM".

DBCC results for 'SmTaxGrp'.

There are 1 rows in 1 pages for object "SmTaxGrp".

DBCC results for 'SmUOMConv'.

There are 1 rows in 1 pages for object "SmUOMConv".

DBCC results for 'SmTraderSub'.

There are 83 rows in 1 pages for object "SmTraderSub".

DBCC results for 'SmUser'.

There are 19 rows in 2 pages for object "SmUser".

DBCC results for 'IvItemTax'.

There are 0 rows in 0 pages for object "IvItemTax".

DBCC results for 'SmWHouse'.

There are 15 rows in 1 pages for object "SmWHouse".

DBCC results for 'POPR'.

There are 0 rows in 0 pages for object "POPR".

DBCC results for 'POPRDtl'.

There are 0 rows in 0 pages for object "POPRDtl".

DBCC results for 'POQuote'.

There are 4 rows in 1 pages for object "POQuote".

DBCC results for 'POQuoteDtl'.

There are 10 rows in 1 pages for object "POQuoteDtl".

DBCC results for 'SmMthBudget'.

There are 9 rows in 2 pages for object "SmMthBudget".

DBCC results for 'SaOrder'.

There are 2484 rows in 85 pages for object "SaOrder".

DBCC results for 'SaOrderDtl'.

There are 5086 rows in 139 pages for object "SaOrderDtl".

DBCC results for 'dtproperties'.

There are 0 rows in 0 pages for object "dtproperties".

DBCC results for 'SmDocNo'.

There are 128 rows in 2 pages for object "SmDocNo".

DBCC results for 'SmTrader'.

There are 309 rows in 30 pages for object "SmTrader".

DBCC results for 'CYReconIMS'.

There are 1084 rows in 28 pages for object "CYReconIMS".

DBCC results for 'SmPara'.

There are 1 rows in 1 pages for object "SmPara".

DBCC results for 'SmMenu'.

There are 231 rows in 3 pages for object "SmMenu".

DBCC results for 'SmGrouprights'.

There are 790 rows in 6 pages for object "SmGrouprights".

DBCC results for 'IvTrxBatchHdr'.

There are 273 rows in 6 pages for object "IvTrxBatchHdr".

DBCC results for 'CoPrDefMasCSim'.

There are 5 rows in 1 pages for object "CoPrDefMasCSim".

DBCC results for 'IvTrxBatch'.

There are 1550 rows in 99 pages for object "IvTrxBatch".

DBCC results for 'IvTrxHis'.

There are 175814 rows in 6613 pages for object "IvTrxHis".

DBCC results for 'CoOverheadCSim'.

There are 35 rows in 1 pages for object "CoOverheadCSim".

DBCC results for 'PrDailyPrdMachine'.

There are 13 rows in 1 pages for object "PrDailyPrdMachine".

DBCC results for 'PrProcess'.

There are 8 rows in 1 pages for object "PrProcess".

DBCC results for 'PrDefProcess'.

There are 2040 rows in 58 pages for object "PrDefProcess".

DBCC results for 'CoSalesInvoice'.

There are 0 rows in 0 pages for object "CoSalesInvoice".

DBCC results for 'SmNum'.

There are 19 rows in 2 pages for object "SmNum".

DBCC results for 'IvIMas'.

There are 1455 rows in 130 pages for object "IvIMas".

DBCC results for 'IvCyCntPSize'.

There are 26708 rows in 640 pages for object "IvCyCntPSize".

DBCC results for 'IvIBal'.

There are 2390 rows in 68 pages for object "IvIBal".

DBCC results for 'IvILoc'.

There are 1647 rows in 85 pages for object "IvILoc".

DBCC results for 'IvILocMas'.

There are 1097 rows in 18 pages for object "IvILocMas".

DBCC results for 'IvISubstitute'.

There are 0 rows in 0 pages for object "IvISubstitute".

CHECKDB found 0 allocation errors and 0 consistency errors in database 'CPMLife'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

i found some solution from other thread.

first i set the database in single user mode.

ALTER DATABASE CPMLife SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then i run the code below to fix the database.

DBCC CHECKDB
(
CPMLife ,
REPAIR_ALLOW_DATA_LOSS
)

but i still get the same error message which is not allow me to delete the record in the table.

|||

Try rebuilding the indexes on the table.

DBCC DBReIndex ('TableName')

|||

hi,

i have run the code you ask me to try which is

DBCC DBReIndex ('IvMthEnd')

Msg 9100, Level 23, State 2, Line 1

Possible index corruption detected. Run DBCC CHECKDB.

then i go to ru nthe DBCC CHECKDB again. and the result is no error

CHECKDB found 0 allocation errors and 0 consistency errors in database 'CPMLife'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I still can not delete the record and get the same error ... anything else i can do ?

|||You have 2 options:
1. restore from backup.
2. find the offending index(es) and delete/recreate - sp_helpindex should give you a list.|||

i do not have backup version of the database..

so i would like to go to option 2, but how to find the offending index(es) record ? to delete or recreate ?

and guide me more to solve it ?

thanks

|||execute 'sp_helpindex table_name' to get the list of the indexes. Make note of the result before you drop the indexes. To drop the index, execute "drop index".

http://msdn2.microsoft.com/en-us/library/ms188771.aspx
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
http://msdn2.microsoft.com/en-us/library/ms176118.aspx

You can post the result here if you need further help.

Actually, before you start anything, backup your database.

BACKUP DATABASE [your db name]
TO DISK='c:\mydb.bak'

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

i run

sp_helpindex N'IvMthEnd'

then i got the result as below

index name PK_IvMthEnd

index_description nonclustered located on PRIMARY

index_keys M_Period, M_Year, I_Cd, WHouse_Cd

then i drop the index with the code below

DROP INDEX PK_IvMthEnd

ON IvMthEnd;

Command(s) completed successfully.

then i create the index again with the code below

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

CREATE INDEX PK_IvMthEnd

ON IvMthEnd (M_Period, M_Year, I_Cd, WHouse_Cd);

Command(s) completed successfully.

i still face the same problem, can not delete the record and get the same result possible index corrupted

i run the DBCC CHECKDB , no error message show.

|||Ah. You will want to drop the statistics as well.

exec sp_helpstats 'IvMthEnd'
go
drop statistics IvMthEnd.<statname>
go

|||

erm..

After i run exec sp_helpstats 'IvMthEnd'

i get the result like below

Statistics Name

_WA_Sys_Adj_Qty_0880433F

_WA_Sys_Avg_Iv_Cost_0880433F

_WA_Sys_Close_Bal_0880433F

_WA_Sys_I_Cd_0880433F

_WA_Sys_Issue_Qty_0880433F

_WA_Sys_Last_Update_0880433F

_WA_Sys_M_Year_0880433F

_WA_Sys_Open_Bal_0880433F

_WA_Sys_Receipt_Qty_0880433F

_WA_Sys_UOM_0880433F

_WA_Sys_User_ID_0880433F

_WA_Sys_WHouse_Cd_0880433F

then i go for

DROP INDEX PK_IvMthEnd

ON IvMthEnd;

then i create the index again

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

CREATE INDEX PK_IvMthEnd

ON IvMthEnd (M_Period, M_Year, I_Cd, WHouse_Cd);

But i still facing the same problem.... i still can not delete the record in the SQL server management studio.

P/S : i would like to ask something about the command

sp_helpindex N'IvMthEnd'

What's the different between

clustered, unique, primary key located on PRIMARY and nonclustered located on PRIMARY

|||You need to run the "drop statistic lvMthEnd._WA_Sys_Adj_Qty_0880433F" etc.

Dropping the index is not the same as dropping the stats.

clustered, unique, primary key on PRIMARY means you've created a clustered index on the key which resides on the primary filegroup. You can lookup for "clustered" and "nonclustered" in book online for more info.

|||

i have done drop the statistic and drop the index also ...

but still the same ...

so if i want to create back the clustered, unique, primary key , how to do it.

thanks.. ..

|||To create the clustered primary key, you can do something like this.

alter table <tb_name>
add constraint <pk_name> primary key clustered (<col1, 2,...n>)

Now to your problem, something doesn't jive here. So, you deleted all indexes and statistics on the table and you still got the "index corruption" warning?

Perhaps, this is a case for MS support to closely work with you to track down the root cause.

ms sql 2005 delete record problem

I'm facing problem when delete a record in the ms sql 2005 .

The error message i got is stated below:

No rows were deleted

A problem occured attempting to delete row 1304.

Error: .Net Sql Clint Data Provider

Error Message: Possible index corruption detected. Run DBCC CHECKDB.

Correct the errors and atempt to delete the row again or press ESC to cancel the changes.

just certain record i can not delete and face this problem.

i detach the database and attached in the ms sql 2000, and select the same record to delete. there is no problem at all.

i wander is it ms sql 2005 bugs? can anyone tell me how to fix it .. how to to use the DBCC CHECKDB ?

thanks alot....

P/s : if i posted at the wrong section just let me know, I'm beginner in this forum here ...

thanks...

Just like the error message tells you:

Use a SqlCommand to execute "DBCC CHECKDB" or use SQL Management Studio, create a new empty query and execute DBCC CHECKDB

--
SvenC

|||

so i just type DBCC CHECKDB in SQL Management Studio..... amd i got this result

what it means ? i still can not delete the record in the table. can some one tell me how to solve it ? index corruption error....

DBCC results for 'CPMLife'.

Service Broker Msg 9675, State 1: Message Types analyzed: 14.

Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.

Service Broker Msg 9667, State 1: Services analyzed: 3.

Service Broker Msg 9668, State 1: Service Queues analyzed: 3.

Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.

Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.

Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.

DBCC results for 'sys.sysrowsetcolumns'.

There are 2294 rows in 15 pages for object "sys.sysrowsetcolumns".

DBCC results for 'sys.sysrowsets'.

There are 199 rows in 2 pages for object "sys.sysrowsets".

DBCC results for 'sysallocunits'.

There are 217 rows in 3 pages for object "sysallocunits".

DBCC results for 'sys.sysfiles1'.

There are 2 rows in 1 pages for object "sys.sysfiles1".

DBCC results for 'sys.syshobtcolumns'.

There are 2294 rows in 17 pages for object "sys.syshobtcolumns".

DBCC results for 'sys.syshobts'.

There are 199 rows in 2 pages for object "sys.syshobts".

DBCC results for 'sys.sysftinds'.

There are 0 rows in 0 pages for object "sys.sysftinds".

DBCC results for 'sys.sysserefs'.

There are 217 rows in 1 pages for object "sys.sysserefs".

DBCC results for 'sys.sysowners'.

There are 14 rows in 1 pages for object "sys.sysowners".

DBCC results for 'sys.sysprivs'.

There are 156 rows in 1 pages for object "sys.sysprivs".

DBCC results for 'sys.sysschobjs'.

There are 436 rows in 6 pages for object "sys.sysschobjs".

DBCC results for 'sys.syscolpars'.

There are 3114 rows in 33 pages for object "sys.syscolpars".

DBCC results for 'sys.sysnsobjs'.

There are 1 rows in 1 pages for object "sys.sysnsobjs".

DBCC results for 'sys.syscerts'.

There are 0 rows in 0 pages for object "sys.syscerts".

DBCC results for 'sys.sysxprops'.

There are 39 rows in 20 pages for object "sys.sysxprops".

DBCC results for 'sys.sysscalartypes'.

There are 27 rows in 1 pages for object "sys.sysscalartypes".

DBCC results for 'sys.systypedsubobjs'.

There are 0 rows in 0 pages for object "sys.systypedsubobjs".

DBCC results for 'sys.sysidxstats'.

There are 1143 rows in 14 pages for object "sys.sysidxstats".

DBCC results for 'sys.sysiscols'.

There are 2391 rows in 10 pages for object "sys.sysiscols".

DBCC results for 'sys.sysbinobjs'.

There are 23 rows in 1 pages for object "sys.sysbinobjs".

DBCC results for 'sys.sysobjvalues'.

There are 1288 rows in 280 pages for object "sys.sysobjvalues".

DBCC results for 'sys.sysclsobjs'.

There are 14 rows in 1 pages for object "sys.sysclsobjs".

DBCC results for 'sys.sysrowsetrefs'.

There are 0 rows in 0 pages for object "sys.sysrowsetrefs".

DBCC results for 'sys.sysremsvcbinds'.

There are 0 rows in 0 pages for object "sys.sysremsvcbinds".

DBCC results for 'sys.sysxmitqueue'.

There are 0 rows in 0 pages for object "sys.sysxmitqueue".

DBCC results for 'sys.sysrts'.

There are 1 rows in 1 pages for object "sys.sysrts".

DBCC results for 'sys.sysconvgroup'.

There are 0 rows in 0 pages for object "sys.sysconvgroup".

DBCC results for 'sys.sysdesend'.

There are 0 rows in 0 pages for object "sys.sysdesend".

DBCC results for 'sys.sysdercv'.

There are 0 rows in 0 pages for object "sys.sysdercv".

DBCC results for 'sys.syssingleobjrefs'.

There are 133 rows in 1 pages for object "sys.syssingleobjrefs".

DBCC results for 'sys.sysmultiobjrefs'.

There are 1331 rows in 9 pages for object "sys.sysmultiobjrefs".

DBCC results for 'sys.sysdbfiles'.

There are 2 rows in 1 pages for object "sys.sysdbfiles".

DBCC results for 'sys.sysguidrefs'.

There are 0 rows in 0 pages for object "sys.sysguidrefs".

DBCC results for 'sys.sysqnames'.

There are 92 rows in 1 pages for object "sys.sysqnames".

DBCC results for 'sys.sysxmlcomponent'.

There are 93 rows in 1 pages for object "sys.sysxmlcomponent".

DBCC results for 'sys.sysxmlfacet'.

There are 97 rows in 1 pages for object "sys.sysxmlfacet".

DBCC results for 'sys.sysxmlplacement'.

There are 17 rows in 1 pages for object "sys.sysxmlplacement".

DBCC results for 'sys.sysobjkeycrypts'.

There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".

DBCC results for 'sys.sysasymkeys'.

There are 0 rows in 0 pages for object "sys.sysasymkeys".

DBCC results for 'sys.syssqlguides'.

There are 0 rows in 0 pages for object "sys.syssqlguides".

DBCC results for 'sys.sysbinsubobjs'.

There are 0 rows in 0 pages for object "sys.sysbinsubobjs".

DBCC results for 'IvItemStatus'.

There are 7 rows in 1 pages for object "IvItemStatus".

DBCC results for 'PrShfPlan'.

There are 127 rows in 1 pages for object "PrShfPlan".

DBCC results for 'IvLabor'.

There are 1 rows in 1 pages for object "IvLabor".

DBCC results for 'PrShfPlanDtl'.

There are 181 rows in 1 pages for object "PrShfPlanDtl".

DBCC results for 'PlMRNDtl'.

There are 306 rows in 3 pages for object "PlMRNDtl".

DBCC results for 'PlMRN'.

There are 60 rows in 5 pages for object "PlMRN".

DBCC results for 'sys.queue_messages_121767491'.

There are 0 rows in 0 pages for object "sys.queue_messages_121767491".

DBCC results for 'IvClass'.

There are 18 rows in 1 pages for object "IvClass".

DBCC results for 'IvMthEnd'.

There are 46731 rows in 1252 pages for object "IvMthEnd".

DBCC results for 'sys.queue_messages_153767605'.

There are 0 rows in 0 pages for object "sys.queue_messages_153767605".

DBCC results for 'IvTrxType'.

There are 0 rows in 0 pages for object "IvTrxType".

DBCC results for 'sys.queue_messages_185767719'.

There are 0 rows in 0 pages for object "sys.queue_messages_185767719".

DBCC results for 'IvMachine'.

There are 10 rows in 1 pages for object "IvMachine".

DBCC results for 'PlSchProcessHis'.

There are 0 rows in 0 pages for object "PlSchProcessHis".

DBCC results for 'PlMRP'.

There are 5890 rows in 353 pages for object "PlMRP".

DBCC results for 'PrShift'.

There are 9 rows in 1 pages for object "PrShift".

DBCC results for 'PlMRPMsg'.

There are 58 rows in 8 pages for object "PlMRPMsg".

DBCC results for 'IvICust'.

There are 763 rows in 20 pages for object "IvICust".

DBCC results for 'ExportLog'.

There are 21010 rows in 577 pages for object "ExportLog".

DBCC results for 'PlSchProcess'.

There are 3041 rows in 73 pages for object "PlSchProcess".

DBCC results for 'PlSchDetailHis'.

There are 0 rows in 0 pages for object "PlSchDetailHis".

DBCC results for 'PlSchHis'.

There are 0 rows in 0 pages for object "PlSchHis".

DBCC results for 'PlCRP'.

There are 4877 rows in 132 pages for object "PlCRP".

DBCC results for 'IvTrxBatchPSize'.

There are 1300 rows in 117 pages for object "IvTrxBatchPSize".

DBCC results for 'PlSchDetail'.

There are 9644 rows in 233 pages for object "PlSchDetail".

DBCC results for 'IvIBalPSize'.

There are 2021 rows in 68 pages for object "IvIBalPSize".

DBCC results for 'PlMRPDtl'.

There are 12848 rows in 374 pages for object "PlMRPDtl".

DBCC results for 'SaInvoiceDtl'.

There are 1482 rows in 45 pages for object "SaInvoiceDtl".

DBCC results for 'PrDate'.

There are 0 rows in 0 pages for object "PrDate".

DBCC results for 'IvTrxHisPSize'.

There are 143966 rows in 3351 pages for object "IvTrxHisPSize".

DBCC results for 'PrDefAttachment'.

There are 0 rows in 0 pages for object "PrDefAttachment".

DBCC results for 'PrDefDtl'.

There are 7986 rows in 297 pages for object "PrDefDtl".

DBCC results for 'PlFloor'.

There are 1201 rows in 32 pages for object "PlFloor".

DBCC results for 'IvSubClass'.

There are 81 rows in 3 pages for object "IvSubClass".

DBCC results for 'PrDailyPrdDefect'.

There are 4 rows in 1 pages for object "PrDailyPrdDefect".

DBCC results for 'PODetail'.

There are 8261 rows in 237 pages for object "PODetail".

DBCC results for 'PrDefType'.

There are 1 rows in 1 pages for object "PrDefType".

DBCC results for 'IvMachineDtl'.

There are 10 rows in 1 pages for object "IvMachineDtl".

DBCC results for 'PrPreventive'.

There are 0 rows in 1 pages for object "PrPreventive".

DBCC results for 'PrSemiFGRecover'.

There are 0 rows in 0 pages for object "PrSemiFGRecover".

DBCC results for 'PrShfCal'.

There are 675 rows in 8 pages for object "PrShfCal".

DBCC results for 'ROPathList'.

There are 1 rows in 1 pages for object "ROPathList".

DBCC results for 'PlForecast'.

There are 282 rows in 17 pages for object "PlForecast".

DBCC results for 'POOrder'.

There are 2789 rows in 112 pages for object "POOrder".

DBCC results for 'PrShiftGrp'.

There are 28 rows in 1 pages for object "PrShiftGrp".

DBCC results for 'PrWrkCtr'.

There are 8 rows in 1 pages for object "PrWrkCtr".

DBCC results for 'PrDailyPrd'.

There are 19 rows in 1 pages for object "PrDailyPrd".

DBCC results for 'ROImportLog'.

There are 0 rows in 0 pages for object "ROImportLog".

DBCC results for 'ExportInvToOSK'.

There are 35 rows in 1 pages for object "ExportInvToOSK".

DBCC results for 'ROTemp'.

There are 3067 rows in 104 pages for object "ROTemp".

DBCC results for 'IvCyCnt'.

There are 25083 rows in 880 pages for object "IvCyCnt".

DBCC results for 'CoActFreightOutwards'.

There are 1 rows in 1 pages for object "CoActFreightOutwards".

DBCC results for 'CoIvMthEndWIP'.

There are 1 rows in 1 pages for object "CoIvMthEndWIP".

DBCC results for 'SmBuyer'.

There are 3 rows in 1 pages for object "SmBuyer".

DBCC results for 'CoLaborCostAct'.

There are 2 rows in 1 pages for object "CoLaborCostAct".

DBCC results for 'SmCalendar'.

There are 913 rows in 7 pages for object "SmCalendar".

DBCC results for 'PlSch'.

There are 805 rows in 31 pages for object "PlSch".

DBCC results for 'CYReconIvTrxHis'.

There are 1685 rows in 55 pages for object "CYReconIvTrxHis".

DBCC results for 'CoOverhead'.

There are 7 rows in 1 pages for object "CoOverhead".

DBCC results for 'SmCompany'.

There are 1 rows in 1 pages for object "SmCompany".

DBCC results for 'IMSVariance'.

There are 1388 rows in 19 pages for object "IMSVariance".

DBCC results for 'CoOverheadAct'.

There are 12 rows in 1 pages for object "CoOverheadAct".

DBCC results for 'SmCountry'.

There are 18 rows in 1 pages for object "SmCountry".

DBCC results for 'SmCur'.

There are 8 rows in 1 pages for object "SmCur".

DBCC results for 'IvIVendor'.

There are 613 rows in 21 pages for object "IvIVendor".

DBCC results for 'CoPrDefDtlCSim'.

There are 70 rows in 2 pages for object "CoPrDefDtlCSim".

DBCC results for 'SmDept'.

There are 12 rows in 1 pages for object "SmDept".

DBCC results for 'CPMDelSch'.

There are 154 rows in 8 pages for object "CPMDelSch".

DBCC results for 'SmRegister'.

There are 69521 rows in 1040 pages for object "SmRegister".

DBCC results for 'SaCDNDtl'.

There are 0 rows in 0 pages for object "SaCDNDtl".

DBCC results for 'CoPrDefProcessCSim'.

There are 17 rows in 1 pages for object "CoPrDefProcessCSim".

DBCC results for 'SmGroup'.

There are 6 rows in 1 pages for object "SmGroup".

DBCC results for 'SaCDNHdr'.

There are 0 rows in 0 pages for object "SaCDNHdr".

DBCC results for 'CoStdFreightOutwards'.

There are 3 rows in 1 pages for object "CoStdFreightOutwards".

DBCC results for 'IvMthEndStdCost'.

There are 1455 rows in 10 pages for object "IvMthEndStdCost".

DBCC results for 'SaInvoiceHdr'.

There are 849 rows in 66 pages for object "SaInvoiceHdr".

DBCC results for 'SmNumCuz'.

There are 64 rows in 6 pages for object "SmNumCuz".

DBCC results for 'SaPayTerms'.

There are 0 rows in 0 pages for object "SaPayTerms".

DBCC results for 'SaTraderTax'.

There are 21 rows in 3 pages for object "SaTraderTax".

DBCC results for 'SmPeriod'.

There are 48 rows in 1 pages for object "SmPeriod".

DBCC results for 'SmCurrExRate'.

There are 15 rows in 3 pages for object "SmCurrExRate".

DBCC results for 'PrDefMas'.

There are 561 rows in 19 pages for object "PrDefMas".

DBCC results for 'SmReason'.

There are 55 rows in 1 pages for object "SmReason".

DBCC results for 'SmCurrRate'.

There are 23 rows in 2 pages for object "SmCurrRate".

DBCC results for 'SmSaRep'.

There are 0 rows in 0 pages for object "SmSaRep".

DBCC results for 'SmTraderContact'.

There are 351 rows in 8 pages for object "SmTraderContact".

DBCC results for 'SmTaxCat'.

There are 1 rows in 1 pages for object "SmTaxCat".

DBCC results for 'SmUOM'.

There are 18 rows in 1 pages for object "SmUOM".

DBCC results for 'SmTaxGrp'.

There are 1 rows in 1 pages for object "SmTaxGrp".

DBCC results for 'SmUOMConv'.

There are 1 rows in 1 pages for object "SmUOMConv".

DBCC results for 'SmTraderSub'.

There are 83 rows in 1 pages for object "SmTraderSub".

DBCC results for 'SmUser'.

There are 19 rows in 2 pages for object "SmUser".

DBCC results for 'IvItemTax'.

There are 0 rows in 0 pages for object "IvItemTax".

DBCC results for 'SmWHouse'.

There are 15 rows in 1 pages for object "SmWHouse".

DBCC results for 'POPR'.

There are 0 rows in 0 pages for object "POPR".

DBCC results for 'POPRDtl'.

There are 0 rows in 0 pages for object "POPRDtl".

DBCC results for 'POQuote'.

There are 4 rows in 1 pages for object "POQuote".

DBCC results for 'POQuoteDtl'.

There are 10 rows in 1 pages for object "POQuoteDtl".

DBCC results for 'SmMthBudget'.

There are 9 rows in 2 pages for object "SmMthBudget".

DBCC results for 'SaOrder'.

There are 2484 rows in 85 pages for object "SaOrder".

DBCC results for 'SaOrderDtl'.

There are 5086 rows in 139 pages for object "SaOrderDtl".

DBCC results for 'dtproperties'.

There are 0 rows in 0 pages for object "dtproperties".

DBCC results for 'SmDocNo'.

There are 128 rows in 2 pages for object "SmDocNo".

DBCC results for 'SmTrader'.

There are 309 rows in 30 pages for object "SmTrader".

DBCC results for 'CYReconIMS'.

There are 1084 rows in 28 pages for object "CYReconIMS".

DBCC results for 'SmPara'.

There are 1 rows in 1 pages for object "SmPara".

DBCC results for 'SmMenu'.

There are 231 rows in 3 pages for object "SmMenu".

DBCC results for 'SmGrouprights'.

There are 790 rows in 6 pages for object "SmGrouprights".

DBCC results for 'IvTrxBatchHdr'.

There are 273 rows in 6 pages for object "IvTrxBatchHdr".

DBCC results for 'CoPrDefMasCSim'.

There are 5 rows in 1 pages for object "CoPrDefMasCSim".

DBCC results for 'IvTrxBatch'.

There are 1550 rows in 99 pages for object "IvTrxBatch".

DBCC results for 'IvTrxHis'.

There are 175814 rows in 6613 pages for object "IvTrxHis".

DBCC results for 'CoOverheadCSim'.

There are 35 rows in 1 pages for object "CoOverheadCSim".

DBCC results for 'PrDailyPrdMachine'.

There are 13 rows in 1 pages for object "PrDailyPrdMachine".

DBCC results for 'PrProcess'.

There are 8 rows in 1 pages for object "PrProcess".

DBCC results for 'PrDefProcess'.

There are 2040 rows in 58 pages for object "PrDefProcess".

DBCC results for 'CoSalesInvoice'.

There are 0 rows in 0 pages for object "CoSalesInvoice".

DBCC results for 'SmNum'.

There are 19 rows in 2 pages for object "SmNum".

DBCC results for 'IvIMas'.

There are 1455 rows in 130 pages for object "IvIMas".

DBCC results for 'IvCyCntPSize'.

There are 26708 rows in 640 pages for object "IvCyCntPSize".

DBCC results for 'IvIBal'.

There are 2390 rows in 68 pages for object "IvIBal".

DBCC results for 'IvILoc'.

There are 1647 rows in 85 pages for object "IvILoc".

DBCC results for 'IvILocMas'.

There are 1097 rows in 18 pages for object "IvILocMas".

DBCC results for 'IvISubstitute'.

There are 0 rows in 0 pages for object "IvISubstitute".

CHECKDB found 0 allocation errors and 0 consistency errors in database 'CPMLife'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

|||

i found some solution from other thread.

first i set the database in single user mode.

ALTER DATABASE CPMLife SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then i run the code below to fix the database.

DBCC CHECKDB
(
CPMLife ,
REPAIR_ALLOW_DATA_LOSS
)

but i still get the same error message which is not allow me to delete the record in the table.

|||

Try rebuilding the indexes on the table.

DBCC DBReIndex ('TableName')

|||

hi,

i have run the code you ask me to try which is

DBCC DBReIndex ('IvMthEnd')

Msg 9100, Level 23, State 2, Line 1

Possible index corruption detected. Run DBCC CHECKDB.

then i go to ru nthe DBCC CHECKDB again. and the result is no error

CHECKDB found 0 allocation errors and 0 consistency errors in database 'CPMLife'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I still can not delete the record and get the same error ... anything else i can do ?

|||You have 2 options:
1. restore from backup.
2. find the offending index(es) and delete/recreate - sp_helpindex should give you a list.|||

i do not have backup version of the database..

so i would like to go to option 2, but how to find the offending index(es) record ? to delete or recreate ?

and guide me more to solve it ?

thanks

|||execute 'sp_helpindex table_name' to get the list of the indexes. Make note of the result before you drop the indexes. To drop the index, execute "drop index".

http://msdn2.microsoft.com/en-us/library/ms188771.aspx
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
http://msdn2.microsoft.com/en-us/library/ms176118.aspx

You can post the result here if you need further help.

Actually, before you start anything, backup your database.

BACKUP DATABASE [your db name]
TO DISK='c:\mydb.bak'

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

i run

sp_helpindex N'IvMthEnd'

then i got the result as below

index name PK_IvMthEnd

index_description nonclustered located on PRIMARY

index_keys M_Period, M_Year, I_Cd, WHouse_Cd

then i drop the index with the code below

DROP INDEX PK_IvMthEnd

ON IvMthEnd;

Command(s) completed successfully.

then i create the index again with the code below

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

CREATE INDEX PK_IvMthEnd

ON IvMthEnd (M_Period, M_Year, I_Cd, WHouse_Cd);

Command(s) completed successfully.

i still face the same problem, can not delete the record and get the same result possible index corrupted

i run the DBCC CHECKDB , no error message show.

|||Ah. You will want to drop the statistics as well.

exec sp_helpstats 'IvMthEnd'
go
drop statistics IvMthEnd.<statname>
go

|||

erm..

After i run exec sp_helpstats 'IvMthEnd'

i get the result like below

Statistics Name

_WA_Sys_Adj_Qty_0880433F

_WA_Sys_Avg_Iv_Cost_0880433F

_WA_Sys_Close_Bal_0880433F

_WA_Sys_I_Cd_0880433F

_WA_Sys_Issue_Qty_0880433F

_WA_Sys_Last_Update_0880433F

_WA_Sys_M_Year_0880433F

_WA_Sys_Open_Bal_0880433F

_WA_Sys_Receipt_Qty_0880433F

_WA_Sys_UOM_0880433F

_WA_Sys_User_ID_0880433F

_WA_Sys_WHouse_Cd_0880433F

then i go for

DROP INDEX PK_IvMthEnd

ON IvMthEnd;

then i create the index again

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

CREATE INDEX PK_IvMthEnd

ON IvMthEnd (M_Period, M_Year, I_Cd, WHouse_Cd);

But i still facing the same problem.... i still can not delete the record in the SQL server management studio.

P/S : i would like to ask something about the command

sp_helpindex N'IvMthEnd'

What's the different between

clustered, unique, primary key located on PRIMARY and nonclustered located on PRIMARY

|||You need to run the "drop statistic lvMthEnd._WA_Sys_Adj_Qty_0880433F" etc.

Dropping the index is not the same as dropping the stats.

|||

i have done drop the statistic and drop the index also ...

but still the same ...

so if i want to create back the clustered, unique, primary key , how to do it.

thanks.. ..

|||To create the clustered primary key, you can do something like this.

alter table <tb_name>
add constraint <pk_name> primary key clustered (<col1, 2,...n>)

Now to your problem, something doesn't jive here. So, you deleted all indexes and statistics on the table and you still got the "index corruption" warning?

Perhaps, this is a case for MS support to closely work with you to track down the root cause.

MS SQL 2005 Connection Problems

I have an MS SQL problem,

I have set up Adobe Enterprisewith a MS SQL 2005 database. Although ‘Connect’ recognised the SQL data source on install it now refuses to connect. The SQL log shows that a connection is being attempted every time the System Administrator username and password details are entered into the ‘Connect’ configuration, so details such as host, data source, ports etc are all right.

I assume this must be an authentication problem as I can connect to the data source fine in the MS SQL 2005 management tool, I thought it may be because I have Windows Authentication on rather than mixed mode authentication…but I don’t seem either a) change to mixed mode or b) change the password.

How do I either reset the MS SQL system admin details or access the database outside of the SQL management software to verify that the login details I am putting into Connect are in fact right...this is of course assuming it is an authentication problem.

Any help please….

Wow not one reply, oh well!

In the end it was simply an authentication problem as I thought.

By switching to Mixed Mode authentication (SQL authentication) rather than Windows Authentication (which is automatic and the default) it is possible to login from the Connect Config screen. You must also make sure "user desktop notification" is switched on for Adobe Breeze in the windows admin setting.

If this is done there may well be a problem if the system fails i.e. errors will only appear on the server, I am also unsure of any extra security issues that may prove a problem by switching to mixed mode.

So, it works but if anyone has any better ideas or advice please let me know.

Thanks

MS SQL 2005 Connection Problems

I have an MS SQL problem,

Any help please….

Wow not one reply, oh well!

In the end it was simply an authentication problem as I thought.

So, it works but if anyone has any better ideas or advice please let me know.

Thanks

MS SQL 2005 caching dependancy....

Is it possible to build an MS sql 2005 caching dependancy stucture similar
to what is offered by the SqlCacheDependency class for use with non asp.net
applications using MS sql 2005 notification services and C# ?
Cheers
OllieNotification Services is something different from what you describe. You should read about "Query
Notifications" and the SqlDedendency object, which you can use from ADO.NET 2,0 but doesn't require
ASP in any form or shape.
In fact, to the best of my knowledge, SqlCacheDepenency uses in turn SqlDependency.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <dave@.nospamnoemial.com> wrote in message news:eFlqCSY1GHA.4264@.TK2MSFTNGP05.phx.gbl...
> Is it possible to build an MS sql 2005 caching dependancy stucture similar to what is offered by
> the SqlCacheDependency class for use with non asp.net applications using MS sql 2005 notification
> services and C# ?
> Cheers
> Ollie
>

MS SQL 2005 caching dependancy....

Is it possible to build an MS sql 2005 caching dependancy stucture similar
to what is offered by the SqlCacheDependency class for use with non asp.net
applications using MS sql 2005 notification services and C# ?
Cheers
OllieNotification Services is something different from what you describe. You sho
uld read about "Query
Notifications" and the SqlDedendency object, which you can use from ADO.NET
2,0 but doesn't require
ASP in any form or shape.
In fact, to the best of my knowledge, SqlCacheDepenency uses in turn SqlDepe
ndency.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <dave@.nospamnoemial.com> wrote in message news:eFlqCSY1GHA.4264@.TK2MSFTNGP05.phx.gbl.
.
> Is it possible to build an MS sql 2005 caching dependancy stucture similar
to what is offered by
> the SqlCacheDependency class for use with non asp.net applications using M
S sql 2005 notification
> services and C# ?
> Cheers
> Ollie
>

MS Sql 2005 beta 3 installation errors..Can this version coexist with VS2005 beta 2

When i tried to install the beta 3 above I encounter the following problems ;-

TITLE: Microsoft SQL Server 2005 Beta 3 Setup
------------

Setup configuration check cannot be executed due to
WMI configuration on the computer
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\etwcls.mof.
For details, see the Sqlrunxx.log file or call Microsoft Product Support.

A seach in the net has the followings help link

For help, click:
http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&ProdVer=9.00.852&EvtSrc=setup.rll&EvtID=29535&EvtType=sqlca%5Csqlmofca.cpp@.Do_sqlMofcomp@.Do_sqlMofcomp@.x8007735f

Can this version coexist with VS2005 beta 2 because before i can install visual studio 2005 beta 2 I need to uninstall the dot net framework 2.0 beta which come together with the beta 3 sql 2005 installation.

Hey I link this editor, Where can I get this beautiful forum message editor Stick out tongue [:P] ?.

You do not need to uninstall the .NET version that came with SQL Server2005 April CTP. They are the same version. Actually, MSrecommends installing SQL Server 2005 first, then VS2005.

MS SQL 2005 and PDC Confusion

hello everyone, I am kind of confused with MS SQL 2005. I have friends who have been developing with MS SQL. They told me that in order to have a MS SQL Server, one need to have a PDC.

This is really a problem since I am behind a Unix box acting as a firewall/gateway. AFAIK, to install a PDC means that DHCP comes with it and DNS. This is really a problem...

Is it possible to have a MS SQL server without having to create a PDC? If so, how do I connect to my database on an MS SQL server. I develop in VB. I would appreciate any help/tips... TIA :D

You don′t need a PDC, you will need a domain only if you want to do WIndows authentication along with more than one computer. Otherwise you can use SQL Server authentication.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

MS SQL 2005 + Windows Server 2003 SP2

At first I thought it was just me, but has anyone else noticed that SQL 2005 suffers a performance hit when you install Windows Server 2003 SP2?

Since SP2, memory and hard drive usage has gone through the roof on a number of servers I use. It may just be coincidence... but it seems unlikely.

We are running one of our production clusters on Windows Server 2003 x64 SP2 and SQL Server 2005 x64 SP2 (Build 3159) without any trouble.

We also recently upgrade our development servers to Win2K3 x64 SP2 and SQL Server 2005 x64 SP2 (Build 3175) again without any issues so far.

Which build of SQL Server are you running?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||I think really just coincidence. Can you reccomend is the first step to diagnosing a SQL 2005 server's performance issues?|||

Here are a couple of blog posts that might help:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!526.entry

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!356.entry

|||Nice posts, thanks Glenn.

ms sql 2005

somehow i cannot migrate the database from ms sql 2000 to 2005, i
recreated the same database name and exported the data from 2000 to
2005. THen what happend was all the default values that i set on
tables are not transferred and stored procedures are not transferred.

how can i duplicate the same database in sql 2000 to sql2005 w/o
loosing anything?HandersonVA (handersonva@.hotmail.com) writes:

Quote:

Originally Posted by

The best is of course to keep all definitions of SQL Server objects
under version control, and build the database from the version control
system.

The simplest way to duplicate a database is to simply take a backup of
it and restore it on the target server.

You can also use the Copy Database Wizard, which gives you two choices:
attach/detach and the SMO method. The SMO method scripts everything, but
this method is unreliable. I have filed several bugs with it. You get to
the Copy Database Wizard by right-clicking the database in Object Explorer
and select Tasks->Copy Database.

MS SQL 2000, 2005 multiple instances not broadcasted to other subnets

We currently have multiple instances of MS Sql 2000 and MS SQL 2005 installed on servers. When at other locations that uses different subnets only the default instance is available, published, broadcasted, selectable.

We have TCPIP and name pipes enabled for all instances. This seems to be a common problem for all locations.

As I understand your situation, you have the set of SQL server instances installed on some subnet. From a different subnet, your client application is trying to enumerate the list of SQL instances available. This is an inherit behavior of the SQL instance discovery mechanism. The client application uses User Datagram Protocol (UDP) to send a message on the broadcast address. Most of the hardware network routers available are designed to prevent routing of UDP messages on the broadcast address beyond the subnet of the sender. The end result is what you are seeing where SQL instances on a different subnet can not be discovered. You can still connect to them if you know the machine name and the instance name.

Jimmy

|||Jimmy thanks, that makes sense.

MS Sql 2000 vs. Pervasive SQL

I am using Maximizer Enterprise 8.0 as my companies CRM solution. Currently
we are using the included Pervasive SQL that shipps with the product. Is
there someone out that who could tell me if there are any performance
benefits to switching the Pervasive Engine with a Microsoft SQL engine.

Regards

JesusChuy wrote:

> I am using Maximizer Enterprise 8.0 as my companies CRM solution. Currently
> we are using the included Pervasive SQL that shipps with the product. Is
> there someone out that who could tell me if there are any performance
> benefits to switching the Pervasive Engine with a Microsoft SQL engine.
> Regards
> Jesus
>
Tell us more about your environment. How big is the database?
If I had to guess, i'd say it's not that big if you're running a CRM
package on it.
Pervasive is a really nice SQL database, especially for the price.
It's very reliable, I've seen them running for many years with no
problems. For most small/medium sized businesses, Pervasive is just
fine. Performance is close to if not better in some respects to MS-SQL.

So, what do you need? What features do you require?

It all depends....

If it works, don't fix it....rule #1|||I currenlty have about 50 users accessing the Pervasive Database. The
database is really just a glorified address book with notes, so it's really
not doing a lot of crunching. I do notice though that at peak usage times
certain larger fields (ie notes filed) takes a while to populate. Also we
have a couple of applications that are currently using MSDE and we would
like to consolidate those with one SQL dbase.

"pheonix1t" <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message
news:am5rd.25232$fC4.11701@.newssvr11.news.prodigy. com...
> Chuy wrote:
>> I am using Maximizer Enterprise 8.0 as my companies CRM solution.
>> Currently we are using the included Pervasive SQL that shipps with the
>> product. Is there someone out that who could tell me if there are any
>> performance benefits to switching the Pervasive Engine with a Microsoft
>> SQL engine.
>>
>> Regards
>>
>> Jesus
> Tell us more about your environment. How big is the database?
> If I had to guess, i'd say it's not that big if you're running a CRM
> package on it.
> Pervasive is a really nice SQL database, especially for the price.
> It's very reliable, I've seen them running for many years with no
> problems. For most small/medium sized businesses, Pervasive is just fine.
> Performance is close to if not better in some respects to MS-SQL.
> So, what do you need? What features do you require?
> It all depends....
> If it works, don't fix it....rule #1
>|||Chuy wrote:
> I currenlty have about 50 users accessing the Pervasive Database. The
> database is really just a glorified address book with notes, so it's really
> not doing a lot of crunching. I do notice though that at peak usage times
> certain larger fields (ie notes filed) takes a while to populate. Also we
> have a couple of applications that are currently using MSDE and we would
> like to consolidate those with one SQL dbase.
>
sounds like you may just need to do some performance tuning.
which version of pervasive are u running?
Have you ever tuned this database for your environment?
Do you know how to do performance tuning on your version of Pervasive?

As far as the MSDE, I'm sure there are methods for intergrating that to
pervasive. Have you tried reading their docs or looking at the btrieve
newsgroup? comp.databases.btrieve
it's a pretty flexible database... as all good quality databases should be!

You can also try calling their sales people, they may be able to find
that out for you. It may be as simple as running the newest version.
I know for a dual-CPU license on Netware 6.0, the license was around
$3500 (last summer). It's price is very reasonable compared to MS-SQL.
It's got clustering, replication and all that enterprise class stuff -
if you need it.|||I have limited experiences with Databases, so no I am not familiar with
performance tuning. I have been in the Pervasive SQL management utility
which allows for tweaking of all kinds of settings that I am not positive
what they do. I dare not mess with it if I don't understand it. Do you
have any recommendations for tweaking it, or are there any utils to sniff
out the SQL Dbase access.

Jesus

"pheonix1t" <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message
news:BKprd.39445$Al3.17911@.newssvr30.news.prodigy. com...
> Chuy wrote:
>> I currenlty have about 50 users accessing the Pervasive Database. The
>> database is really just a glorified address book with notes, so it's
>> really not doing a lot of crunching. I do notice though that at peak
>> usage times certain larger fields (ie notes filed) takes a while to
>> populate. Also we have a couple of applications that are currently using
>> MSDE and we would like to consolidate those with one SQL dbase.
>>
>>
> sounds like you may just need to do some performance tuning.
> which version of pervasive are u running?
> Have you ever tuned this database for your environment?
> Do you know how to do performance tuning on your version of Pervasive?
> As far as the MSDE, I'm sure there are methods for intergrating that to
> pervasive. Have you tried reading their docs or looking at the btrieve
> newsgroup? comp.databases.btrieve
> it's a pretty flexible database... as all good quality databases should
> be!
> You can also try calling their sales people, they may be able to find that
> out for you. It may be as simple as running the newest version.
> I know for a dual-CPU license on Netware 6.0, the license was around $3500
> (last summer). It's price is very reasonable compared to MS-SQL.
> It's got clustering, replication and all that enterprise class stuff - if
> you need it.|||Chuy wrote:
> I have limited experiences with Databases, so no I am not familiar with
> performance tuning. I have been in the Pervasive SQL management utility
> which allows for tweaking of all kinds of settings that I am not positive
> what they do. I dare not mess with it if I don't understand it. Do you
> have any recommendations for tweaking it, or are there any utils to sniff
> out the SQL Dbase access.

At this point, I'd suggest you don't mess with it.
You need a good programmer or database consultant (with Pervasive
experience) to work on your database. It should be a quick visit, most
of the time performance tuning for a small environment doesn't take long
(50-users is relatively small for a RDMBS).
It could be something as simple as rebuilding your indexes - but that's
just a guess. You'd need to gather information like what version of
Pervasive you're running on as well as what patch level (if any), what
OS the database is running on, available disk space, amount of RAM on
that database server, stuff like this.

Where are you located? I do IT consulting - we do have remote access
DBA services too.

http://www.atechbusinesssolutions.com

Oskar

my cell is 832-212-2675

> Jesus
> "pheonix1t" <pheonix1tAThoustonDOTrrDOTcom@.com.com> wrote in message
> news:BKprd.39445$Al3.17911@.newssvr30.news.prodigy. com...
>>Chuy wrote:
>>
>>>I currenlty have about 50 users accessing the Pervasive Database. The
>>>database is really just a glorified address book with notes, so it's
>>>really not doing a lot of crunching. I do notice though that at peak
>>>usage times certain larger fields (ie notes filed) takes a while to
>>>populate. Also we have a couple of applications that are currently using
>>>MSDE and we would like to consolidate those with one SQL dbase.
>>>
>>>
>>
>>sounds like you may just need to do some performance tuning.
>>which version of pervasive are u running?
>>Have you ever tuned this database for your environment?
>>Do you know how to do performance tuning on your version of Pervasive?
>>
>>As far as the MSDE, I'm sure there are methods for intergrating that to
>>pervasive. Have you tried reading their docs or looking at the btrieve
>>newsgroup? comp.databases.btrieve
>>it's a pretty flexible database... as all good quality databases should
>>be!
>>
>>You can also try calling their sales people, they may be able to find that
>>out for you. It may be as simple as running the newest version.
>>I know for a dual-CPU license on Netware 6.0, the license was around $3500
>>(last summer). It's price is very reasonable compared to MS-SQL.
>>It's got clustering, replication and all that enterprise class stuff - if
>>you need it.
>>
>>
>

MS SQL 2000 using Mobilink question

Hello,

I am trying to get my PDA program to work on MS SQL 2000, which I originally designed using Sybase ASA 8.

From what I have read, MS SQL 2000 does not support variables as ASA does, but functions may be able to do what I need them to do.

What I am trying to figure out is what I need to do to get it set up so that in MS SQL, there is something that will work like a session-wide variable that holds the user_id, and how to call it from scripts.

Any ideas?Transact SQL definitely supports variables, almost exactly like ASA server does. What you want is available from a function called suser_sname() so I'd just use that.

-PatP|||Ok, thanks.

I will give that a go, and tell you how it works out.

MS SQL 2000 TCPIP Port Number and SQL Replication

Hi all

I'm struggling to find an answer to this one!

We've configured our SQL boxes to run under a different port number,
e.g. from 1433 to 4533. This works fine under our Citrix enviornmnet
where the dsn is configured to use the new port. The problem comes in
terms of SQL Server management. For example, when you try and
configure SQL Replication, it is not possible to add the subscriber
unless the SQL Servce runs under port 1433, the default port. The
same goes when you try to add the server as a registered server within
the Enterprise Manager MMC.

Does anyone know how you can configure SQL Server to run under a
different port number, without naffecting SQL REPLICATION, etc?

Many thnaks
Nik
nik@.nastek.co.uknik@.nastek.co.uk wrote in message news:<a30v10lk653eu3j878hfj0tpflub252qrl@.4ax.com>...
> Hi all
> I'm struggling to find an answer to this one!
> We've configured our SQL boxes to run under a different port number,
> e.g. from 1433 to 4533. This works fine under our Citrix enviornmnet
> where the dsn is configured to use the new port. The problem comes in
> terms of SQL Server management. For example, when you try and
> configure SQL Replication, it is not possible to add the subscriber
> unless the SQL Servce runs under port 1433, the default port. The
> same goes when you try to add the server as a registered server within
> the Enterprise Manager MMC.
> Does anyone know how you can configure SQL Server to run under a
> different port number, without naffecting SQL REPLICATION, etc?
> Many thnaks
> Nik
> nik@.nastek.co.uk

I haven't tried this myself, but this KB article seems to suggest that
creating a server alias (in Client Network Utility) with the correct
port number should work:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402030644.62669b66@.posting.google.com>...
> nik@.nastek.co.uk wrote in message news:<a30v10lk653eu3j878hfj0tpflub252qrl@.4ax.com>...
> > Hi all
> > I'm struggling to find an answer to this one!
> > We've configured our SQL boxes to run under a different port number,
> > e.g. from 1433 to 4533. This works fine under our Citrix enviornmnet
> > where the dsn is configured to use the new port. The problem comes in
> > terms of SQL Server management. For example, when you try and
> > configure SQL Replication, it is not possible to add the subscriber
> > unless the SQL Servce runs under port 1433, the default port. The
> > same goes when you try to add the server as a registered server within
> > the Enterprise Manager MMC.
> > Does anyone know how you can configure SQL Server to run under a
> > different port number, without naffecting SQL REPLICATION, etc?
> > Many thnaks
> > Nik
> > nik@.nastek.co.uk
> I haven't tried this myself, but this KB article seems to suggest that
> creating a server alias (in Client Network Utility) with the correct
> port number should work:
> http://support.microsoft.com/defaul...2&Product=sql2k
> Simon

We have some servers with non-standard ports. We access them using the
Client Network Utility with the correct port and have no problems with
MMC, ISQLW or OSQLW or even ODBC.

MS SQL 2000 string.replace

Hello
with the following query :
SELECT words FROM T1
i get :
A,B,C
how can i get
A > B > C
something like String.Replace(words , ',' , ' > ')
thank youHi,

you can replace SELECT words by SELECT replace(words,',','>')
your query become:

SELECT replace(words,',','>') words FROM T1

goodluck!|||wonderfull, and so easy :-)

thanks a lot|||hi`, sometime everything become simple if we think it simple :)

MS SQL 2000 Standard Edition Capability

Hi,
I'm about to build data warehouse of approx 15 Gb. The biggest table is
expected to be of 10 000 000 records. How will MS SQL 2000 Standard Edition
cope with it?
Thanks,
Alex
Standard edition from memory only supports up to 2Gb of memory. This might
prove problematic depending on how the wareohuse is used.
"Alexander Zotkin" <a@.a> wrote in message
news:emLy0OSDFHA.2220@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm about to build data warehouse of approx 15 Gb. The biggest table is
> expected to be of 10 000 000 records. How will MS SQL 2000 Standard
> Edition
> cope with it?
> Thanks,
> Alex
>
|||Hi,
Scott, you are wrong. If Sql Server Standard Edition is running on Windows
2000 Advance Server or Windows 2003 Server and /3GB switch is on in boot.ini,
Sql Server Standard Edition support up to 3GB.
It’s really a small data warehouse. If you don’t break data warehouse rules
in design and you will create necessary indexes, it will work ok.
Tomasz B.
"Scott Delaney" wrote:

> Standard edition from memory only supports up to 2Gb of memory. This might
> prove problematic depending on how the wareohuse is used.
>
> "Alexander Zotkin" <a@.a> wrote in message
> news:emLy0OSDFHA.2220@.TK2MSFTNGP09.phx.gbl...
>
>
|||I doubt if you will have any problems. 10M fact records is a very small
database. Unless there are other issues, such as several large dimensions,
you will be fine with Standard Edition. As you get into more complex
applications, you might start reading and thinking about the various best
practices outlined in the following two Guides:
http://www.microsoft.com/technet/pro.../anservog.mspx
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexander Zotkin" <a@.a> wrote in message
news:emLy0OSDFHA.2220@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm about to build data warehouse of approx 15 Gb. The biggest table is
> expected to be of 10 000 000 records. How will MS SQL 2000 Standard
Edition
> cope with it?
> Thanks,
> Alex
>
|||Actually Tomasz you are incorrect and Scott is correct. While it is true
that you can enable the /3GB switch SQL Standard is hardcoded to not go
above 2GB.
Alex,
It all boils down to requirements and expectations but with a reasonable
design and proper indexing a 15GB warehouse is well within SQL Standards
capability.
Ray
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:C4C34AFB-6747-4CD9-BB7B-12CE782BAED0@.microsoft.com...
> Hi,
> Scott, you are wrong. If Sql Server Standard Edition is running on Windows
> 2000 Advance Server or Windows 2003 Server and /3GB switch is on in
> boot.ini,
> Sql Server Standard Edition support up to 3GB.
> It's really a small data warehouse. If you don't break data warehouse
> rules
> in design and you will create necessary indexes, it will work ok.
> Tomasz B.
> "Scott Delaney" wrote:
>

MS SQL 2000 Standard Edition Capability

Hi,
I'm about to build data warehouse of approx 15 Gb. The biggest table is
expected to be of 10 000 000 records. How will MS SQL 2000 Standard Edition
cope with it?
Thanks,
AlexStandard edition from memory only supports up to 2Gb of memory. This might
prove problematic depending on how the wareohuse is used.
"Alexander Zotkin" <a@.a> wrote in message
news:emLy0OSDFHA.2220@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm about to build data warehouse of approx 15 Gb. The biggest table is
> expected to be of 10 000 000 records. How will MS SQL 2000 Standard
> Edition
> cope with it?
> Thanks,
> Alex
>|||Hi,
Scott, you are wrong. If Sql Server Standard Edition is running on Windows
2000 Advance Server or Windows 2003 Server and /3GB switch is on in boot.ini
,
Sql Server Standard Edition support up to 3GB.
It’s really a small data warehouse. If you don’t break data warehouse ru
les
in design and you will create necessary indexes, it will work ok.
Tomasz B.
"Scott Delaney" wrote:

> Standard edition from memory only supports up to 2Gb of memory. This might
> prove problematic depending on how the wareohuse is used.
>
> "Alexander Zotkin" <a@.a> wrote in message
> news:emLy0OSDFHA.2220@.TK2MSFTNGP09.phx.gbl...
>
>|||I doubt if you will have any problems. 10M fact records is a very small
database. Unless there are other issues, such as several large dimensions,
you will be fine with Standard Edition. As you get into more complex
applications, you might start reading and thinking about the various best
practices outlined in the following two Guides:
http://www.microsoft.com/technet/pr...n/anservog.mspx
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexander Zotkin" <a@.a> wrote in message
news:emLy0OSDFHA.2220@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm about to build data warehouse of approx 15 Gb. The biggest table is
> expected to be of 10 000 000 records. How will MS SQL 2000 Standard
Edition
> cope with it?
> Thanks,
> Alex
>|||Actually Tomasz you are incorrect and Scott is correct. While it is true
that you can enable the /3GB switch SQL Standard is hardcoded to not go
above 2GB.
Alex,
It all boils down to requirements and expectations but with a reasonable
design and proper indexing a 15GB warehouse is well within SQL Standards
capability.
Ray
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:C4C34AFB-6747-4CD9-BB7B-12CE782BAED0@.microsoft.com...
> Hi,
> Scott, you are wrong. If Sql Server Standard Edition is running on Windows
> 2000 Advance Server or Windows 2003 Server and /3GB switch is on in
> boot.ini,
> Sql Server Standard Edition support up to 3GB.
> It's really a small data warehouse. If you don't break data warehouse
> rules
> in design and you will create necessary indexes, it will work ok.
> Tomasz B.
> "Scott Delaney" wrote:
>
>

MS Sql 2000 Standard Backup

Hello all quick question. We are looking at purchasing MS Sql 2000 standard
and we also are looking at no longer using a tape backup solution but either
that of another drive offsite or some type of removeable hot swap type
drive. I seen a demo of MS Sql 2000 enterprise edition that showed
basically where you could backup to either a tape or other location ie
another network server but I don't know if this is the case with the
standard edition. Any help / website direction would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_03C5_01C3A9EC.DD50C560
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Yes, you can backup to local tape, or local or remote disk on Standard
Edition.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brian" <nixtootec@.hotmail.com> wrote in message
news:OHGZrYhqDHA.2820@.TK2MSFTNGP10.phx.gbl...
Hello all quick question. We are looking at purchasing MS Sql 2000 standard
and we also are looking at no longer using a tape backup solution but either
that of another drive offsite or some type of removeable hot swap type
drive. I seen a demo of MS Sql 2000 enterprise edition that showed
basically where you could backup to either a tape or other location ie
another network server but I don't know if this is the case with the
standard edition. Any help / website direction would be much appreciated.
--=_NextPart_000_03C5_01C3A9EC.DD50C560
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Yes, you can backup to local tape, or =local or remote disk on Standard Edition.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Brian" =wrote in message news:OHGZrYhqDHA.2820=@.TK2MSFTNGP10.phx.gbl...Hello all quick question. We are looking at purchasing MS Sql 2000 standardand we also are looking at no longer using a tape backup =solution but eitherthat of another drive offsite or some type of removeable =hot swap typedrive. I seen a demo of MS Sql 2000 enterprise edition =that showedbasically where you could backup to either a tape or other =location ieanother network server but I don't know if this is the case with thestandard edition. Any help / website direction would be =much appreciated.

--=_NextPart_000_03C5_01C3A9EC.DD50C560--|||This is a multi-part message in MIME format.
--=_NextPart_000_0043_01C3A9E5.FD321A70
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok thanx much for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:Oos%23IbhqDHA.2632@.TK2MSFTNGP09.phx.gbl...
Yes, you can backup to local tape, or local or remote disk on Standard =Edition.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Brian" <nixtootec@.hotmail.com> wrote in message =news:OHGZrYhqDHA.2820@.TK2MSFTNGP10.phx.gbl...
Hello all quick question. We are looking at purchasing MS Sql 2000 =standard
and we also are looking at no longer using a tape backup solution but =either
that of another drive offsite or some type of removeable hot swap type
drive. I seen a demo of MS Sql 2000 enterprise edition that showed
basically where you could backup to either a tape or other location ie
another network server but I don't know if this is the case with the
standard edition. Any help / website direction would be much =appreciated.
--=_NextPart_000_0043_01C3A9E5.FD321A70
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Ok thanx much for the =help.
"Tom Moreau" = wrote in message news:Oos%23IbhqDHA.=2632@.TK2MSFTNGP09.phx.gbl...
Yes, you can backup to local tape, =or local or remote disk on Standard Edition.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"Brian" =wrote in message news:OHGZrYhqDHA.2820=@.TK2MSFTNGP10.phx.gbl...Hello all quick question. We are looking at purchasing MS Sql 2000 standardand we also are looking at no longer using a tape backup =solution but eitherthat of another drive offsite or some type of removeable =hot swap typedrive. I seen a demo of MS Sql 2000 enterprise =edition that showedbasically where you could backup to either a tape or other =location ieanother network server but I don't know if this is the case with = thestandard edition. Any help / website direction would be =much appreciated.

--=_NextPart_000_0043_01C3A9E5.FD321A70--

MS SQL 2000 sp3 problem

Recently we upgrade sql2000 with sp3 and had a problem with database full, we find that even thou "autogrow" was set database was full.
Any ideas?
Thanks,
alexIt's a long shot but, was your hard disk full? This would then not allow for your db to grow.|||I have found the answer to my question

A Timeout Occurs When a Database Is Automatically Expanding
http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
A database does not expand automatically although you have the autogrow option on.
The Errorlog file does not contain any messages that indicate that the database is full.
Client computers that try to modify data in this database receive a "Timeout Expired" message on their user transaction.

Thanks,

alex

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.

The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.

So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,

which state does the error message tell you ?

http://www.lcard.ru/~nail/sybase/error/13243.htm

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.

Unfortunately, the error state is not listed in the URL document you specified.|||

What is the usage of TEMPDB in this environment?

I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.

The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.

So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,

which state does the error message tell you ?

http://www.lcard.ru/~nail/sybase/error/13243.htm

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.

Unfortunately, the error state is not listed in the URL document you specified.|||

What is the usage of TEMPDB in this environment?

I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.

MS SQL 2000 SP Query Plan

I have a couple of complex stored procedures that work well and quickly
once they have compiled. The problem I am running into is that every
once in a while they want to refresh thier execution plans, and when
that happens it takes about 1 minute and 30 seconds for them to
rebuild, well of course my application is set up to time out commands
after 30 seconds so basicly the stored procedure never completes and
hangs up all of my subsequent stored procdures.

I have tried to use

OPTION KEEP FIXEDPLAN

on all of my select statments but I was wondering what else could be
done to stop a stored procedure from it's need to rebuild.

-AdamAdam --

I honestly don't think that what you think is happening is actually
happening. I think what might be more realistic is that one of your
stored procedures has started scanning a table, or acquiring a
long-lived lock, causing the others to slow down. Or perhaps something
else is acquiring a lock, slowing up your procedures. 1:30 to
recompile a query plan is an absolutely enormous amount of time. Keep
in mind that the time that it takes to run your query may vary by the
inputs that are passed to it. Have you run SQL Server Profiler and run
a trace? Look for large amounts of reads and writes associated with
the long duration of your stored procedures.

-Dave|||Adam Rogas (adam.rogas@.gmail.com) writes:
> I have a couple of complex stored procedures that work well and quickly
> once they have compiled. The problem I am running into is that every
> once in a while they want to refresh thier execution plans, and when
> that happens it takes about 1 minute and 30 seconds for them to
> rebuild, well of course my application is set up to time out commands
> after 30 seconds so basicly the stored procedure never completes and
> hangs up all of my subsequent stored procdures.
> I have tried to use
> OPTION KEEP FIXEDPLAN
> on all of my select statments but I was wondering what else could be
> done to stop a stored procedure from it's need to rebuild.

As Dave says, 1 minute for a recompilation is a very long time. There
is all reason to reinvestigate whether the diagnosis is correct. There
could be several other reasons for such stalls.

One way to test this is to run a copy of a procedure with a different
name from Query Analyzer, in this fashion:

CREATE PROCEDURE alternate_name AS ...
go
DECLARE @.d datetime
SELECT @.d = getdate()
EXEC alternate_name ...
PRINT 'First run took ' + ltrim(str(datediff(ms, @.d, getdate())
go
DECLARE @.d datetime
SELECT @.d = getdate()
EXEC alternate_name ...
PRINT 'Second run took ' + ltrim(str(datediff(ms, @.d, getdate())
go
EXEC sp_recompile alternate_name ...
go
DECLARE @.d datetime
SELECT @.d = getdate()
EXEC alternate_name ...
PRINT 'Third run took ' + ltrim(str(datediff(ms, @.d, getdate())

In the first run, there is no plan in csche, so the procedure will
be compiled at least once. Data may or may not be in cache. In the
second run, plan and data is in cache. In the third run, data is still
in cache, but the procedure will be compiled again. Thus, you should
compare the second and third runs.

The biggest procedure in our system is 3000 lines of code. It takes
about 8 seconds to compile. I've seen that queries with very long
IN lists (SELECT ... FROM tbl WHERE col IN (...)) with over 15000
elements can take up to 15 seconds to compile. That is still a far
cry from 90 seconds.

If you indeed have recompilation problems, you need to analyse what the
causes are. The SP:Recompile event populates the EventSubClass column,
values are documented here:
http://support.microsoft.com/defaul...b;EN-US;q308737.

The most likely reason is changed statistics. This white paper may give
guidance in such case:
http://www.microsoft.com/technet/pr...5/qrystats.mspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Saturday, February 25, 2012

ms sql 2005 developer edition

ms sql 2005 delete record problem

ms sql 2005 delete record problem

MS SQL 2005 Connection Problems

MS SQL 2005 Connection Problems

MS SQL 2005 caching dependancy....

MS SQL 2005 caching dependancy....

MS Sql 2005 beta 3 installation errors..Can this version coexist with VS2005 beta 2

MS SQL 2005 and PDC Confusion

MS SQL 2005 + Windows Server 2003 SP2

ms sql 2005

MS SQL 2000, 2005 multiple instances not broadcasted to other subnets

MS Sql 2000 vs. Pervasive SQL

MS SQL 2000 using Mobilink question

MS SQL 2000 TCPIP Port Number and SQL Replication

MS SQL 2000 string.replace

MS SQL 2000 Standard Edition Capability

MS SQL 2000 Standard Edition Capability

MS Sql 2000 Standard Backup

MS SQL 2000 sp3 problem

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.

MS SQL 2000 SP Query Plan

MS Service Pack frustrations

Blog Archive

About Me