Plecenie do tworzenia bazy danych BaseLine dla Dynamics AX 2012 :
Initialize-AXModelStore -Server OPTSBR2\SQL2012 -Database AXDB_Baseline -Verbose
piątek, 27 września 2013
wtorek, 12 marca 2013
Mój stary Job jeszcze z czasów AX3.0.
Umożliwia on wyszukanie w bazie danych w jakich tabelach i polach znajduje się konkretna wartość.
Przykładowo chcemy wyszukać w jakich tabelach jest pole o typie Voucher (niezależnie od jego nazwy, uwzględniając typy podrzędne) o wartości 'VOU0012345'.
Umożliwia on wyszukanie w bazie danych w jakich tabelach i polach znajduje się konkretna wartość.
Przykładowo chcemy wyszukać w jakich tabelach jest pole o typie Voucher (niezależnie od jego nazwy, uwzględniając typy podrzędne) o wartości 'VOU0012345'.
static void SBR_FindValue_v1_1(Args _args) { str func; Dictionary dict; DictTable dictTable; DictField dictField; int tableCnt; int fieldCnt; Dialog dialog = new dialog(); Dialogfield dfLookFor; Dialogfield dfLookForType; Name lookFor; int lookForTypeId; Set setTypes; ExtendedDataTypeName lookForType; xRefTypeHierarchy xRefTypeHierarchyBase; Query query; QueryBuildDataSource qbds; QueryBuildRange qbr; QueryRun qr; Set fillList(Set _set, xRefTypeHierarchy _xRefTypeHierarchy, boolean _forward = true) { xRefTypeHierarchy __xRefTypeHierarchy; int __id; if (_forward) { __id = _xRefTypeHierarchy.Id; select __xRefTypeHierarchy where __xRefTypeHierarchy.BaseType == _xRefTypeHierarchy.BaseType && __xRefTypeHierarchy.Parent == __id; } else { __id = _xRefTypeHierarchy.Parent; select __xRefTypeHierarchy where __xRefTypeHierarchy.BaseType == _xRefTypeHierarchy.BaseType && __xRefTypeHierarchy.Id == __id; } while (__xRefTypeHierarchy) { _set.add(__xRefTypeHierarchy.Id); fillList(_set, __xRefTypeHierarchy, _forward); next __xRefTypeHierarchy; } return _set; } ; func = "int getExtendedTypeNum() {\n"; func = func + " ;\n"; func = func + " return extendedTypeNum(\'%1\');\n"; func = func + "}\n"; dict = new Dictionary(); dialog.caption('Looking for...'); dfLookForType = dialog.addField(typeid(ExtendedDataTypeName), 'Type'); dfLookFor = dialog.addField(typeid(Name), 'Value'); if (!dialog.run()) return; lookForType = dfLookForType.value(); lookFor = dfLookFor.value(); lookForTypeId = runBuf(StrFmt(func, lookForType)); setTypes = new Set(Types::Integer); if (lookFor == '' || lookForType == '') throw error('Input parameters cannot be empty!'); setPrefix(strFmt('Searching value \'%1\' found in:', lookFor)); select xRefTypeHierarchyBase where xRefTypeHierarchyBase.Name == lookForType && xRefTypeHierarchyBase.Id == lookForTypeId; if (xRefTypeHierarchyBase) { setTypes = fillList(setTypes, xRefTypeHierarchyBase); setTypes = fillList(setTypes, xRefTypeHierarchyBase, false); } setTypes.add(lookForTypeId); StartLengthyOperation(); for(tableCnt = 1; tableCnt <= dict.tableCnt(); tableCnt++) { dictTable = new DictTable( dict.tableCnt2Id(tableCnt) ); if (dictTable.isTmp() || dictTable.isView() || dictTable.isMap()) continue; for(fieldCnt = 1; fieldCnt <= dictTable.fieldCnt(); fieldCnt++) { dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(fieldCnt)); if (dictField.typeId() > 0 && setTypes.in(dictField.typeId())) { infolog.messageWin().addLine(strFmt('searching %1 in %2(%3)', dictField.baseType(), dictTable.name(), dictField.name())); query = new Query(); qbds = query.addDataSource(dictTable.id()); qbds.firstOnly(true); qbr = qbds.addRange(dictField.id()); qbr.value(queryValue(lookFor)); qr = new QueryRun(query); if (qr.next()) { infolog.messageWin().addLine('FOUND!'); info(strFmt('Table = %1, Field = %2', dictTable.name(), dictField.name())); } } } } EndLengthyOperation(); }
Active sessions and locks
-- active requests SELECT r.session_id, se.host_name, se.login_name, Db_name(r.database_id) AS dbname, r.status, r.command, r.cpu_time, r.total_elapsed_time, r.reads, r.logical_reads, r.writes, s.text sql_text, p.query_plan query_plan, SQL_CURSORSQL.text, SQL_CURSORPLAN.query_plan FROM sys.dm_exec_requests r INNER JOIN sys.dm_exec_sessions se ON r.session_id = se.session_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN WHERE r.session_id <> @@SPID AND se.is_user_process = 1
-- active locks SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS database_name, O.Name AS locked_object_name, P.object_id AS locked_objectId, L.resource_type AS locked_resource, L.request_mode AS lock_type, ST.text AS sql_statement_text, ES.login_name AS login_name, ES.host_name AS [host_name], TST.is_user_transaction as is_user_transaction, AT.name as transaction_name, CN.auth_scheme as authentication_method FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id
Etykiety:
SKRYPTY SQL,
SQL
poniedziałek, 11 marca 2013
Unused/Missing indexes
-- unused indexes SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName, OBJECT_NAME(I.OBJECT_ID) AS ObjectName, I.NAME AS IndexName FROM sys.indexes I WHERE -- only get indexes for user created tables OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 -- find all indexes that exists but are NOT used AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id -- limit our query only for the current db AND database_id = DB_ID()) AND I.index_id != 0 AND I.is_primary_key = 0 AND I.is_unique_constraint = 0 -- AND I.is_unique = 0 ORDER BY SchemaName, ObjectName, IndexName
-- rarely used indexes SELECT O.name AS object_name, I.name AS index_name, I.index_id AS index_id, DM_IUS.user_seeks AS user_seeks, DM_IUS.user_scans AS user_scans, DM_IUS.user_lookups AS user_lookups, DM_IUS.user_updates AS user_updates, P.table_rows as table_rows, DM_IUS.last_user_seek, DM_IUS.last_user_scan, DM_IUS.last_user_lookup, DM_IUS.last_user_update FROM sys.dm_db_index_usage_stats DM_IUS INNER JOIN sys.indexes I ON I.index_id = DM_IUS.index_id AND DM_IUS.OBJECT_ID = I.OBJECT_ID INNER JOIN sys.objects O ON DM_IUS.OBJECT_ID = O.OBJECT_ID INNER JOIN sys.schemas S ON O.schema_id = S.schema_id INNER JOIN (SELECT SUM(P.rows) table_rows, P.index_id, P.OBJECT_ID FROM sys.partitions P GROUP BY P.index_id, P.OBJECT_ID) P ON P.index_id = DM_IUS.index_id AND DM_IUS.OBJECT_ID = P.OBJECT_ID WHERE OBJECTPROPERTY(DM_IUS.OBJECT_ID,'IsUserTable') = 1 AND DM_IUS.database_id = DB_ID() AND I.type_desc = 'nonclustered' AND I.is_primary_key = 0 AND I.is_unique_constraint = 0 ORDER BY (DM_IUS.user_seeks + DM_IUS.user_scans + DM_IUS.user_lookups) ASC
-- used indexes stats SELECT DB_NAME(C.database_id) AS database_name, OBJECT_NAME(C.object_id, C.database_id) AS table_name, A.avg_user_impact * (A.user_seeks + A.user_scans) avg_estimated_impact, A.avg_total_user_cost * (A.user_seeks + A.user_scans) avg_estimated_total_user_cost, A.unique_compiles, A.user_seeks, A.last_user_seek, DATEPART(d, GETDATE() - A.last_user_seek) as last_user_seek_days_ago, A.user_scans, A.last_user_scan, DATEPART(d, GETDATE() - A.last_user_scan) as last_user_scan_days_ago, A.avg_total_user_cost, A.avg_user_impact, C.equality_columns, C.inequality_columns, C.included_columns FROM sys.dm_db_missing_index_group_stats AS A INNER JOIN sys.dm_db_missing_index_groups AS B ON A.group_handle = B.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS C ON B.index_handle = C.index_handle --WHERE --DATEPART(hh, last_user_seek) >= 6 AND DATEPART(hh, last_user_seek) <= 20 AND -- run in work hours --DATEPART(d, GETDATE() - A.last_user_seek) = 1 -- run today ORDER BY avg_estimated_total_user_cost DESC
Etykiety:
SKRYPTY SQL,
SQL
wtorek, 5 marca 2013
Create ledger journal from code (using LedgerJournalEngine)
static void SBR_Create_Ledger_Journal(Args _args) { #define.JournalName('KOST12') LedgerJournalName ledgerJournalName; LedgerJournalTable ledgerJournalTable; LedgerJournalTrans ledgerJournalTrans; LedgerJournalId journalId; Amount amount; LedgerJournalEngine engine; ; ttsBegin; ledgerJournalName = LedgerJournalName::find(#JournalName); ledgerJournalTable.clear(); ledgerJournalTable.JournalName = ledgerJournalName.JournalName; ledgerJournalTable.initFromLedgerJournalName(); ledgerJournalTable.insert(); journalId = ledgerJournalTable.JournalNum; // Create Trans engine = LedgerJournalEngine::construct(ledgerJournalName.JournalType); engine.newJournalActive(ledgerJournalTable); ledgerJournalTrans.clear(); ledgerJournalTrans.initValue(); ledgerJournalTrans.JournalNum = journalId; engine.initValue(ledgerJournalTrans); // account ledgerJournalTrans.AccountType = LedgerJournalACType::Cust; engine.accountNumModified(ledgerJournalTrans); ledgerJournalTrans.AccountNum = '10058'; engine.accountModified(ledgerJournalTrans); // offset account ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger; engine.offsetAccountTypeModified(ledgerJournalTrans); ledgerJournalTrans.OffsetAccount = '22500'; engine.offsetAccountModified(ledgerJournalTrans); // dimensions ledgerJournalTrans.Dimension[1] = ''; ledgerJournalTrans.Dimension[2] = ''; ledgerJournalTrans.Dimension[3] = ''; engine.initDimension(ledgerJournalTrans); // amount amount = 100; ledgerJournalTrans.AmountCurCredit = (amount > 0 ? abs(amount) : 0); if (ledgerJournalTrans.AmountCurCredit) engine.amountCurCreditModified(ledgerJournalTrans); ledgerJournalTrans.AmountCurDebit = (amount < 0 ? abs(amount) : 0); if (ledgerJournalTrans.AmountCurDebit) engine.amountCurDebitModified(ledgerJournalTrans); ledgerJournalTrans.insert(); pause; ttsCommit; info(strFmt('created journal %1', journalId)); }
Subskrybuj:
Posty (Atom)