Monday, October 20, 2008

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.

Saturday, October 04, 2008

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

Friday, October 03, 2008

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.