We are doing this project where it is required that the attachments are stored into a folder on the HDD now thatz a default configurable parameter in AX the complication is that in this case we are expecting the attachment size to be huge so much so that it exceeds the storage capacity of the server.
Well in the modern world no one takes a decision of making a change in the system to accomodate for such a scenario as the easiest option is to increase the storage space but in this case it seems that we do not have an option to further expand the storage on the server and once the server disk is filled up the only option is to either get a new server which has more slots to push more HDD's or to backup the files onto a different server.
AX allows us to configure a folder path for each file type but the issue is that it only maintains the file name in the transaction tables and the path is maintained in the parameter table which means if the path is changed at a later date AX will not be able to trace the old attachements coz when those were made the path was different
I found where my attachments for a customer were being stored and realized that the path was being referred to from the parameters table and the full file name with path was being build in a function in the docuref table called completeFileName
select DV.FileName, DV.FileType, DR.* from docuref DR
inner join docuvalue DV
on DR.ValueRecId = DV.RecId
where RefTableId = 77
and PartyId = 191
so the solution in this case is that we decide to store the file path with the file when the attachments are made so that the system remembers multiple paths for the files.
Monday, April 19, 2010
Saturday, April 10, 2010
Create Clustered Index Script
I had this requirement where we identified that a no of tables in AXapta were missing the clustered index which was resulting in some performance issues. It was then recommended by Microsoft to create clustered indexes on all these tables this is what we did.
--list of all tables missing an clustered index
select SO.name, SI.*
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
--script to generate the script
select 'create clustered index [IDX_' + SO.name + '] on [' + SO.name + '] (recid, dataareaid )'
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
another important query is to list all the columns in a index in this case clustered index
--list of columns in the clustered index
select OBJECT_NAME(SI.id) TableName, SI.name IndexName, SIK.indid IndexId, SC.name ColName
from sysindexes SI
inner join sysindexkeys SIK
on SI.id = SIK.id
and SI.indid = SIK.indid
inner join syscolumns SC
on SC.id = SIK.id
and SC.colid = SIK.colid
where SIK.id = OBJECT_ID( 'ContactMapping' )
and SIK.indid = 1
order by SIK.indid, SC.colorder
--list of all tables missing an clustered index
select SO.name, SI.*
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
--script to generate the script
select 'create clustered index [IDX_' + SO.name + '] on [' + SO.name + '] (recid, dataareaid )'
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
another important query is to list all the columns in a index in this case clustered index
--list of columns in the clustered index
select OBJECT_NAME(SI.id) TableName, SI.name IndexName, SIK.indid IndexId, SC.name ColName
from sysindexes SI
inner join sysindexkeys SIK
on SI.id = SIK.id
and SI.indid = SIK.indid
inner join syscolumns SC
on SC.id = SIK.id
and SC.colid = SIK.colid
where SIK.id = OBJECT_ID( 'ContactMapping' )
and SIK.indid = 1
order by SIK.indid, SC.colorder
Thursday, April 08, 2010
Space used by each Table in SQL
SQL 2005 Version
================
BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);
INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END
FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch
Previous Versions of SQL
========================
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
================
BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);
INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END
FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch
Previous Versions of SQL
========================
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
Wednesday, April 07, 2010
Inconsistencies in G/L Entry posting
The transaction cannot be completed because it will cause inconsistencies in the G/L Entry table. Check where and how the CONSISTENT function is used in the transaction to find the reason for the error. Contact your system manager if you need assistance. Parts of the program mark tables as inconsistent during very comprehensive tasks, such as posting. This prevent data from being updated incorrectly.
1. Rounding errors could be one of the issues making a line inconsistent.
Finding the transaction which cause the issue ? there is a solution got this from Rashed Amini courtesy mibuso
1. create the following CU
OBJECT Codeunit 50000 Single Instance CU
{
OBJECT-PROPERTIES
{
Date=10/11/07;
Time=[ 2:50:02 PM];
Modified=Yes;
Version List=MOD01;
}
PROPERTIES
{
SingleInstance=Yes;
OnRun=BEGIN
IF NOT StoreToTemp THEN BEGIN
StoreToTemp := TRUE;
END ELSE
FORM.RUNMODAL(0,TempGLEntry);
END;
}
CODE
{
VAR
TempGLEntry@1000000000 : TEMPORARY Record 17;
StoreToTemp@1000000001 : Boolean;
PROCEDURE InsertGL@1000000000(GLEntry@1000000000 : Record 17);
BEGIN
IF StoreToTemp THEN BEGIN
TempGLEntry := GLEntry;
IF NOT TempGLEntry.INSERT THEN BEGIN
TempGLEntry.DELETEALL;
TempGLEntry.INSERT;
END;
END;
END;
BEGIN
END.
}
}
2. And in CU 12 I add the following Code in function FinishCodeunit
FinishCodeunit()
WITH GenJnlLine DO BEGIN
IF GLEntryTmp.FIND('-') THEN BEGIN
REPEAT
GLEntry := GLEntryTmp;
IF GLSetup."Additional Reporting Currency" = '' THEN BEGIN
GLEntry."Additional-Currency Amount" := 0;
GLEntry."Add.-Currency Debit Amount" := 0;
GLEntry."Add.-Currency Credit Amount" := 0;
END;
GLEntry.INSERT;
//MOD01 Start
SingleCU.InsertGL(GLEntry);
//MOD01 End
IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN
3. Once you've made the changes. You run the SinleInstanceCU Once.
4. Then do what ever you do to get the consistency error.
5. Then Run the SingleInstanceCU again.
6. You'll see a list of GL lines. You will see why the transaction is not balanced.
1. Rounding errors could be one of the issues making a line inconsistent.
Finding the transaction which cause the issue ? there is a solution got this from Rashed Amini courtesy mibuso
1. create the following CU
OBJECT Codeunit 50000 Single Instance CU
{
OBJECT-PROPERTIES
{
Date=10/11/07;
Time=[ 2:50:02 PM];
Modified=Yes;
Version List=MOD01;
}
PROPERTIES
{
SingleInstance=Yes;
OnRun=BEGIN
IF NOT StoreToTemp THEN BEGIN
StoreToTemp := TRUE;
END ELSE
FORM.RUNMODAL(0,TempGLEntry);
END;
}
CODE
{
VAR
TempGLEntry@1000000000 : TEMPORARY Record 17;
StoreToTemp@1000000001 : Boolean;
PROCEDURE InsertGL@1000000000(GLEntry@1000000000 : Record 17);
BEGIN
IF StoreToTemp THEN BEGIN
TempGLEntry := GLEntry;
IF NOT TempGLEntry.INSERT THEN BEGIN
TempGLEntry.DELETEALL;
TempGLEntry.INSERT;
END;
END;
END;
BEGIN
END.
}
}
2. And in CU 12 I add the following Code in function FinishCodeunit
FinishCodeunit()
WITH GenJnlLine DO BEGIN
IF GLEntryTmp.FIND('-') THEN BEGIN
REPEAT
GLEntry := GLEntryTmp;
IF GLSetup."Additional Reporting Currency" = '' THEN BEGIN
GLEntry."Additional-Currency Amount" := 0;
GLEntry."Add.-Currency Debit Amount" := 0;
GLEntry."Add.-Currency Credit Amount" := 0;
END;
GLEntry.INSERT;
//MOD01 Start
SingleCU.InsertGL(GLEntry);
//MOD01 End
IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN
3. Once you've made the changes. You run the SinleInstanceCU Once.
4. Then do what ever you do to get the consistency error.
5. Then Run the SingleInstanceCU again.
6. You'll see a list of GL lines. You will see why the transaction is not balanced.
Saturday, April 03, 2010
CONTEXT_INFO Connection wide variable
Need for a connection wide variable at the database level arose when I was creating an interface between Navision and MSCRM
The requirement was that when an Item was created in Navision it was pushed to CRM and when any changes were made in CRM they were reflected back in Navision we used triggers and things were fine but due to a two way interface it was required that the trigger should ignore any updates which were being made during the synch process and only the entries which were being made by the user on the front end should be captured for sync.
This is what we did. The procedure which was updating the CRM item table set the context_info as shown below
--context_info is used in the target tables triggers to ignore updates
--else it results in endless updates
declare @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = CAST ('SynchronizeNAV' as varbinary(128) )
set CONTEXT_INFO @CONTEXT_INFO
In the trigger on the Item table in CRM we checked if the update/ insert was being made by the user or by the sync procedure as below
declare @sourceProc varchar(20)
select @sourceProc = cast ( context_info() as varchar)
set @sourceProc = ltrim( rtrim( @sourceProc ) )
if ( @sourceProc = 'SynchronizeCRM' )
return
one thing to ensure while using the context_info is that once the use is done it should be reset to null else it will continue to exist with the set value
thus at the end of the process we user
set context_info null
The requirement was that when an Item was created in Navision it was pushed to CRM and when any changes were made in CRM they were reflected back in Navision we used triggers and things were fine but due to a two way interface it was required that the trigger should ignore any updates which were being made during the synch process and only the entries which were being made by the user on the front end should be captured for sync.
This is what we did. The procedure which was updating the CRM item table set the context_info as shown below
--context_info is used in the target tables triggers to ignore updates
--else it results in endless updates
declare @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = CAST ('SynchronizeNAV' as varbinary(128) )
set CONTEXT_INFO @CONTEXT_INFO
In the trigger on the Item table in CRM we checked if the update/ insert was being made by the user or by the sync procedure as below
declare @sourceProc varchar(20)
select @sourceProc = cast ( context_info() as varchar)
set @sourceProc = ltrim( rtrim( @sourceProc ) )
if ( @sourceProc = 'SynchronizeCRM' )
return
one thing to ensure while using the context_info is that once the use is done it should be reset to null else it will continue to exist with the set value
thus at the end of the process we user
set context_info null
Friday, April 02, 2010
Thursday, April 01, 2010
Get a list of Objects modified after a given date from AOT
1. Goto AOT - Expand "System documentation" - Expand "Tables" - Tablebrowse "UtilElements" or Tablebrowse - "UtilIdElements"
2. Right click in field "Utillevel" - select Filter By Field - type - usr - press ok
3. Right click in field "RecordType" - select Filter By Field - type - SharedProject - press ok
4. Right click in field "CreatedDate" - select Filter By Field - type - 01-Jan-2010..
2. Right click in field "Utillevel" - select Filter By Field - type - usr - press ok
3. Right click in field "RecordType" - select Filter By Field - type - SharedProject - press ok
4. Right click in field "CreatedDate" - select Filter By Field - type - 01-Jan-2010..
Oracle Linked Server
Following are the steps involved in creating a linked server and working with stored procedures on the Oracle Database
1. Install the Oracle Client software on the server
2. Create a linked server as follows
EXEC sp_addlinkedserver
'OracleLinkedServer', 'Oracle',
'MSDAORA', 'OracleServer'
The name of the linked server is Oracle-LinkedServer.
The second parameter, product name (Oracle),is optional.The third parameter specifies the OLE DB provider.
The third parameter MSDAORA is the name of the Microsoft OLE DB Provider for Oracle.
Thr fourth parameter is the data source name of the Oracle Server (This is the TNS name of Oracle server which should work when pinged using a utility called TNSping and the servername this utility is installed with the client software of Oracle.)
3. Add Login information for the linked server
EXEC sp_addlinkedsrvlogin '
OracleLinkedServer ', false,
'SQLuser', 'OracleUser',
'OraclePwd'
The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.
The second parameter determines the name of the login to be used on the remote system.A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case.A value of False means you'll supply the remote login.
The third parameter specifiesthe name of a SQL Server login that this remote login will map to.A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the keyword domain\ to specify a Windows login.
The fourth and fifth parameters supply login and password values for the Oracle system.
3. To be able to use stored procedures on Oracle the RPC paramter should be enabled on the server which is done as follows:-
sp_serveroption 'ORALINK', 'rpc out', 'true'
sp_serveroption 'ORALINK', 'rpc', 'true'
4. To access the tables on a linked server, use a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name.
For example, to query the sample Oracle Scott database, you'd enter the statement
SELECT * FROM OracleLinkedServer..SCOTT.EMP
(Please mark the double dots after the server name which means that the catalogue or database name is not required and used by default this is because in oracle each database is treated as a server with a TNS name)
5. Test a stored procedure
declare @lstatus varchar(240)
set @lstatus = ''
execute ( 'BEGIN JNC_RMS_API.CHECK_CONNECTIVITY( ? ); END;', @lstatus output) at ORALINK
print @lstatus
Please ensure that the strings being sent for output parameters are initialized with a default value ( set @lstatus = '' ) else the procedures don't execute.
1. Install the Oracle Client software on the server
2. Create a linked server as follows
EXEC sp_addlinkedserver
'OracleLinkedServer', 'Oracle',
'MSDAORA', 'OracleServer'
The name of the linked server is Oracle-LinkedServer.
The second parameter, product name (Oracle),is optional.The third parameter specifies the OLE DB provider.
The third parameter MSDAORA is the name of the Microsoft OLE DB Provider for Oracle.
Thr fourth parameter is the data source name of the Oracle Server (This is the TNS name of Oracle server which should work when pinged using a utility called TNSping and the servername this utility is installed with the client software of Oracle.)
3. Add Login information for the linked server
EXEC sp_addlinkedsrvlogin '
OracleLinkedServer ', false,
'SQLuser', 'OracleUser',
'OraclePwd'
The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.
The second parameter determines the name of the login to be used on the remote system.A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case.A value of False means you'll supply the remote login.
The third parameter specifiesthe name of a SQL Server login that this remote login will map to.A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the keyword domain\ to specify a Windows login.
The fourth and fifth parameters supply login and password values for the Oracle system.
3. To be able to use stored procedures on Oracle the RPC paramter should be enabled on the server which is done as follows:-
sp_serveroption 'ORALINK', 'rpc out', 'true'
sp_serveroption 'ORALINK', 'rpc', 'true'
4. To access the tables on a linked server, use a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name.
For example, to query the sample Oracle Scott database, you'd enter the statement
SELECT * FROM OracleLinkedServer..SCOTT.EMP
(Please mark the double dots after the server name which means that the catalogue or database name is not required and used by default this is because in oracle each database is treated as a server with a TNS name)
5. Test a stored procedure
declare @lstatus varchar(240)
set @lstatus = ''
execute ( 'BEGIN JNC_RMS_API.CHECK_CONNECTIVITY( ? ); END;', @lstatus output) at ORALINK
print @lstatus
Please ensure that the strings being sent for output parameters are initialized with a default value ( set @lstatus = '' ) else the procedures don't execute.
Subscribe to:
Posts (Atom)