Custom Database Objects or Out of the Box Deltek Objects?

I was running through a list of stored procedures and tables in our Vision database this morning, and I was trying to find some of our custom objects. Like many Deltek clients, we have built custom applications that run within Vision and or use the Vision dataset in a new/better way. While looking around, I got lost very quickly.

If you have many custom tables/stored procedures or are planning on creating custom apps within your Vision database, I would suggest that you consider the idea of creating a seperate schema for those objects to live in.

This is greatly helpful in many ways. A seperate scheman allows you to quickly see which database objects are out of the box and provided by Detlek and which ones have been created specifically for your instance/company. Furthermore, depending on how granular you wish to get, you can create schemas for different applications that run within you Vision database, or you can come up with a naming convention for you tables and stored procedures that furthere deliniate there purpose.

Enjoy......

Vision XML Stored in the database

As many of us know, The vision application stores xml strings in the database as way to record parameters and settings. Many times, these strings are tens of thousands of characters long and are hard to read from a sql client tool interface.In addition, the strings sometimes exceed the character count limitation that many SQL client tools impose and you cannot fully view the contents of the xml.

To help parse out the xml in a usable format, I've written the query (no guarantees with no liabilities) below as an example of which parses the report name and the where clause out of the report log table and displays it.

select ReportLog.Options.query( N'/options/reportName/text()') as ReportName,
ReportLog.Options.query( N'/options/where/text()') as WhereClause
from
(
select Convert(XML,Options) as Options from reportlog
) as ReportLog


For those of you who are working within a Multicurrency environment, you can also query (no guarantees with no liabilities) the ledger tables to parse out information about rates and dates used on Given transactions



select LedgerAR.wbs1,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgRate/text()') as ExchgRate,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgDate/text()') as ExchgDate,
LedgerAR.ExchangeInfo.query( N'/parms/OvrDateUsed/text()') as OvrDateUsed,
LedgerAR.ExchangeInfo.query( N'/parms/OvrAmtUsed/text()') as OvrAmtUsed,
LedgerAR.ExchangeInfo.query( N'/parms/InvUsed/text()') as InvUsed,
LedgerAR.ExchangeInfo.query( N'/parms/TriUsed/text()') as TriUsed,
LedgerAR.ExchangeInfo.query( N'/parms/TriCurCode/text()') as TriCurCode,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgRate2/text()') as ExchgRate2,
LedgerAR.ExchangeInfo.query( N'/parms/ExchgDate2/text()') as ExchgDate2,
LedgerAR.ExchangeInfo.query( N'/parms/InvUsed2/text()') as InvUsed2

FROM
( select wbs1,
Convert(xml,ExchangeInfo) as ExchangeInfo
from LedgerAR
where ExchangeInfo is not null
) as LedgerAR

The above queries (no guarantees with no liabilities) should help you get started in dealing with long xml strings stored in the vision database.

Enjoy......