Direct Queries to SoftPro SelectDb

[joe.mag]

Hi, all. I am looking into making some direct queries against the SelectDb database in an after-hours scheduled stored procedure (from my own database on the same SQL Server instance as SoftPro). I have asked my business associates in my firm if anyone has provided them with a guide to the schema but no one has gotten any such documentation (I thought maybe as an aid for report writing and/or document design). I've constructed a SQL Server Database Diagram and it shows me linkages and such but I'd really like some guidance in terms of the entity hierarchy.

Does anyone know of a document and/or other resource that would help explain the SP schema?

Thanks!

[Mark McKenna]

SoftPro does not provide a database schema because it is not a public facing data store. The schema is subject to change with every major release - and these sorts of changes generally go unannounced. However, if you are intending to perform read-only queries against the data store, or are working on reports, we can often assist with specific questions.

[joe.mag]

Yes, these would be read only queries and I will have an abstraction layer of views between my stored procs and the SelectDb schema to at least minimize changes to my code when new releases hit.

I mainly want to make sure I understand the core hierarchy of Orders and Title Policies. The database diagram I've derived shows me things like a linkage between Policy and TitleProductItem but I'm not sure that's the correct path to be following.

My primary interest right now has to do with Policy.Number and relating the Policy record back to the Order (or whatever corresponds to the core File for SoftPro). We've implemented a way to fetch jacket numbers for policies automatically (a temporary fix until 360 integration with FA is completed) and I'd like to run reports and sanity checks on jacket assignments to files.

Thanks!

[joe.mag]

Let me detail my understanding of how to get the information I want and see if you guys agree:

In order to query for the core entities (core from my perspective), I use these tables:

Policy -- TitleProductItem -- Title -- Order -- FinalTitleOpinion, PreliminaryTitleOpinion

as follows:

select * from
SelectDb.dbo.[Order] o
inner join
SelectDb.dbo.Title t on o.ID = t.OrderID
inner join
SelectDb.dbo.TitleProductItem tpi on t.OrderID = tpi.TitleID
inner join
SelectDb.dbo.Policy p on tpi.ID = p.ID
inner join
SelectDb.dbo.FinalTitleOpinion fto on t.OrderID = fto.TitleID
inner join
SelectDb.dbo.PreliminaryTitleOpinion pto on t.OrderID = pto.TitleID


For Property, Parties and Recording information I use these separate queries:

select * from
SelectDb.dbo.[Order] o
inner join
SelectDb.dbo.Property prop on o.ID = prop.OrderID

select * from
SelectDb.dbo.[Order] o
inner join
SelectDb.dbo.OrderContact oc1 on o.ID = oc1.OrderID

select * from
SelectDb.dbo.[Order] o
inner join
SelectDb.dbo.Deed d on o.ID = d.OrderID
inner join
SelectDb.dbo.RecordingInformation ri on d.RecordingInfoID = ri.ID

Sound about right?

[Mark McKenna]

It looks like you are on the right track. Let us know if you run into any roadblocks while trying to extract whatever specific data you are looking for.