EXEC_SP Call not working

MarkP

Hi everyone,
I have a stored procedure in SQL Server that is just producing a unique customer only list i.e. SELECT DISTINCT ... from a set of CustomerFarms
The procedure works fine if run in SSMS.
I created a B/O called CustomerList with the necessary attributes that I am retrieving from the procedure.
However, when I use EXEC_SP in Aware (8.6) and try to use the CustomerList B/O as the Return object to store the output into, I am getting the following error information from the Server Log:

I know these are Aware system generated fieldnames which are created as part of the CustomerList object so I initially tried removing these from the table structure directly in SQL Server so I was just left with the 2 columns I really wanted.
Still no luck.
Is anyone able to advise me what I might be doing wrong or need to do? I did try a suggested workaround I found in the forum re: IF EXISTS statements to build the unique list manually and this worked but OMG - it takes forever.
Thanks

UnionSystems

Typing this on my phone so a bit restricted.

I think you need to include ID,BASTIMESTAMP & BASVERSION columns in your SQL SELECT statement.

MarkP

Thanks UnionSystems
That got rid of the error messages - but unfortunately it killed the whole approach of trying to use a SELECT DISTINCT sql clause to build a unique customer name list as the ID field is always unique. :cry:
I've worked out a different process approach on how to build the list - still a bit slow but it does work ok.

Jaymer

no, this should have worked
plz post your code
its just a SQL issue

PointsWell

As long as you are not going to do any operation with the records you can put whatever you want into they ID and BASVersion fields.

I use Stored Procs to do dashboard calculations and just put arbitrary numbers into these fields as in my case the data returned doesn't relate to real entities.

You obviously cannot do that if you want to do something with the data, but as you are looking for a list of distinct names it doesn't suggest that you want to do anything but read.

BLOMASKY

As others have said, there are 3 "hidden" columns in every Aware table. ID, BASVERSION, BASTIMESTAMP. (ok, the ID is not really hidden). When you call a SP and want data returned, you have 2 choices:

1st, If you just need a couple of fields returned, they can be output statements in your SP, so your SP can look like
CREATE PROCEDURE test1
@x integer -- THIS IS AN INPUT PARAM
@y integer output -- THIS IS RETURNED TO AWARE

as
select @y = .... from .... where ... = @x

and your Aware SP call looks like EXEC_SP 'test1' WITH '@x' = BO.attr, '@y' = bo.attr2 OUT

OR, if you want to return a list, a table, you always need to return the 3 hidden fields for each row returned. the ID HAS to be unique. the other 2 can be 1 for BASVERSION and getdate() for BASTIMESTAMP or any other contstants.

Now, to solve the problem of the SELECT DISTINCT you could write your SQL SP to have an inner and outer select so it could look like

SELECT ROW_NUMBER() OVER (order by custname) as ID, 1 as BASVERSION, getdate() as BASTIMESTAMP, custname from
(select distinct custname from sometable) as X

The ROW_NUMBER() OVER (order by somefield) will give a sequential range of numbers. Many other ways to do that. you can also do a NEXT VALUE FOR BAS_IDGEN_SEQ as ID

Bruce