Metainformationen zur Seite
  •  

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
firma:compas:compass [2025/10/17 09:43] – ↷ Seite von tipps:compas:compass nach firma:compas:compass verschoben uwe-handzikfirma:compas:compass [2025/10/17 11:55] (aktuell) – ↷ Links angepasst, weil Seiten im Wiki verschoben wurden 47.128.31.176
Zeile 1704: Zeile 1704:
 ====InsertDocumentsIntoAllLocations ====InsertDocumentsIntoAllLocations
  
-{{collapse(SQL Script ...) +{{tipps:compas:collapse_sql_script_..._file_file_--_--_number_of_documents_with_missing_links_---_select_count_from_view_all_document_where_select_count_from_xref_element_where_relationship_id_aim.xref.doc.location_2_--_--_--_link_all_documents_to_all_locations_--_--_declare_alldocs_cursor_local_forward_only_for_select_aimkey_from_document_declare_doc_aimkey_numeric_17_5_open_alldocs_fetch_next_from_alldocs_into_doc_aimkey_while_fetch_status_0_begin_declare_dest_loc_cursor_local_forward_only_for_select_aimkey_from_location_declare_loc_aimkey_numeric_17_5_open_dest_loc_fetch_next_from_dest_loc_into_loc_aimkey_while_fetch_status_0_begin_if_select_count_from_xref_element_where_parent_aimkey_doc_aimkey_and_child_aimkey_loc_aimkey_0_begin_insert_into_xref_element_parent_aimkey_child_aimkey_relationship_id_owner_owner_group_values_doc_aimkey_loc_aimkey_aim.xref.doc.location_administrator_engineering_employees_end_fetch_next_from_dest_loc_into_loc_aimkey_end_close_dest_loc_deallocate_dest_loc_fetch_next_from_alldocs_into_doc_aimkey_end_close_alldocs_deallocate_alldocs_go
- +
-<file> +
- +
-</file> +
--- +
--- Number of documents with missing links +
---- +
-select count(*) from VIEW_ALL_DOCUMENT +
-where (select count(*) from XREF_ELEMENT +
- where RELATIONSHIP_ID='AIM.XREF.DOC.LOCATION')<2 +
--- +
--- +
--- Link all documents to all locations +
--- +
--- +
-DECLARE alldocs CURSOR LOCAL FORWARD_ONLY FOR SELECT AIMKEY FROM DOCUMENT +
-DECLARE @doc_aimkey  NUMERIC(17,5) +
- +
-OPEN alldocs +
- +
-FETCH NEXT FROM alldocs INTO @doc_aimkey +
- +
-WHILE @@FETCH_STATUS = 0 +
-BEGIN +
-   DECLARE dest_loc CURSOR LOCAL FORWARD_ONLY FOR SELECT AIMKEY FROM LOCATION +
-   DECLARE @loc_aimkey  NUMERIC(17,5) +
- +
-   OPEN dest_loc +
- +
-   FETCH NEXT FROM dest_loc INTO @loc_aimkey +
- +
-   WHILE @@FETCH_STATUS = 0 +
-      BEGIN +
-         if ((select count(*) from XREF_ELEMENT  +
-              where PARENT_AIMKEY=@doc_aimkey and CHILD_AIMKEY=@loc_aimkey)=0) +
-         begin +
-           INSERT INTO XREF_ELEMENT (PARENT_AIMKEY, CHILD_AIMKEY, RELATIONSHIP_ID, OWNER, OWNER_GROUP) +
-                           VALUES(@doc_aimkey, @loc_aimkey, 'AIM.XREF.DOC.LOCATION', 'Administrator', 'ENGINEERING_EMPLOYEES') +
-         end +
-         FETCH NEXT FROM dest_loc INTO @loc_aimkey +
-       END +
- +
-       CLOSE dest_loc +
-       DEALLOCATE dest_loc +
- +
-     FETCH NEXT FROM alldocs INTO @doc_aimkey +
- +
-  END  +
- +
-  CLOSE alldocs +
-  DEALLOCATE alldocs +
-  GO+
  
  
Zeile 1767: Zeile 1715:
 ====Repl Status ====Repl Status
  
-{{collapse(SQL Script ...) +{{tipps:compas:collapse_sql_script_..._file_print_anzahl_aktueller_pushauftraege_pro_ziel-standort_--_--_--_select_destlocation_count_from_replicatorq_where_replication_type_like_4_group_by_destlocation_--_--_--_print_print_anzahl_aktueller_pushauftraege_pro_quell-standort_--_select_sourcelocation_count_from_replicatorq_where_replication_type_like_4_group_by_sourcelocation_--_--_--_print_print_anzahl_aktueller_pushauftraege_pro_quell-standort_--_select_destlocation_count_from_replicatorq_where_replication_type_like_8_and_destlocation_sourcelocation_group_by_destlocation_file
- +
-<file> +
-PRINT 'Anzahl aktueller Pushaufträge pro Ziel-Standort' +
--- +
--- +
--- +
-SELECT DESTLOCATION, COUNT(*) +
- FROM   REPLICATORQ +
- WHERE (REPLICATION_TYPE LIKE '%4') +
- GROUP BY DESTLOCATION +
--- +
--- +
--- +
-PRINT '' +
-Print 'Anzahl aktueller Pushaufträge pro Quell-Standort' +
--- +
-SELECT SOURCELOCATION, COUNT(*)  +
- FROM   REPLICATORQ +
- WHERE (REPLICATION_TYPE LIKE '%4') +
- GROUP BY SOURCELOCATION +
--- +
--- +
--- +
-PRINT '' +
-Print 'Anzahl aktueller Pushaufträge pro Quell-Standort' +
--- +
-SELECT DESTLOCATION, COUNT(*) +
- FROM         REPLICATORQ +
- WHERE     (REPLICATION_TYPE LIKE '%8') AND (DESTLOCATION <> SOURCELOCATION) +
- GROUP BY DESTLOCATION +
-</file>+
  
 }} }}
Zeile 1806: Zeile 1723:
 ====Einsame IPTs ====Einsame IPTs
  
-{{collapse(SQL Script ...) +{{tipps:compas:collapse_sql_script_..._file_select_from_view_document_engineering_where_aimkey_not_in_select_parent_aimkey_from_xref_element_where_relationship_id_aim.xref.doc.eng_and_aimkey_not_in_select_child_aimkey_from_xref_element_where_relationship_id_aim.xref.doc.eng_and_file_type_like_i_file
- +
-<file> +
-SELECT * FROM VIEW_DOCUMENT_ENGINEERING  +
-WHERE  +
-(AIMKEY not in (select PARENT_AIMKEY from XREF_ELEMENT where RELATIONSHIP_ID='AIM.XREF.DOC.ENG'))  +
-AND (AIMKEY not in (select CHILD_AIMKEY from XREF_ELEMENT where RELATIONSHIP_ID='AIM.XREF.DOC.ENG')) +
-AND (FILE_TYPE like 'I%') +
- +
-</file>+
  
 }} }}
Zeile 1823: Zeile 1731:
 ====Aufheben der Replikation ====Aufheben der Replikation
  
-{{collapse(SQL Script ...) +{{tipps:compas:collapse_sql_script_..._file_--_aufheben_der_replikation_--_drop_rowguid_indexes_select_drop_index_sysobjects.name_._sysindexes.name_from_sysindexes_inner_join_sysobjects_on_sysindexes.id_sysobjects.id_where_objectproperty_object_id_sysobjects.name_ismsshipped_0_and_sysindexes.indid_0_and_sysindexes.indid_255_and_sysindexes.status_64_0_and_index_col_sysobjects.name_sysindexes.indid_1_rowguid_order_by_sysindexes.indid_--_remove_rowguid_default_constraints_select_alter_table_b.name_drop_constraint_a.name_from_sysobjects_a_inner_join_syscolumns_on_syscolumns.id_a.parent_obj_inner_join_sysobjects_b_on_syscolumns.id_b.id_where_syscolumns.name_rowguid_and_objectproperty_object_id_b.name_ismsshipped_0_and_a.xtype_d_--_remove_rowguid_columns_select_alter_table_sysobjects.name_drop_column_rowguid_from_syscolumns_inner_join_sysobjects_on_syscolumns.id_sysobjects.id_where_syscolumns.name_rowguid_and_objectproperty_object_id_sysobjects.name_ismsshipped_0_file
- +
-<file> +
--- aufheben der Replikation +
- +
--- drop rowguid indexes +
-select 'drop index ' + sysobjects.name + '.' + sysindexes.name from +
-sysindexes +
-inner join sysobjects +
-on sysindexes.id = sysobjects.id +
-where objectproperty(object_id(sysobjects.name),'IsMSShipped') = 0 +
-and sysindexes.indid > 0 and sysindexes.indid < 255 and (sysindexes.status & +
-64)=0 +
-and index_col(sysobjects.name, sysindexes.indid, 1) = 'rowguid' +
-order by sysindexes.indid +
- +
--- remove rowguid default constraints +
-select 'alter table ' + b.name + ' drop constraint ' + a.name from +
-sysobjects a +
-inner join syscolumns on syscolumns.id = a.parent_obj +
-inner join sysobjects b on syscolumns.id = b.id +
-where syscolumns.name = 'rowguid' +
-and objectproperty(object_id(b.name),'IsMSShipped') = 0 +
-and a.xtype = 'D' +
- +
--- remove rowguid columns +
-select 'alter table ' + sysobjects.name + ' drop column ''rowguid'' ' from +
-syscolumns +
-inner join sysobjects on syscolumns.id = sysobjects.id +
-where syscolumns.name = 'rowguid' +
-and objectproperty(object_id(sysobjects.name),'IsMSShipped') = 0  +
-</file>+
  
 }} }}