Hi,

I've recently added some replies to this thread regarding the use of SAME_DB but this question is a bit different so I thought I'd start a new topic. Plus the other topic was closed some time ago.

I've been playing around with two methods of using the MS SQL Server for my HOLD files.

One method is done by using...
ON TABLE HOLD AS MYHOLD FORMAT SAME_DB
In subsequent TABLE request use TABLE FILE MYHOLD
This assumes the table referenced in your TABLE FILE statement is a SQL Table.
Advantage: very simple to implement and pretty fast response time
Disadvantage: can't get ASNAMES to work with it so Field Title information from master file is lost. Subsequent TABLE request shows Field Names instead of Titles.

The other method requires a few statements...
ENGINE SQLMSS SET TRANSACTIONS AUTOCOMMIT
ON TABLE HOLD AS #MYHOLD FORMAT SQLMSS
In subsequent TABLE request use TABLE FILE #MYHOLD
I can't be certain but I think the reason I need to set transactions to autocommit is because I have ENGINE SQLMSS SET CURSORS CLIENT in our server profile.
Advantage: ASNAMES does work with this method so Field Titles are used as Field Names in the HOLD file thus making them appear in the subsequent TABLE request.
Disadvantage: MUCH slower at returning results than the SAME_DB method! I have tried different values in the ENGINE SQLMSS SET FETCHSIZE n statement but that seems to deliver inconsistent results.

My questions are:
1) Have any of you figured out how to get ASNAMES to work with the SAME_DB method?
2) Anybody know a 'safe' way to speed up the results of the second method that works consistently?

Thanks for your time and attention.

Best regards,

Dan

This message has been edited. Last edited by: Dan Pinault,

Dan,

I don't think I have had trouble with ASNAMES and SAME_DB.

APP HOLDMETA SYNTEST

SET ASNAMES=ON

TABLE FILE TIME
PRINT 
TIME_KEY AS TK
DAY_OF_WEEK AS DOW
YQUARTER AS YQ
ON TABLE HOLD AS H001 FORMAT SAME_DB
WHERE READLIMIT EQ 100
END

Then

APP HOLDMETA SYNTEST

?FF H001

TABLE FILE H001
PRINT *
END

Result:

 TK            TK            P9
 DOW           DOW           I11
 YQ            YQ            P19
 FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 SELECT T1."TK",T1."DOW",T1."YQ" FROM #H001 T1;

I think Dan is talking about the TITLE attribute from the original master and not an ASNAME.

In therory SET HOLDATTR = ON should do what you want

quote:

SET HOLDATTR =[ON|OFF|FOCUS]
where:

ON
Uses the TITLE attribute as specified in the original Master File in HOLD files in any format.

However It do'nt work in 7.6.10

Either it's a bug or a incorrect documentation, either way I'd raise a case with support.

What is interesting is that ASNAMES has no effect on FORMAT SAME_DB, the AS 'name' is always used irrespective of
ON or OFF being used. (another bug)

This message has been edited. Last edited by: <JG>,

I only have Developer Studio but I have to create many production SQL Server tables with the 'AS' fieldnames. I get around this by defining the 'AS' fields I will be holding in SQL Server:

DEFINE FILE CAR
CountryName/A10=COUNTRY;
DealerCost/D7=DEALER_COST;
RetailCost/D7=RETAIL_COST;
ModelName/A24=MODEL;
END

TABLE FILE CAR
SUM
DealerCost
RetailCost
BY CountryName
BY ModelName
ON TABLE HOLD AS AS_TEST FORMAT SQLMSS
END

AS_TEST.MAS
FILENAME=AS_TEST, SUFFIX=SQLMSS , $
SEGMENT=AS_TEST, SEGTYPE=S0, $
FIELDNAME=COUNTRYNAME, ALIAS=CountryName, USAGE=A10, ACTUAL=A10, $
FIELDNAME=MODELNAME, ALIAS=ModelName, USAGE=A24, ACTUAL=A24, $
FIELDNAME=DEALERCOST, ALIAS=DealerCost, USAGE=D20.2, ACTUAL=D8, $
FIELDNAME=RETAILCOST, ALIAS=RetailCost, USAGE=D20.2, ACTUAL=D8, $

Thanks for the responses everybody. I'm with JG here. I think it must be a bug. I will open a case and update when I learn something.

Well, according to the support folks the trick is to use SET HOLDATTR = ON as JG mentioned. It did work for me. I am running 7.6.10 HF2 so I don't know if that is why it worked for me and not JG.

Now, have any of you using SAME_DB figured out how to get past the "1 PAGE 1" issue where the report stops delivering output at the end of the first "page"? I added that question to my case as well. I'll let you know if I learn anything there.

Cheers,

Dan

Oops! I spoke too soon. SET HOLDATTR = ON did not work for me.

I also did some more testing and I can't get rid of the "1 PAGE 1" text at the very beginning of the page. I also found out that if you read from a hold file that was created using the SAME_DB method it won't work with On Demand Paging. All you will get is the first page without the ODP toolbar. Oh, and you'll get the "1 PAGE 1" at the top too as a bonus!

Needless to say, the case is still open.

Well, supposedly SAME_DB does not support the use of HOLDATTR. I have submitted a NFR for this.

Also, the '1 PAGE 1' business I discovered was related to the use of TABLEF in the original query. Don't ask me how they are related but changing TABLEF to TABLE made the '1 PAGE 1' go away and now the report even works with On-Demand-Paging.

Who'd have thunk it?!?