SQL: Querying Microsoft Nice Plains – Overview for Database Administrator/Developer

Seems to be like Microsoft Nice Plains turns into increasingly common, partly due to Microsoft muscle mass behind it. Now it’s focused to the entire spectrum of horizontal and vertical market clientele. Small corporations use Small Enterprise Supervisor (which relies on the identical expertise – Nice Plains Dexterity dictionary and runtime), Nice Plains Normal on MSDE is for small to midsize purchasers, after which Nice Plains serves the remainder of the market as much as huge companies. There are a number of reporting instruments obtainable and also you undoubtedly have to know which one to make use of for various kinds of studies.

In case you are database administrator who’s requested to import some information to Nice Plains or restore or copy information from one firm to a different – learn this and you’ll have the clues on the place to look additional.

1. Microsoft Nice Plains Tables Construction – Launch Nice Plains and go to Instruments->Useful resource Description->Tables. Discover the desk within the correct sequence. In case you are on the lookout for the purchasers – it needs to be RM00101 – buyer grasp file.

2. DEX_ROW_ID. That is id column and every Nice Plains desk has it – that is as a result of Nice Plains Dexterity expertise. This column is rarely used as a key subject – so do not attempt to hyperlink your tables on DEX_ROW_ID. In case if you’ll want to switch the desk from one firm to a different it’s best to use these queries:

Choose * into GL00100_BAK from TWO.dbo.GL00100
go
alter desk GL00100_BAK drop column DEX_ROW_ID
go
insert into GL00100 choose * from GL00100_BAK
go
drop desk GL00100_BAK

The set of queries above will switch GL00100 (Account Grasp desk) from TWO firm into your present firm. Then you’ll want to run Checklinks – confer with GP Guide – to be able to recreate the remainder of the account grasp associated tables.

3. Don’t modify the desk – typically it appears to be good for those who simply append couple of additional columns to the desk – like in IV00101 – stock grasp file why would not you simply add couple of extra descriptions. If you happen to do that – Nice Plains Dexterity engine will fail studying all of your objects – as a consequence of the truth that DYNAMICS.DIC (essential Nice Plains Dictionary file) has precise description of all of the tables and Dexterity makes use of it for studying and writing into the particular desk

4. Be at liberty to create SQL views or saved procs. In case you are serving to your inside builders to create Crystal Stories – good SQL views are actual assist to them. Let me offer you instance, the view beneath will present work and historic SOP Invoices – then Crystal can simply use it

Create view SOP_WORK_HIST
as
choose SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP30200 the place SOPTYPE=Three and VOIDSTTS=0
union
choose SOPNUMBE, CUSTNMBR, CUSTNAME, DOCAMNT from SOP10100 the place SOPTYPE=Three and VOIDSTTS=0

The above view will present all of the work and historic non-voided invoices (SOP Kind = Three stays for bill)

5. Some restore / unlocking suggestions:

If you happen to run question above towards DYNAMICS database – it should unlock the consumer, who by chance shut down the pc with out logging off Nice Plains:

delete ACTIVITY the place USERID=’JOHN’

Subsequent one will unlock hanging batch:

replace SY00500 set BCHSTTUS=Zero the place BACHNUMB = ‘JULYINVOICES04’

Comfortable querying! In order for you us to do the job – give us a name 1-866-528-0577! assist@albaspectrum.com

Leave a Reply

Your email address will not be published. Required fields are marked *