Find Distinct

KnightWare

Is it possible to Select a distinct list from a BO? I have a BO that contains the attribute Vendor. Can I do a Find from thi Object of just Distinct Vendors? IN SQL it would be -

Select DISTINCT Vendor, VendorName from VendorItems.

BLOMASKY

Why don't you write a Stored Procedure to do just that, then, in your query have an Exec_SP 'FindDistinct' returning BO

Bruce

KnightWare

That's what I finally ended up doing!

KnightWare

I'm using SQL Server envirenment for AIM backend. How do you (best practice) distinguish between the TEST and PRODUCTION envirnement for calling the stored procedure? I can place the procedure in both databases, but the EXEC_SP requires the complete path.

BLOMASKY

Assuming when you set up your Business Space you check BOTH boxes, for separate DB for production and test, you don't have to do a thing different. If you call the SP without the database name just the SP name, then it connects to which ever one is being run (does this make sense?)

So, do not use a fully qualified name i.e.: EXEC_SP 'ProdDatabase.dbo.mySP' but instead just EXEC_SP 'mySP'

Bruce

KnightWare

Database Allocation was setup as default". So, Database & Test Database = 'Default'.

KnightWare

I should add, I setup SQL with two DB, AWAREIM-P & AWAREIM-T for Prod and Test. And then setup config file (BASServer.props) with DAtamaseNameTest = AWAREIM-T. I notice as I', entering data, Production is goinf to Production and Test to Test.

BLOMASKY

it is MUCH better, when you create your BS to create 2 shiny new databases for your BS production and Test. Separate from the 2 you enter on the Database Properties window. Otherwise, Aware will preface all of your tables so your SP, will have to be changed between test and production. It is not TOO late to do this!!!

Bruce

KnightWare

So -
With the BS already defined , you can change and add in the new DB's? Best practice is to Setup Seperate DB names at the start. I don't think you can change after you have created revisions of you app, right?

KnightWare

I see what you are saying. I originally setup two DB's on our server, one each for Test and Production. Yes, in each one the BS are prefaced. BUT, the Stored Proceedure is the same name in each (dbo.C_DISTINCT_VENDOR) and leveraged against the proper table in respective DB. In SSMS i can run EXEC C_DISTINCT_VENDOR and the proper data is retuned in each connection. In AwareIM I have the query with Textual View = EXEC_SP 'C_DISTIINCT_VENDOR' RETURN Vendors and a BO (Vendors) setup to receive the Results. However when run in TEST I get the "Internal Error. Could not find stored procedure 'C_DISTINCT_VENDOR'.

I also see you point in createing to seperate DB's. I experimented with a new BS, but received the error of CREATE DATABASE permission denied in database master. I would need to know how configuator needs to be setup to do this and talk with DBA about a different server/instance to generate all these apps on.

KnightWare

Issue with stored procedure not found was a mis-spelling on my part. mia cupa. However in test if I run the query contaning the exec_sp call, no data is returned. I'm starting the test with use existing data.

KnightWare

Got This.
I needed to view "Rule Log" on the server so I was running under control panel (Only way to view that log). This wipes out the AIM wrapper log. Decided to stop CP and run AIM as Service as I usually do. Inspected the log and realized I forgotr to return ID,BASVersion & BASTimeStamp in the SP.

Geeesh.