MS Sqlserver 2005: Displaying only dbs user has a right to?

Hi;

My company just installed MS SQLServer 2005 ( see below the dotted
line ).

When a user logs into management studio all of the databases on the
server are displayed in the right hand column.

What can we do to have only the databases the user has rights to,
display?

Thanks much in advance for any info.

Steve

--------------------
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 4.0 6.0
Microsoft Internet Explorer7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System5.1.2600Steve (tinker123@.gmail.com) writes:

Quote:

Originally Posted by

My company just installed MS SQLServer 2005 ( see below the dotted
line ).
>
When a user logs into management studio all of the databases on the
server are displayed in the right hand column.
>
What can we do to have only the databases the user has rights to,
display?

What you can do is to revoke the permission VIEW ANY DATABASE from public,
or DENY this permission to the users in question. Alas, the user would then only see tempdb and msdb (or was it master?), databases he owns, and
possibly his current database. He would have to issue a USE database in
the dark to get there.

This is not an uncommon question, so I filed a suggestion for an improvement
in SQL Server. You can vote on it at
https://connect.microsoft.com/SQLSe...edbackID=273830
--
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 Command line insert

I use a similar command below to insert into a temp table the result of a large command line call to an exectable with many parameters passed in the command of which the result passed back contains many items. I then parse the response string to get my results...

set @.command = 'dir'
insert into tsverisign(response) exec master..xp_cmdshell @.command

My question is our can I insert two values at the same time to this same table one of which is my "exec master..xp_cmdshell @.command"

similar to insert into tables (field_a, feild_b) values ('1','2')

Something like (and I know this does not work):

insert into tsverisign(response,trans_id)
values (exec master..xp_cmdshell @.command, '123')

Any help would be greatly appreciated ... PS I'm new to MS SQL 2000 and proper syntax etc. etc. so I need full example so I can try. :rolleyes:What you're trying to do is not possible (in any dbms). What you should do is to dump the result of @.command into a staging table. Then insert it into the target table.

e.g.
insert into staging(i)
exec master..xp_cmdshell @.command

insert into target(i,j)
select i, @.other_val
from staging

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

GO

DROP INDEX PK_IvMthEnd

ON IvMthEnd;

GO

Command(s) completed successfully.

then i create the index again with the code below

GO

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

GO

CREATE INDEX PK_IvMthEnd

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

GO

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'

go

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;

GO

then i create the index again

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

GO

CREATE INDEX PK_IvMthEnd

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

GO

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

GO

DROP INDEX PK_IvMthEnd

ON IvMthEnd;

GO

Command(s) completed successfully.

then i create the index again with the code below

GO

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

GO

CREATE INDEX PK_IvMthEnd

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

GO

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'

go

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;

GO

then i create the index again

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'PK_IvMthEnd')

DROP INDEX PK_IvMthEnd ON IvMthEnd;

GO

CREATE INDEX PK_IvMthEnd

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

GO

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 (min function To min2ndlowest)

Hi,
Below stated is my original query from my database but for the sake ofsimplicity , I have used the pubs database to convey my thoughts.

select p.pub_name,min(t.price),max(t.price) from publishers p,titles t
where
p.pub_id=t.pub_id
group by p.pub_name

Instead of the min(Price), I would like to get the min2ndLowest(Price).
Is there away to manipulate the Min function so as to change it . Or is itsomehow possible to rewrite another function like Min2ndLowest() toeasily solve this situation.I will be more happy to solve via thisroute as I later have to solve other queries like Max2ndHighest() andso forth.
Or is it only possible thru some serious query design

Thanks for help guys..

My Original Query

select b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported,min(t.begintime),max(t.begintime)
from
batches b, queues q,tasks t
where
b.queueid=q.queueid
and
b.batchid = t.batchid
group by b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported

--SELECT MIN(b.PRICE1) AS Max2ndHighest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1 DESC) AS b

--SELECT MAX(b.PRICE1) As MIN2ndLowest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1) AS b

--table is from this thread with more data:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619492&SiteID=1

create

table #x

(

ProductID

int,

MarketID

int,

Date

datetime,

PRICE1

decimal(9,2),

PRICE2

decimal(9,2),

PRICE3

decimal(9,2)

)

insert

#x

select

1, 2,'1/01/2006', 2.78, 3.4, 2.97unionall

select

1, 2,'2/01/2006', 2.51, 3.5,NULLunionall

select

1, 2,'3/01/2006',NULL, 3.6,NULLunionall

select

1, 2,'4/01/2006',NULL, 3.55,NULLunionall

select

2, 4,'1/01/2006', 3.44, 1.23, 4.33unionall

select

2, 4,'2/01/2006', 3.55, 1.22, 4.22unionall

select

2, 4,'3/01/2006', 3.13, 1.51, 4.54unionall

select

2, 4,'4/01/2006',NULL, 1.50, 4.25

SELECT b.ProductID, b.MarketID,

(

SELECTMIN(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDORDERBY a.PRICE2DESC)AS c)AS Max2ndHighest,

(

SELECTMAX(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDorderby a.PRICE2)AS c)AS MIN2ndLowest

FROM

#xAS b

GROUP

BY b.ProductID, b.MarketID

drop

table #x|||

Insert this into the joins area of your original query:

LEFT JOIN ({a complete copy of your original query}) t1 ON ({field1 from original query}=t1.{field1 from original query} AND {field2}=t1.{field2} ... AND {field to be min-ed from original query}={min-ed field result from subquery})

Then add

WHERE t1.{field1} IS NULL to the where clause of your original query.

For example:

SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1

becomes

SELECT field1,MIN(field2) AS MinField2
FROM table1
LEFT JOIN (
SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1) t1 ON (table1.field1=t1.field1 AND table1.field2=t1.MinField2)
WHERE field3='something'AND t1.field1 IS NULL
GROUP BY field1