consistency errors not associated with any single object

I have a dbcc checkdb returning that I have two consistency errors. One
is directly attributal to a table. The second is not attributable to
any single objects. It is my understanding that this second consistency
error is related to an extent that is shared. Is there anyway to see
which objects share this unknown extnet? If I issue a dbcc checkdb
along with a repair allow data loss is there anyway to see what data it
will remove before it does so?
Thanks ahead of time.Please post the exact output from CHECKDB.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:uZDCRS3YEHA.644@.tk2msftngp13.phx.gbl...
> I have a dbcc checkdb returning that I have two consistency errors. One
> is directly attributal to a table. The second is not attributable to
> any single objects. It is my understanding that this second consistency
> error is related to an extent that is shared. Is there anyway to see
> which objects share this unknown extnet? If I issue a dbcc checkdb
> along with a repair allow data loss is there anyway to see what data it
> will remove before it does so?
> Thanks ahead of time.|||Paul S Randal [MS] wrote:
> Please post the exact output from CHECKDB.
>
There is an error at the very top, this is the one I am refereing to.
The error in the middle relates to a particular table that I can deal with.
Job 'DBCC_checkdb' : Step 7, 'dbcc checkdb stage' : Began Executing
2004-07-06 09:12:59
Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID
(3:391967). The PageId in the page header = (0:24). [SQLSTATE 42000]
Msg 2536, Sev 16: DBCC results for 'stage'. [SQLSTATE 01000]
Msg 8954, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
errors not associated with any single object. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysobjects'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 329 rows in 7 pages for object 'sysobjects'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysindexes'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 666 rows in 37 pages for object
'sysindexes'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syscolumns'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 3400 rows in 78 pages for object
'syscolumns'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'systypes'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 26 rows in 1 pages for object 'systypes'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syscomments'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 296 rows in 81 pages for object
'syscomments'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfiles1'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 7 rows in 1 pages for object 'sysfiles1'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'syspermissions'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 52 rows in 1 pages for object
'syspermissions'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysusers'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18 rows in 1 pages for object 'sysusers'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysproperties'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysproperties'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysdepends'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 2423 rows in 15 pages for object
'sysdepends'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysreferences'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 3 rows in 1 pages for object
'sysreferences'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysfulltextcatalogs'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 0 rows in 0 pages for object
'sysfulltextnotify'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 6 rows in 1 pages for object
'sysfilegroups'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_control_missing_day_ends_tbl'.
[SQLSTATE 01000]
There are 0 rows in 0 pages for object
'meta_control_missing_day_ends_tbl'. [SQLSTATE 01000]
DBCC results for 'import_opco_AFSLHF'. [SQLSTATE 01000]
There are 6442240 rows in 715814 pages for object 'import_opco_AFSLHF'.
[SQLSTATE 01000]
DBCC results for 'import_opco_AGPODF'. [SQLSTATE 01000]
There are 4601233 rows in 678964 pages for object 'import_opco_AGPODF'.
[SQLSTATE 01000]
DBCC results for 'BRBRDF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'BRBRDF'. [SQLSTATE 01000]
DBCC results for 'import_opco_AHPOHF'. [SQLSTATE 01000]
There are 592812 rows in 98813 pages for object 'import_opco_AHPOHF'.
[SQLSTATE 01000]
DBCC results for 'CR01F'. [SQLSTATE 01000]
There are 1570 rows in 11 pages for object 'CR01F'. [SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CMOTYPF'. [SQLSTATE 01000]
There are 65576 rows in 227 pages for object 'XXX_import_pfg_CMOTYPF'.
[SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CMPTYPF'. [SQLSTATE 01000]
There are 5415 rows in 33 pages for object 'XXX_import_pfg_CMPTYPF'.
[SQLSTATE 01000]
DBCC results for 'TYTYPF'. [SQLSTATE 01000]
There are 20800 rows in 138 pages for object 'TYTYPF'. [SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CTOTYPF'. [SQLSTATE 01000]
There are 340193 rows in 1516 pages for object 'XXX_import_pfg_CTOTYPF'.
[SQLSTATE 01000]
DBCC results for 'meta_AJFFCTLF_Status_tbl'. [SQLSTATE 01000]
There are 5 rows in 1 pages for object 'meta_AJFFCTLF_Status_tbl'.
[SQLSTATE 01000]
DBCC results for 'AESLDF_tbl'. [SQLSTATE 01000]
There are 14807362 rows in 227806 pages for object 'AESLDF_tbl'.
[SQLSTATE 01000]
DBCC results for 'XXX_import_pfg_CTPTYPF'. [SQLSTATE 01000]
There are 35448 rows in 229 pages for object 'XXX_import_pfg_CTPTYPF'.
[SQLSTATE 01000]
DBCC results for 'AFSLHF_tbl'. [SQLSTATE 01000]
There are 751636 rows in 11219 pages for object 'AFSLHF_tbl'. [SQLSTATE
01000]
DBCC results for 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
There are 7 rows in 1 pages for object
'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
DBCC results for 'AGPODF_tbl'. [SQLSTATE 01000]
There are 531337 rows in 6561 pages for object 'AGPODF_tbl'. [SQLSTATE
01000]
DBCC results for 'import_pfg_LOCDMAST'. [SQLSTATE 01000]
There are 3300 rows in 27 pages for object 'import_pfg_LOCDMAST'.
[SQLSTATE 01000]
DBCC results for 'AHPOHF_tbl'. [SQLSTATE 01000]
There are 68367 rows in 732 pages for object 'AHPOHF_tbl'. [SQLSTATE 01000]
DBCC results for 'import_pfg_LOCMAST'. [SQLSTATE 01000]
There are 42331 rows in 321 pages for object 'import_pfg_LOCMAST'.
[SQLSTATE 01000]
DBCC results for 'ajffctlf_tbl'. [SQLSTATE 01000]
There are 16194 rows in 443 pages for object 'ajffctlf_tbl'. [SQLSTATE
01000]
DBCC results for 'import_pfg_LOCRMAST'. [SQLSTATE 01000]
There are 3210 rows in 26 pages for object 'import_pfg_LOCRMAST'.
[SQLSTATE 01000]
DBCC results for 'FM03MJF'. [SQLSTATE 01000]
There are 3505 rows in 23 pages for object 'FM03MJF'. [SQLSTATE 01000]
DBCC results for 'meta_control_AS400_trans'. [SQLSTATE 01000]
There are 112799 rows in 1849 pages for object
'meta_control_AS400_trans'. [SQLSTATE 01000]
DBCC results for 'meta_logging_AS400_trans'. [SQLSTATE 01000]
There are 18121 rows in 154 pages for object 'meta_logging_AS400_trans'.
[SQLSTATE 01000]
DBCC results for 'sys_time'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sys_time'. [SQLSTATE 01000]
DBCC results for 'sys_time_fiscal'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'sys_time_fiscal'. [SQLSTATE 01000]
DBCC results for 'meta_certification_history'. [SQLSTATE 01000]
There are 49337 rows in 537 pages for object
'meta_certification_history'. [SQLSTATE 01000]
DBCC results for 'meta_certification_current'. [SQLSTATE 01000]
There are 400 rows in 17 pages for object 'meta_certification_current'.
[SQLSTATE 01000]
DBCC results for 'meta_table_types_tbl'. [SQLSTATE 01000]
There are 4 rows in 1 pages for object 'meta_table_types_tbl'. [SQLSTATE
01000]
DBCC results for 'dtproperties'. [SQLSTATE 01000]
There are 28 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
DBCC results for 'meta_control_AJ_import_tbl'. [SQLSTATE 01000]
There are 8 rows in 4 pages for object 'meta_control_AJ_import_tbl'.
[SQLSTATE 01000]
Msg 8928, Sev 16: Object ID 880722190, index ID 0: Page (3:391967) could
not be processed. See other errors for details. [SQLSTATE 42000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2001'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 18047893 rows in 2255990 pages for object
'import_opco_AESLDF_2001'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'maxdayend'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 1739 rows in 6 pages for object 'maxdayend'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2002'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 39724816 rows in 5062006 pages for object
'import_opco_AESLDF_2002'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2003'. [SQLSTATE
01000]
Msg 2593, Sev 16: There are 51367587 rows in 7716283 pages for object
'import_opco_AESLDF_2003'. [SQLSTATE 01000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
errors in table 'import_opco_AESLDF_2003' (object ID 880722190).
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_default'.
[SQLSTATE 01000]
Msg 2593, Sev 16: There are 178 rows in 23 pages for object
'import_opco_AESLDF_default'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'sf_opco_item_temp'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18025657 rows in 559636 pages for object
'sf_opco_item_temp'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_operation'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 7 rows in 1 pages for object
'meta_operation'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'meta_control_as400_trans_02'.
[SQLSTATE 01000]
Msg 2593, Sev 16: There are 404306 rows in 2230 pages for object
'meta_control_as400_trans_02'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CLCLSF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 18710 rows in 135 pages for object 'CLCLSF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSCHNF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 67202 rows in 505 pages for object 'CSCHNF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSFRCF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 619 rows in 9 pages for object 'CSFRCF'.
[SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSGROUPD'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 357624 rows in 2430 pages for object
'CSGROUPD'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'CSGROUPH'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 167472 rows in 1173 pages for object
'CSGROUPH'. [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'ITITMF'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 29342780 rows in 846483 pages for object
'ITITMF'. [SQLSTATE 01000]
DBCC results for 'MIT01F'. [SQLSTATE 01000]
There are 2158317 rows in 8969 pages for object 'MIT01F'. [SQLSTATE 01000]
DBCC results for 'PBPBHF'. [SQLSTATE 01000]
There are 207741 rows in 1472 pages for object 'PBPBHF'. [SQLSTATE 01000]
DBCC results for 'SLSLSF'. [SQLSTATE 01000]
There are 299042 rows in 2253 pages for object 'SLSLSF'. [SQLSTATE 01000]
DBCC results for 'VNVNDF'. [SQLSTATE 01000]
There are 2085401 rows in 15823 pages for object 'VNVNDF'. [SQLSTATE 01000]
DBCC results for 'CSCSTF2'. [SQLSTATE 01000]
There are 1731565 rows in 6578 pages for object 'CSCSTF2'. [SQLSTATE 01000]
DBCC results for 'INVREGF'. [SQLSTATE 01000]
There are 386374 rows in 3488 pages for object 'INVREGF'. [SQLSTATE 01000]
DBCC results for 'OCOSCF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'OCOSCF'. [SQLSTATE 01000]
DBCC results for 'RMRTMF'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'RMRTMF'. [SQLSTATE 01000]
DBCC results for 'ITBAAF'. [SQLSTATE 01000]
There are 70680533 rows in 627912 pages for object 'ITBAAF'. [SQLSTATE
01000]
DBCC results for 'STPCNTF'. [SQLSTATE 01000]
There are 7217728 rows in 44833 pages for object 'STPCNTF'. [SQLSTATE 01000]
DBCC results for 'meta_control_opco_info'. [SQLSTATE 01000]
There are 18 rows in 1 pages for object 'meta_control_opco_info'.
[SQLSTATE 01000]
DBCC results for 'AIDEHF_backup'. [SQLSTATE 01000]
There are 5553 rows in 206 pages for object 'AIDEHF_backup'. [SQLSTATE
01000]
DBCC results for 'MostCurrentVersionDayEnd'. [SQLSTATE 01000]
There are 1 rows in 1 pages for object 'MostCurrentVersionDayEnd'.
[SQLSTATE 01000]
DBCC results for 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
There are 18863 rows in 177 pages for object
'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
DBCC results for 'meta_control_table_info'. [SQLSTATE 01000]
There are 18 rows in 1 pages for object 'meta_control_table_info'.
[SQLSTATE 01000]
DBCC results for 'cd_attribute'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_attribute'. [SQLSTATE 01000]
DBCC results for 'cd_attribute_item_xref'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_attribute_item_xref'.
[SQLSTATE 01000]
DBCC results for 'RCTYPF'. [SQLSTATE 01000]
There are 4 rows in 1 pages for object 'RCTYPF'. [SQLSTATE 01000]
DBCC results for 'cd_attribute_type'. [SQLSTATE 01000]
There are 51 rows in 1 pages for object 'cd_attribute_type'. [SQLSTATE
01000]
DBCC results for 'cd_attribute_type_xref'. [SQLSTATE 01000]
There are 2 rows in 1 pages for object 'cd_attribute_type_xref'.
[SQLSTATE 01000]
DBCC results for 'CSRECAP'. [SQLSTATE 01000]
There are 6500447 rows in 73039 pages for object 'CSRECAP'. [SQLSTATE 01000]
DBCC results for 'cd_base_item'. [SQLSTATE 01000]
There are 5492062 rows in 458052 pages for object 'cd_base_item'.
[SQLSTATE 01000]
DBCC results for 'MasterDeliveryInfo'. [SQLSTATE 01000]
There are 275 rows in 2 pages for object 'MasterDeliveryInfo'. [SQLSTATE
01000]
DBCC results for 'CSCSTF'. [SQLSTATE 01000]
There are 15585869 rows in 833613 pages for object 'CSCSTF'. [SQLSTATE
01000]
DBCC results for 'cd_brand'. [SQLSTATE 01000]
There are 220167 rows in 2489 pages for object 'cd_brand'. [SQLSTATE 01000]
DBCC results for 'FinalDeliveryInfo'. [SQLSTATE 01000]
There are 275 rows in 2 pages for object 'FinalDeliveryInfo'. [SQLSTATE
01000]
DBCC results for 'RCRECF'. [SQLSTATE 01000]
There are 19468099 rows in 150916 pages for object 'RCRECF'. [SQLSTATE
01000]
DBCC results for 'cd_brand_type'. [SQLSTATE 01000]
There are 328 rows in 5 pages for object 'cd_brand_type'. [SQLSTATE 01000]
DBCC results for 'DECTL02F'. [SQLSTATE 01000]
There are 1252068 rows in 24082 pages for object 'DECTL02F'. [SQLSTATE
01000]
DBCC results for 'cd_category'. [SQLSTATE 01000]
There are 1961 rows in 23 pages for object 'cd_category'. [SQLSTATE 01000]
DBCC results for 'cd_cop'. [SQLSTATE 01000]
There are 347 rows in 6 pages for object 'cd_cop'. [SQLSTATE 01000]
DBCC results for 'cd_item'. [SQLSTATE 01000]
There are 2878151 rows in 33269 pages for object 'cd_item'. [SQLSTATE 01000]
DBCC results for 'cd_item_xref'. [SQLSTATE 01000]
There are 32862699 rows in 119501 pages for object 'cd_item_xref'.
[SQLSTATE 01000]
DBCC results for 'cd_packer_item'. [SQLSTATE 01000]
There are 2875655 rows in 45046 pages for object 'cd_packer_item'.
[SQLSTATE 01000]
DBCC results for 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
There are 12815617 rows in 2110584 pages for object
'import_opco_AESLDF_2004'. [SQLSTATE 01000]
DBCC results for 'cd_size'. [SQLSTATE 01000]
There are 1678 rows in 10 pages for object 'cd_size'. [SQLSTATE 01000]
DBCC results for 'cd_subcategory'. [SQLSTATE 01000]
There are 5449 rows in 63 pages for object 'cd_subcategory'. [SQLSTATE
01000]
DBCC results for 'cd_supplier'. [SQLSTATE 01000]
There are 274329 rows in 2633 pages for object 'cd_supplier'. [SQLSTATE
01000]
DBCC results for 'TETRTF'. [SQLSTATE 01000]
There are 4302 rows in 29 pages for object 'TETRTF'. [SQLSTATE 01000]
DBCC results for 'cd_synonym'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_synonym'. [SQLSTATE 01000]
DBCC results for 'SGSMGF'. [SQLSTATE 01000]
There are 5021 rows in 35 pages for object 'SGSMGF'. [SQLSTATE 01000]
DBCC results for 'meta_control_AI_import_tbl'. [SQLSTATE 01000]
There are 0 rows in 4 pages for object 'meta_control_AI_import_tbl'.
[SQLSTATE 01000]
DBCC results for 'cd_temp_zone'. [SQLSTATE 01000]
There are 204 rows in 2 pages for object 'cd_temp_zone'. [SQLSTATE 01000]
DBCC results for 'ajffctlf_holding_tbl'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'ajffctlf_holding_tbl'. [SQLSTATE
01000]
DBCC results for 'cd_vendor_xref'. [SQLSTATE 01000]
There are 739871 rows in 3866 pages for object 'cd_vendor_xref'.
[SQLSTATE 01000]
DBCC results for 'cd_word'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'cd_word'. [SQLSTATE 01000]
DBCC results for 'meta_logging_stage_errors'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'meta_logging_stage_errors'.
[SQLSTATE 01000]
DBCC results for 'AIDEHF_tbl'. [SQLSTATE 01000]
There are 1060 rows in 70 pages for object 'AIDEHF_tbl'. [SQLSTATE 01000]
DBCC results for 'AKDFCTLF'. [SQLSTATE 01000]
There are 61 rows in 1 pages for object 'AKDFCTLF'. [SQLSTATE 01000]
DBCC results for 'AIDEHF_holding_tbl'. [SQLSTATE 01000]
There are 0 rows in 1 pages for object 'AIDEHF_holding_tbl'. [SQLSTATE
01000]
DBCC results for 'meta_control_AIDEHF_errors_tbl'. [SQLSTATE 01000]
There are 0 rows in 0 pages for object 'meta_control_AIDEHF_errors_tbl'.
[SQLSTATE 01000]
CHECKDB found 0 allocation errors and 2 consistency errors in database
'stage'. [SQLSTATE 01000]
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (stage ). [SQLSTATE 01000]
DBCC execution completed. If DBCC printed error messages, contact your
system administrator. [SQLSTATE 01000]|||The page is corrupt (bad page id) and won't be processed. Depending on how
corrupt the page header is you may or may not be able to work out which
object/index it's from, but that's beyond the scope of this forum. I suspect
its a data page in a heap as there are no b-tree linkage errors and no
errors that would suggest its an IAM page. That being the case, the repair
is to delete the page (and hence all the data on the page) - that's why the
repair option is called REPAIR_ALLOW_DATA_LOSS. Given that it's probably a
heap data page, there's no easy way to work out what data will be deleted.
Your best course of action is to restore from your most recent backup and
then restore your log backups to the point of failure. Failing that, you'll
have to run repair. I suggest you do root-cause analysis to find out how
this happened - look in your NT event logs and the SQL errorlog for IO
subsystem errors.
I'd further suggest that you call Product Support to help you with this if
you're uncomfortable with any of the above.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"sfibich" <sfibich@.pfgc.com> wrote in message
news:eQT7#l5YEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Paul S Randal [MS] wrote:
> > Please post the exact output from CHECKDB.
> >
> There is an error at the very top, this is the one I am refereing to.
> The error in the middle relates to a particular table that I can deal
with.
> Job 'DBCC_checkdb' : Step 7, 'dbcc checkdb stage' : Began Executing
> 2004-07-06 09:12:59
> Msg 8909, Sev 16: Table error: Object ID 0, index ID 0, page ID
> (3:391967). The PageId in the page header = (0:24). [SQLSTATE 42000]
> Msg 2536, Sev 16: DBCC results for 'stage'. [SQLSTATE 01000]
> Msg 8954, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
> errors not associated with any single object. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysobjects'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 329 rows in 7 pages for object 'sysobjects'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysindexes'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 666 rows in 37 pages for object
> 'sysindexes'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syscolumns'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 3400 rows in 78 pages for object
> 'syscolumns'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'systypes'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 26 rows in 1 pages for object 'systypes'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syscomments'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 296 rows in 81 pages for object
> 'syscomments'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfiles1'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 7 rows in 1 pages for object 'sysfiles1'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'syspermissions'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 52 rows in 1 pages for object
> 'syspermissions'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysusers'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18 rows in 1 pages for object 'sysusers'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysproperties'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysproperties'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysdepends'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 2423 rows in 15 pages for object
> 'sysdepends'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysreferences'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 3 rows in 1 pages for object
> 'sysreferences'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfulltextcatalogs'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysfulltextcatalogs'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfulltextnotify'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 0 rows in 0 pages for object
> 'sysfulltextnotify'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sysfilegroups'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 6 rows in 1 pages for object
> 'sysfilegroups'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_control_missing_day_ends_tbl'.
> [SQLSTATE 01000]
> There are 0 rows in 0 pages for object
> 'meta_control_missing_day_ends_tbl'. [SQLSTATE 01000]
> DBCC results for 'import_opco_AFSLHF'. [SQLSTATE 01000]
> There are 6442240 rows in 715814 pages for object 'import_opco_AFSLHF'.
> [SQLSTATE 01000]
> DBCC results for 'import_opco_AGPODF'. [SQLSTATE 01000]
> There are 4601233 rows in 678964 pages for object 'import_opco_AGPODF'.
> [SQLSTATE 01000]
> DBCC results for 'BRBRDF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'BRBRDF'. [SQLSTATE 01000]
> DBCC results for 'import_opco_AHPOHF'. [SQLSTATE 01000]
> There are 592812 rows in 98813 pages for object 'import_opco_AHPOHF'.
> [SQLSTATE 01000]
> DBCC results for 'CR01F'. [SQLSTATE 01000]
> There are 1570 rows in 11 pages for object 'CR01F'. [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CMOTYPF'. [SQLSTATE 01000]
> There are 65576 rows in 227 pages for object 'XXX_import_pfg_CMOTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CMPTYPF'. [SQLSTATE 01000]
> There are 5415 rows in 33 pages for object 'XXX_import_pfg_CMPTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'TYTYPF'. [SQLSTATE 01000]
> There are 20800 rows in 138 pages for object 'TYTYPF'. [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CTOTYPF'. [SQLSTATE 01000]
> There are 340193 rows in 1516 pages for object 'XXX_import_pfg_CTOTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'meta_AJFFCTLF_Status_tbl'. [SQLSTATE 01000]
> There are 5 rows in 1 pages for object 'meta_AJFFCTLF_Status_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'AESLDF_tbl'. [SQLSTATE 01000]
> There are 14807362 rows in 227806 pages for object 'AESLDF_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'XXX_import_pfg_CTPTYPF'. [SQLSTATE 01000]
> There are 35448 rows in 229 pages for object 'XXX_import_pfg_CTPTYPF'.
> [SQLSTATE 01000]
> DBCC results for 'AFSLHF_tbl'. [SQLSTATE 01000]
> There are 751636 rows in 11219 pages for object 'AFSLHF_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
> There are 7 rows in 1 pages for object
> 'meta_MetaControlAs400Trans_Status_tbl'. [SQLSTATE 01000]
> DBCC results for 'AGPODF_tbl'. [SQLSTATE 01000]
> There are 531337 rows in 6561 pages for object 'AGPODF_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'import_pfg_LOCDMAST'. [SQLSTATE 01000]
> There are 3300 rows in 27 pages for object 'import_pfg_LOCDMAST'.
> [SQLSTATE 01000]
> DBCC results for 'AHPOHF_tbl'. [SQLSTATE 01000]
> There are 68367 rows in 732 pages for object 'AHPOHF_tbl'. [SQLSTATE
01000]
> DBCC results for 'import_pfg_LOCMAST'. [SQLSTATE 01000]
> There are 42331 rows in 321 pages for object 'import_pfg_LOCMAST'.
> [SQLSTATE 01000]
> DBCC results for 'ajffctlf_tbl'. [SQLSTATE 01000]
> There are 16194 rows in 443 pages for object 'ajffctlf_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'import_pfg_LOCRMAST'. [SQLSTATE 01000]
> There are 3210 rows in 26 pages for object 'import_pfg_LOCRMAST'.
> [SQLSTATE 01000]
> DBCC results for 'FM03MJF'. [SQLSTATE 01000]
> There are 3505 rows in 23 pages for object 'FM03MJF'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AS400_trans'. [SQLSTATE 01000]
> There are 112799 rows in 1849 pages for object
> 'meta_control_AS400_trans'. [SQLSTATE 01000]
> DBCC results for 'meta_logging_AS400_trans'. [SQLSTATE 01000]
> There are 18121 rows in 154 pages for object 'meta_logging_AS400_trans'.
> [SQLSTATE 01000]
> DBCC results for 'sys_time'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sys_time'. [SQLSTATE 01000]
> DBCC results for 'sys_time_fiscal'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'sys_time_fiscal'. [SQLSTATE 01000]
> DBCC results for 'meta_certification_history'. [SQLSTATE 01000]
> There are 49337 rows in 537 pages for object
> 'meta_certification_history'. [SQLSTATE 01000]
> DBCC results for 'meta_certification_current'. [SQLSTATE 01000]
> There are 400 rows in 17 pages for object 'meta_certification_current'.
> [SQLSTATE 01000]
> DBCC results for 'meta_table_types_tbl'. [SQLSTATE 01000]
> There are 4 rows in 1 pages for object 'meta_table_types_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'dtproperties'. [SQLSTATE 01000]
> There are 28 rows in 1 pages for object 'dtproperties'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AJ_import_tbl'. [SQLSTATE 01000]
> There are 8 rows in 4 pages for object 'meta_control_AJ_import_tbl'.
> [SQLSTATE 01000]
> Msg 8928, Sev 16: Object ID 880722190, index ID 0: Page (3:391967) could
> not be processed. See other errors for details. [SQLSTATE 42000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2001'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 18047893 rows in 2255990 pages for object
> 'import_opco_AESLDF_2001'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'maxdayend'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 1739 rows in 6 pages for object 'maxdayend'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2002'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 39724816 rows in 5062006 pages for object
> 'import_opco_AESLDF_2002'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_2003'. [SQLSTATE
> 01000]
> Msg 2593, Sev 16: There are 51367587 rows in 7716283 pages for object
> 'import_opco_AESLDF_2003'. [SQLSTATE 01000]
> Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 1 consistency
> errors in table 'import_opco_AESLDF_2003' (object ID 880722190).
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'import_opco_AESLDF_default'.
> [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 178 rows in 23 pages for object
> 'import_opco_AESLDF_default'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'sf_opco_item_temp'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18025657 rows in 559636 pages for object
> 'sf_opco_item_temp'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_operation'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 7 rows in 1 pages for object
> 'meta_operation'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'meta_control_as400_trans_02'.
> [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 404306 rows in 2230 pages for object
> 'meta_control_as400_trans_02'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CLCLSF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 18710 rows in 135 pages for object 'CLCLSF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSCHNF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 67202 rows in 505 pages for object 'CSCHNF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSFRCF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 619 rows in 9 pages for object 'CSFRCF'.
> [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSGROUPD'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 357624 rows in 2430 pages for object
> 'CSGROUPD'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'CSGROUPH'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 167472 rows in 1173 pages for object
> 'CSGROUPH'. [SQLSTATE 01000]
> Msg 2536, Sev 16: DBCC results for 'ITITMF'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 29342780 rows in 846483 pages for object
> 'ITITMF'. [SQLSTATE 01000]
> DBCC results for 'MIT01F'. [SQLSTATE 01000]
> There are 2158317 rows in 8969 pages for object 'MIT01F'. [SQLSTATE 01000]
> DBCC results for 'PBPBHF'. [SQLSTATE 01000]
> There are 207741 rows in 1472 pages for object 'PBPBHF'. [SQLSTATE 01000]
> DBCC results for 'SLSLSF'. [SQLSTATE 01000]
> There are 299042 rows in 2253 pages for object 'SLSLSF'. [SQLSTATE 01000]
> DBCC results for 'VNVNDF'. [SQLSTATE 01000]
> There are 2085401 rows in 15823 pages for object 'VNVNDF'. [SQLSTATE
01000]
> DBCC results for 'CSCSTF2'. [SQLSTATE 01000]
> There are 1731565 rows in 6578 pages for object 'CSCSTF2'. [SQLSTATE
01000]
> DBCC results for 'INVREGF'. [SQLSTATE 01000]
> There are 386374 rows in 3488 pages for object 'INVREGF'. [SQLSTATE 01000]
> DBCC results for 'OCOSCF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'OCOSCF'. [SQLSTATE 01000]
> DBCC results for 'RMRTMF'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'RMRTMF'. [SQLSTATE 01000]
> DBCC results for 'ITBAAF'. [SQLSTATE 01000]
> There are 70680533 rows in 627912 pages for object 'ITBAAF'. [SQLSTATE
> 01000]
> DBCC results for 'STPCNTF'. [SQLSTATE 01000]
> There are 7217728 rows in 44833 pages for object 'STPCNTF'. [SQLSTATE
01000]
> DBCC results for 'meta_control_opco_info'. [SQLSTATE 01000]
> There are 18 rows in 1 pages for object 'meta_control_opco_info'.
> [SQLSTATE 01000]
> DBCC results for 'AIDEHF_backup'. [SQLSTATE 01000]
> There are 5553 rows in 206 pages for object 'AIDEHF_backup'. [SQLSTATE
> 01000]
> DBCC results for 'MostCurrentVersionDayEnd'. [SQLSTATE 01000]
> There are 1 rows in 1 pages for object 'MostCurrentVersionDayEnd'.
> [SQLSTATE 01000]
> DBCC results for 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
> There are 18863 rows in 177 pages for object
> 'RecordsMostCurrentLastSoldDate'. [SQLSTATE 01000]
> DBCC results for 'meta_control_table_info'. [SQLSTATE 01000]
> There are 18 rows in 1 pages for object 'meta_control_table_info'.
> [SQLSTATE 01000]
> DBCC results for 'cd_attribute'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_attribute'. [SQLSTATE 01000]
> DBCC results for 'cd_attribute_item_xref'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_attribute_item_xref'.
> [SQLSTATE 01000]
> DBCC results for 'RCTYPF'. [SQLSTATE 01000]
> There are 4 rows in 1 pages for object 'RCTYPF'. [SQLSTATE 01000]
> DBCC results for 'cd_attribute_type'. [SQLSTATE 01000]
> There are 51 rows in 1 pages for object 'cd_attribute_type'. [SQLSTATE
> 01000]
> DBCC results for 'cd_attribute_type_xref'. [SQLSTATE 01000]
> There are 2 rows in 1 pages for object 'cd_attribute_type_xref'.
> [SQLSTATE 01000]
> DBCC results for 'CSRECAP'. [SQLSTATE 01000]
> There are 6500447 rows in 73039 pages for object 'CSRECAP'. [SQLSTATE
01000]
> DBCC results for 'cd_base_item'. [SQLSTATE 01000]
> There are 5492062 rows in 458052 pages for object 'cd_base_item'.
> [SQLSTATE 01000]
> DBCC results for 'MasterDeliveryInfo'. [SQLSTATE 01000]
> There are 275 rows in 2 pages for object 'MasterDeliveryInfo'. [SQLSTATE
> 01000]
> DBCC results for 'CSCSTF'. [SQLSTATE 01000]
> There are 15585869 rows in 833613 pages for object 'CSCSTF'. [SQLSTATE
> 01000]
> DBCC results for 'cd_brand'. [SQLSTATE 01000]
> There are 220167 rows in 2489 pages for object 'cd_brand'. [SQLSTATE
01000]
> DBCC results for 'FinalDeliveryInfo'. [SQLSTATE 01000]
> There are 275 rows in 2 pages for object 'FinalDeliveryInfo'. [SQLSTATE
> 01000]
> DBCC results for 'RCRECF'. [SQLSTATE 01000]
> There are 19468099 rows in 150916 pages for object 'RCRECF'. [SQLSTATE
> 01000]
> DBCC results for 'cd_brand_type'. [SQLSTATE 01000]
> There are 328 rows in 5 pages for object 'cd_brand_type'. [SQLSTATE 01000]
> DBCC results for 'DECTL02F'. [SQLSTATE 01000]
> There are 1252068 rows in 24082 pages for object 'DECTL02F'. [SQLSTATE
> 01000]
> DBCC results for 'cd_category'. [SQLSTATE 01000]
> There are 1961 rows in 23 pages for object 'cd_category'. [SQLSTATE 01000]
> DBCC results for 'cd_cop'. [SQLSTATE 01000]
> There are 347 rows in 6 pages for object 'cd_cop'. [SQLSTATE 01000]
> DBCC results for 'cd_item'. [SQLSTATE 01000]
> There are 2878151 rows in 33269 pages for object 'cd_item'. [SQLSTATE
01000]
> DBCC results for 'cd_item_xref'. [SQLSTATE 01000]
> There are 32862699 rows in 119501 pages for object 'cd_item_xref'.
> [SQLSTATE 01000]
> DBCC results for 'cd_packer_item'. [SQLSTATE 01000]
> There are 2875655 rows in 45046 pages for object 'cd_packer_item'.
> [SQLSTATE 01000]
> DBCC results for 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
> There are 12815617 rows in 2110584 pages for object
> 'import_opco_AESLDF_2004'. [SQLSTATE 01000]
> DBCC results for 'cd_size'. [SQLSTATE 01000]
> There are 1678 rows in 10 pages for object 'cd_size'. [SQLSTATE 01000]
> DBCC results for 'cd_subcategory'. [SQLSTATE 01000]
> There are 5449 rows in 63 pages for object 'cd_subcategory'. [SQLSTATE
> 01000]
> DBCC results for 'cd_supplier'. [SQLSTATE 01000]
> There are 274329 rows in 2633 pages for object 'cd_supplier'. [SQLSTATE
> 01000]
> DBCC results for 'TETRTF'. [SQLSTATE 01000]
> There are 4302 rows in 29 pages for object 'TETRTF'. [SQLSTATE 01000]
> DBCC results for 'cd_synonym'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_synonym'. [SQLSTATE 01000]
> DBCC results for 'SGSMGF'. [SQLSTATE 01000]
> There are 5021 rows in 35 pages for object 'SGSMGF'. [SQLSTATE 01000]
> DBCC results for 'meta_control_AI_import_tbl'. [SQLSTATE 01000]
> There are 0 rows in 4 pages for object 'meta_control_AI_import_tbl'.
> [SQLSTATE 01000]
> DBCC results for 'cd_temp_zone'. [SQLSTATE 01000]
> There are 204 rows in 2 pages for object 'cd_temp_zone'. [SQLSTATE 01000]
> DBCC results for 'ajffctlf_holding_tbl'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'ajffctlf_holding_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'cd_vendor_xref'. [SQLSTATE 01000]
> There are 739871 rows in 3866 pages for object 'cd_vendor_xref'.
> [SQLSTATE 01000]
> DBCC results for 'cd_word'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'cd_word'. [SQLSTATE 01000]
> DBCC results for 'meta_logging_stage_errors'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'meta_logging_stage_errors'.
> [SQLSTATE 01000]
> DBCC results for 'AIDEHF_tbl'. [SQLSTATE 01000]
> There are 1060 rows in 70 pages for object 'AIDEHF_tbl'. [SQLSTATE 01000]
> DBCC results for 'AKDFCTLF'. [SQLSTATE 01000]
> There are 61 rows in 1 pages for object 'AKDFCTLF'. [SQLSTATE 01000]
> DBCC results for 'AIDEHF_holding_tbl'. [SQLSTATE 01000]
> There are 0 rows in 1 pages for object 'AIDEHF_holding_tbl'. [SQLSTATE
> 01000]
> DBCC results for 'meta_control_AIDEHF_errors_tbl'. [SQLSTATE 01000]
> There are 0 rows in 0 pages for object 'meta_control_AIDEHF_errors_tbl'.
> [SQLSTATE 01000]
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'stage'. [SQLSTATE 01000]
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (stage ). [SQLSTATE 01000]
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator. [SQLSTATE 01000]

ConnectionTimeout

I'm creating connection object to SQL Server db this way
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
Vlad
objConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...

> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...

> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?

ConnectionTimeout

I'm creating connection object to SQL Server db this way
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
VladobjConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...

> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?

Connection.Execute & SQL Stored Procedure

I am hopeful that those of you with more experience in the compilation of
.asp, the connection object and MS SQL Server as the DB can help me out. I
have a web app that routinely calls stored procedures in a MS SQL DB.
In one web page, I make 6 calls with the Connection.Execute 'store
procedure'. Five of the six calls execute as expected. However; I have one
call in middle of the 6 that only executes the stored procedure partially.
This particular stored procedure does quite a bit of work within the DB, mak
e
3-4 select quiries and then a few updates to a single record. In this
procedure, one of SQL update statements is constructed in a loop to ensure
the appropriate fields are updated and no others. (The fields updated vary
with the input being used) This is where the procedure will "time out". I
have reduce/elimnated all the "debuggin" code that is no esential to the
store procedure. When the dubbuging print and select statements commented
out the procedure will execute further when called from the asp page.
Has anyone run across a situation where the .asp does not allow a stored
procedure to execute to completion before executing the next line of .asp
code? If so is there a way to force the Connection.Execute call to wait unti
l
the SQL server stored procedure has completed executing before processing th
e
next line of asp? Note the stored procedure does not explicitly return
anything to the asp code... also noteworthy, if the same stored procedure is
run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
procedure runs flawlessly and as expected to completion...
Anyone have an idea?Simon McLaren wrote:
> I am hopeful that those of you with more experience in the
> compilation of .asp, the connection object and MS SQL Server as the
> DB can help me out. I have a web app that routinely calls stored
> procedures in a MS SQL DB.
This would have been more on topic at .inetserver.asp.db. However, read on.

> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I
> have one call in middle of the 6 that only executes the stored
> procedure partially. This particular stored procedure does quite a
> bit of work within the DB, make 3-4 select quiries and then a few
> updates to a single record. In this procedure, one of SQL update
> statements is constructed in a loop to ensure the appropriate fields
> are updated and no others. (The fields updated vary with the input
> being used) This is where the procedure will "time out". I have
> reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements
> commented out the procedure will execute further when called from the
> asp page.
>
There are two timeout properties to be concerned with:
1. The IIS ScriptTimeout property, which controls the max time a page will
be allowed to execute on the server. This can be set globally using IIS
Manager to modify your website's application properties. It can also be set
at the page level using:
Server.ScriptTimeout = <some reasonable value>
2. The ADO Connection's CommandTimeout property. This controls the amount of
time a Command will allow a statement to execute. If set for a longer period
of time than the ScriptTimeout, the script may time out before the command
finishes executing.This is set via:
dim cn
set cn=createobject("adodb.connection")
cn.open ...
cn.CommandTimeout = <some reasonable value>
You should go to msdn.microsoft.com/library and read the documentation about
these properties.
Having said that, I need to add: Why call 6 procedures from ASP? One of the
main benefits of procedures is encapsulation, a huge benefit of which is
reducing the number of round trips to the database. You should create a new
procedure which accepts all the arguments required to call the other 6
procedures. This procedure calls the 6 procedures in turn and then returns
the overall result to the ASP client. One trip to the database. This can be
a huge performance booster.
I also need to add: you need to do some work to optimize the procedures you
have. You should not be allowing an asp page to time out waiting for
database activity to complete. Is there any way to take that time-consuming
activity offline?
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||I'll try to answer to your question as much as I understood it.
First of all, ASP is not compiled, it is interpreted language. Ok, this has
nothing with your question but I had to say it :)
ADO Connection/Command object has CommandTimeout property which defines for
how long it will wait for SP to execute.
I did not work with ASP and ADO for a long time. I had that "luck" to switch
to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
advice you to:
-Remove print commands from your SP, it may confuse ADO
-Set "set nocount on" at the beging of your code
-Close connection as soon as you finish you finish your work with DB
-Use SQL OLE DB provider for SQL Server
http://msdn.microsoft.com/library/d...
qlprovspec.asp
I hope this help
Regards,
Marko Simic
"Simon McLaren" wrote:

> I am hopeful that those of you with more experience in the compilation of
> .asp, the connection object and MS SQL Server as the DB can help me out. I
> have a web app that routinely calls stored procedures in a MS SQL DB.
> In one web page, I make 6 calls with the Connection.Execute 'store
> procedure'. Five of the six calls execute as expected. However; I have one
> call in middle of the 6 that only executes the stored procedure partially.
> This particular stored procedure does quite a bit of work within the DB, m
ake
> 3-4 select quiries and then a few updates to a single record. In this
> procedure, one of SQL update statements is constructed in a loop to ensure
> the appropriate fields are updated and no others. (The fields updated vary
> with the input being used) This is where the procedure will "time out".
I
> have reduce/elimnated all the "debuggin" code that is no esential to the
> store procedure. When the dubbuging print and select statements commented
> out the procedure will execute further when called from the asp page.
> Has anyone run across a situation where the .asp does not allow a stored
> procedure to execute to completion before executing the next line of .asp
> code? If so is there a way to force the Connection.Execute call to wait un
til
> the SQL server stored procedure has completed executing before processing
the
> next line of asp? Note the stored procedure does not explicitly return
> anything to the asp code... also noteworthy, if the same stored procedure
is
> run from the SQL Query Analyzer (a tool for MS SQL Server) the stored
> procedure runs flawlessly and as expected to completion...
> Anyone have an idea?
>|||Bob and Simic,
Thanks for taking the time to respond. Bob to answer you question about why
the 6 SP calls... Originally it the six where all one procedure.
Progromatically, the six procedures achimplish "steps" of a process, all of
which in this application could be (and are) occassionally executed
independently. I have created an encapsulating SP that in turn calls the 6
seperate SP's, but this non-completion issue forced me to back away from tha
t
idea to try and isolate the "offending" procedure.
The Connection/Command timeouts have been set to 60seconds. This failure
happens much quicker than 60 seconds.
By trial and error last w I tried commenting out all "debugging" code (to
include print and select statement indented to allow me to see what was
happening and if dynamicly generated SQL statements where correct.) in the
offending stored procedure, figuring not that it might be confusing ADO, but
rather that is was adding to the execution time of the stored procedure. An
d
presto, execution to completion for the .asp page and no debuggin "insight"
from the query analyzer.
While I "stumbled" across the issue on my own, it was not until reading your
responses that I understood what the actual issue was. With "debugging code
"
commented out of the stored procedures, I believe I can move back to a singl
e
SP and trip to the database.
Thanks,
Simon
"Simic Marko" wrote:
> I'll try to answer to your question as much as I understood it.
> First of all, ASP is not compiled, it is interpreted language. Ok, this ha
s
> nothing with your question but I had to say it :)
> ADO Connection/Command object has CommandTimeout property which defines fo
r
> how long it will wait for SP to execute.
> I did not work with ASP and ADO for a long time. I had that "luck" to swit
ch
> to .NET (c#) and ADO.NET, but from my expirience with ASP adn SPs I would
> advice you to:
> -Remove print commands from your SP, it may confuse ADO
> -Set "set nocount on" at the beging of your code
> -Close connection as soon as you finish you finish your work with DB
> -Use SQL OLE DB provider for SQL Server
> http://msdn.microsoft.com/library/d...sqlprovspec.asp
> I hope this help
> Regards,
> Marko Simic
>
> "Simon McLaren" wrote:
>

Connection.Command.Text length

Is there a way to set the length of my command text? I have a very large
script I am trying to pass through the adodb.connection object, and it keeps
getting truncated.Hi
How large?
Regards
Mike
"Mike Morella" wrote:

> Is there a way to set the length of my command text? I have a very large
> script I am trying to pass through the adodb.connection object, and it kee
ps
> getting truncated.|||Here is the script. It is 248 lines long.
declare @.cmd nvarchar(1024)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmplg]'))
drop table #tmplg
CREATE TABLE #tmplg
(
DBName varchar(10),
LogSize real,
LogSpaceUsed real,
Status int
)
SELECT @.cmd = 'dbcc sqlperf (logspace)'
INSERT INTO #Tmplg EXECUTE (@.cmd)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_stats]'))
drop table #tmp_stats
create table #tmp_stats (
totalextents int,
usedextents int,
dbname varchar(10),
logsize real,
logspaceused real
)
go
use [aw]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'aw'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'aw'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'aw'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [css]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'css'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'css'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'css'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [master]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'master'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'master'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'master'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [model]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'model'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'model'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'model'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [msdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'msdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'msdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'msdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [rms]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'rms'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'rms'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'rms'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [sbb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'sbb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'sbb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'sbb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [tempdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'tempdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'tempdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'tempdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
SELECT dbname,totalextents*64/1024 as dbSize, usedextents*64/1024 as
dbSpaceUsed ,logsize ,logSpaceUsed from #tmp_stats
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> How large?
> Regards
> Mike
> "Mike Morella" wrote:
>|||Mike Morella (MikeMorella@.discussions.microsoft.com) writes:
> Here is the script. It is 248 lines long.
You pass all that in one call to cmd.Execute? Truncation or not, you
will get hoops of syntax errors, like here:

> create table #tmp_stats (
> totalextents int,
> usedextents int,
> dbname varchar(10),
> logsize real,
> logspaceused real
> )
> go
You can't have an identifier directly after a CREATE TABLE commmand, and
you have plenty of these "go".
Yes, I know what you are thinking, but GO is not an SQL command. As far
as SQL command that "go" is just another identifier. Lots of client tools
uses "go" on a separate line as a batch terminator. So could you, but you
need to break the input in batches on the "go" before you send it to SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connection.Command.Text length

Is there a way to set the length of my command text? I have a very large
script I am trying to pass through the adodb.connection object, and it keeps
getting truncated.
Hi
How large?
Regards
Mike
"Mike Morella" wrote:

> Is there a way to set the length of my command text? I have a very large
> script I am trying to pass through the adodb.connection object, and it keeps
> getting truncated.
|||Here is the script. It is 248 lines long.
declare @.cmd nvarchar(1024)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmplg]'))
drop table #tmplg
CREATE TABLE #tmplg
(
DBName varchar(10),
LogSize real,
LogSpaceUsed real,
Status int
)
SELECT @.cmd = 'dbcc sqlperf (logspace)'
INSERT INTO #Tmplg EXECUTE (@.cmd)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_stats]'))
drop table #tmp_stats
create table #tmp_stats (
totalextents int,
usedextents int,
dbname varchar(10),
logsize real,
logspaceused real
)
go
use [aw]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'aw'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'aw'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'aw'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [css]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'css'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'css'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'css'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [master]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'master'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'master'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'master'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [model]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'model'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'model'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'model'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [msdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'msdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'msdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'msdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [rms]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'rms'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'rms'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'rms'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [sbb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'sbb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'sbb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'sbb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [tempdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'tempdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'tempdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'tempdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
SELECT dbname,totalextents*64/1024 as dbSize, usedextents*64/1024 as
dbSpaceUsed ,logsize ,logSpaceUsed from #tmp_stats
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> How large?
> Regards
> Mike
> "Mike Morella" wrote:
|||Mike Morella (MikeMorella@.discussions.microsoft.com) writes:
> Here is the script. It is 248 lines long.
You pass all that in one call to cmd.Execute? Truncation or not, you
will get hoops of syntax errors, like here:

> create table #tmp_stats (
> totalextents int,
> usedextents int,
> dbname varchar(10),
> logsize real,
> logspaceused real
> )
> go
You can't have an identifier directly after a CREATE TABLE commmand, and
you have plenty of these "go".
Yes, I know what you are thinking, but GO is not an SQL command. As far
as SQL command that "go" is just another identifier. Lots of client tools
uses "go" on a separate line as a batch terminator. So could you, but you
need to break the input in batches on the "go" before you send it to SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Connection to SQL2005 Database Engine

On one server I connect to I always get this message when I try to launch object explorer.
TITLE: Microsoft SQL Server Management Studio
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Insert Error :column name or number of supplied values doesn't match.. Error:213.

Sometimes I can run refresh, and it will eventually connect.

Is the version of sql server you're connecting to the same as the one that was installed where you're launching SSMS? If not, and depending on the versions, underlying procs that SSMS depend on may be different between the two versions.
|||The server is SQL Standard Sept. release, and I installed the same version, workstation only on my station. I eventually get the browser to work. Other server is a June release std and it doesn't do this. I reinstalled every workstation component, and now I have SSIS working but still getting the browser error looking at objects on this server. BTW this server has been installed a couple of times .|||I found out that I had some possible old components in the install dir, so I started over again with fresh Sept install. When I did that , I noticed again my SQL2000 Ent Mgr quit working as I uninstalled. Prev. I had repatched SQL sp4 then reran setup of 2005. This time I left it alone , finished up install, and lo and behold the object browser works AND SQL 2000 Ent. MGr. What I am trying to deduce is the sqldmo dll might be involved . Does SQL2005 install replace that in the Windows directory , and it happens to have backwards compatibility so the 2000 still can use it ? Here is a link to the article. All is right for the moment, I shall cherish it at least the SQL2005 world a bit better.

http://www.aspfaq.com/sql2005/show.asp?id=4

connection string question

Being new to .net but not new to classic asp I have a question about the connection object.

I'd like to be able to reference the same connection object with any SQL command on my code behind page, but it seems like it closes after the first command is completed. I get this error:

ExecuteReader requires an open and available Connection. The connection's current state is closed.

Below is the code I'm using, this is just basic stuff for a test. Any help would be appreciated Oh, by the way, when I did this with JUST the first Sub being called it worked fine.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Partial Class AZone_ConnectionTest
Inherits System.Web.UI.Page

Public con As New SqlConnection(WebConfigurationManager.ConnectionStrings("Conn").ConnectionString)

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
con.Open()
FirstLabel()
SecondLabel()
con.Close()
End Sub

Sub FirstLabel()
' command
Dim cmd As New SqlCommand
cmd.Connection = con
cmd.CommandText = "select top 1 PropertyName from tblProperties"

' using con
Using con
Dim r As SqlDataReader = cmd.ExecuteReader
r.Read()
lblFirst.Text = r("PropertyName")
r.Close()
End Using
' clear command
cmd.Dispose()
End Sub

Sub SecondLabel()
' command
Dim cmd As New SqlCommand
cmd.Connection = con
cmd.CommandText = "SELECT top 1 DevID FROM tblProperties"

' using con
Using con
Dim r As SqlDataReader = cmd.ExecuteReader
r.Read()
lblSecond.Text = r("DevID")
r.Close()
End Using
' clear command
cmd.Dispose()
End Sub

End Class

You are calling the connection using a directive (no pun intended).

When you use a directive the object gets diposed at the end of the scopoe. hence, your statement End Using. meanind the connection is now closed at this point.

|||

I'm not very familiar with the syntax of Visual Basic.NET, but most often it is extremely like C#. In C# (and I believe it is the same with VB.NET) the using statement will dispose the object at the end. Thus the following:

using (SqlConnection connection =new SqlConnection(myConnectionString)){ connection.Open(); ...//Do stuff with the connection here.}

Would in fact be the same as writing:

try{ SqlConnection connection =new SqlConnection(myConnectionString); connection.Open(); ...//Do stuff with the connection here.}finally{ ((IDisposable)connection).Dispose();//Which effectively will close the connection}

The call to Dispose in the end of theusing statement will thus close the connection.|||

ExecuteReader closes its connection automatically when it reaches end of file. Strictly speaking though the connection was actually returned to a pool of connections for use by another read request. Provided all the connection strings are defined exactly the same, connections are pooled automatically. Thus you do not need to (and should not) define a connection globally. In this way ADO.NET becomes much faster than ADO in connections.

|||

OK, well in the second sub I tried opening the connection again and got THIS message

The ConnectionString property has not been initialized.

|||

The object got disposed, there is no reference to an object to open.

Take out the using statements, your not using them correctly.

|||

Thanks, that did the trick (removing 'using') can you explain what "using" is for?

|||

The using statement is there to make sure that the object is disposed, to make sure you don't end up forgetting to, for example, close a connection to a database. Look at my previous post and you'll see what the using statement actually does.

|||

In addition, look up the term "Directives" in .Net, this will clear it up for you.

|||

thanks, I appreciate everyone's help and information

Connection problem with database and calendar

hi folks:

imports system.data

Private schedule As New DataView()

Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
schedule = CType(srcSchedule.Select(DataSourceSelectArguments.Empty), DataView)
schedule.Sort = "entryDate"
End Sub
///////////////////////
whenever i run the above code an error message comes up with the following message:

Object reference not set to an instance of an object.
and it highlights the bold message mention in the code.

I wanted this code to highlight the dates in the calendar that have events on them.

Can you please help. thank you very much in advance.

Hi

srcSchedule might be null when program run into the bold line.

You can debug that line to check out.

Hope it helps.