Adjust Exchange Rates Dimension Error

The adjust exchange rates batch job calculates and post the exchange gain/loss entries due to transactions in foreign currency. In doing so the batch job is required to pass a no of entries into the G/L.

In cases where the G/L has been tightly bound by dimension rules it can sometimes fails in this case it is necessary to understand the entries that this batch job passes and accordingly have the rules in place.

The Batch job as mentioned in the help creates one entry per currency per posting group of the banks defined. Because it consolidates the entries it cannot pick the dimensions from the source transactions. Thus it picks the dimensions which are defined as the default dimensions on the bank card. Thus we need to make sure that the dimension rules are met by these default dimensions. The entries are passed to the control accounts defined in the posting groups for the banks and the exchange gain or loss accounts.

In case of adjusting the Customer and Vendor Accounts it passes entries to the control accounts and a balancing entry is passed to the Exchange gain or loss accounts as configured in the the currency setup.

Columns in a Primary Key Index

Query to get the columns in a primary key for a table

select object_name(SI.id) tableName, SI.name indexName, SC.name
from sysindexes SI
inner join sysindexkeys SIK
on SIK.id = SI.id
and SIK.indid = SI.indid
inner join syscolumns SC
on SC.colid = SIK.colid
and SC.id = SI.id
where 1=1
and object_name(SI.id) = 'SalesHeader'
and SI.status in ( 2066 , 2048 )

sysindexes (T-SQL)

Contains one row for each index and table in the database. This table is stored in each database. This same table can be used to identify clustered indexes and non-clustered indexes the clustered index always has the indid = 1 and a non-clustered index will have an indid > 1

Column name	Data type	Description
id	int	ID of table (for indid = 0 or 255). Otherwise, ID of table to which the index belongs.
status	int	Internal system-status information:
		1 = Cancel command if attempt to insert
		duplicate key.
		2 = Unique index.
		4 = Cancel command if attempt to insert
		duplicate row.
		16 = Clustered index.
		64 = Index allows duplicate rows.
		2048 = Index used to enforce PRIMARY KEY
		constraint.
		4096 = Index used to enforce UNIQUE constraint.
first	binary(6)	Pointer to the first or root page.
indid	smallint	ID of index:
		1 = Clustered index.
		>1 = Nonclustered.
		255 = Entry for tables that have text or image
		data.
root	binary(6)	For indid >= 1 and <>root is the pointer to the root page. For indid = 0 or indid = 255, root is the pointer to the last page.
minlen	smallint	Minimum size of a row.
keycnt	smallint	Number of keys.
groupid	smallint	Filegroup ID on which the object was created.
dpages	int	For indid = 0 or indid = 1, dpages is the count of data pages used. For indid=255, it is set to 0. Otherwise, it is the count of index pages used.
reserved	int	For indid = 0 or indid = 1, reserved is the count of pages allocated for all indexes and table data. For indid = 255, reserved is a count of the pages allocated for text or image data. Otherwise, it is the count of pages allocated for the index.
used	int	For indid = 0 or indid = 1, used is the count of the total pages used for all index and table data. For indid = 255. used is a count of the pages used for text or image data. Otherwise, it is the count of pages used for the index.
rowcnt	binary(8)	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rowcnt is set to 0.
rowmodctr	int	Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
soid	tinyint	Sort order ID that the index was created with. 0, if there is no character data in the keys.
csid	tinyint	Character set ID that the index was created with. 0, if there is no character data in the keys.
xmaxlen	smallint	Maximum size of a row.
maxirow	smallint	Maximum size of a nonleaf index row.
OrigFillFactor	tinyint	Original fillfactor value used when the index was created. This value is not maintained; however, it can be helpful if you need to re-create an index and do not remember what fillfactor was used.
reserved1	tinyint	Reserved.
reserved2	int	Reserved.
FirstIAM	binary(6)	Reserved.
impid	smallint	Reserved. Index implementation flag.
lockflags	smallint	Used to constrain the considered lock granularities for an index. For example, a lookup table that is essentially read-only could be set up to do only table level locking to minimize locking cost.
pgmodctr	int	Used to track the number of pages that have changed in an index.
keys	varbinary(816)	List of the column IDs of the columns that make up the index key.
name	sysname	Name of table (for indid = 0 or 255). Otherwise, name of index.
statblob	image	Statistics BLOB.
maxlen	int	Reserved.
rows	int	Data-level rowcount based on indid = 0 and indid = 1, and the value is repeated for indid >1. For indid = 255, rows is set to 0. Provided for backward compatibility.

syscolumns (T-SQL)

Contains one row for every column in every table and view, and a row for each parameter in a stored procedure. This table is in each database.

Column name	Data type	Description
name	sysname	Name of the column or procedure parameter.
id	int	Object ID of the table to which this column belongs, or the ID of the stored procedure with which this parameter is associated.
xtype	tinyint	Physical storage type from systypes.
typestat	tinyint	For internal use only.
xusertype	smallint	ID of extended user-defined data type.
length	smallint	Maximum physical storage length from systypes.
xprec	tinyint	For internal use only.
xscale	tinyint	For internal use only.
colid	smallint	Column or parameter ID.
xoffset	smallint	For internal use only.
bitpos	tinyint	For internal use only.
reserved	tinyint	For internal use only.
colstat	smallint	For internal use only.
cdefault	int	ID of the default for this column.
domain	int	ID of the rule or CHECK constraint for this column.
number	smallint	Subprocedure number when the procedure is grouped (0 for nonprocedure entries).
colorder	smallint	For internal use only.
autoval	varbinary(255)	For internal use only.
offset	smallint	Offset into the row in which this column appears; if negative, variable-length row.
status	tinyint	Bitmap used to describe a property of the column or the parameter:
		0x08 = Column allows null values.
		0x10 = ANSI padding was in effect when varchar or varbinary columns were added. Trailing blanks are preserved for varchar and trailing zeros are preserved for varbinary columns.
		0x40 = Parameter is an OUTPUT parameter.
		0x80 = Column is an identity column.
type	tinyint	Physical storage type from systypes.
usertype	smallint	ID of user-defined data type from systypes.
printfmt	varchar(255)	For internal use only.
prec	smallint	Level of precision for this column.
scale	int	Scale for this column.
iscomputed	int	Flag indicating whether the column is computed:
		0 = Noncomputed
		1 = Computed
isoutparam	int	Is whether the procedure parameter is an output parameter:
		1 = True
		0 = False
isnullable	int	Is whether the column allows null values:
		1 = True
		0 = False

sysindexkeys (T-SQL)

Contains information for the keys or columns in an index. This table is stored in each database.

Column name	Data type	Description
id	int	ID of the table
indid	smallint	ID of the index
colid	smallint	ID of the column
keyno	smallint	Position of the column in the index

Reading Varbinary Data

We are working on a application which needs to read data from a SQL table and then create an XML packet out of it did not want to use the XML capabilities of SQL as we has some specific requirements for the data. When we came across timestamp values we could not read them in the Ado.Recordset as the datatype was binary then came across this powerful function which would do the conversion for us.

master.dbo.fn_varbintohexstr( @@DBTS)

Windows Scheduled Task

We were in the process of testing a script we had written to automate sage and insert some data reading if from a text file. Later the requirement was to read this data automatically on a scheduled basis for scheduling we suggested using Windows Scheduled Tasks. We tested the script working perfectly on the local machine and then using Remote Desktop we ported the exe to server and tried to test but the script would not do anything we later found that the exe was working fine the only problem is that when a task being scheduled in windows needs to interact using GUI is always does so on the console session.

Took quite some time to figure this out this is a handy information.