Sage 100

Anyone dealt with forms-worktables in Sage 100 Premium (SQL). I look at tempdb in SQL but all files have names like dbo.#A24EB959 and data doesn't display. How do you find a SalesOrderWrk worktable instance in SQL?

I would go into SO Printing and open the sales order form in crystal and see what worktable crystal is using there.

With premium there isn't a way that I know of. Sage is using the tempdb's

David - official name is SalesOrderWrk.M4T. In the program, the table instance is like SO_SalesOrderWrkFSI062221681901 - but I don't see that anywhere in SQL Management.

Jim - that's my quandry - know way I know of seeing/using the tempdb's - but they exist somewhere. I don't need to use SQL Manager to view them, I just need their names.

Jim: Sage support doesn't seem to know - maybe DSD Development team?

During a SQL conversion class a couple years ago. I believe @AlnoorCassim said that it was all internal to SQL and they were not visible.

I guess I misunderstood I thought you were trying to find SalesOrderWrk.M4T so you could edit the crystal.

Jim - even if SQL manager doesn't show them, they exist somewhere for crystal to use. Puzzled !

Lee - The worktable + session ID is created in TempDB but because the table name is prefixed with the # symbol, it is created by SQL Server as a session specific temp table which means the data inside of it isn't visible to you with SQL Mgmt Studio. You found the right table dbo.#A24EB959 but the data is invisible to you. This is by design to match the MAS 500 scheme. It sounds like you want to see the worktable data while you're modifying a Sales Order form with Crystal Reports designer. That is just not possible with Premium unless you modify Sy_ReportEngine or wherever that # table prefix is actually done.

Thanks @AlnoorCassim I thought that what you said but I couldn't remember the exact reason.

If I new which table in the format dbo.#A24EB959 format was ""mine"" - it might be accessed by a 3rd party document manager for multiple crystal iterations. Thanks for the Sy_ReportEngine tip.

I believe the purpose of work tables is to workaround to the slow ODBC connection between Sage 100 tables and Crystal. A way to replicate this approach in Sage 100 Premium is to use a stored procedure with parameters. The store procedure is a SELECT statement and selects from the same tables Sage 100 would when generating the work table. You can select a stored procedure just as you would any other database table in CR Database Expert.. If you don't see them, Go to File > Options and click the Database tab. Then check the box for Stored Procedures. Now it should show up in your list. If your stored procedure has an input parameter(s), Crystal Reports will automatically create a corresponding parameter for you in the report and give it the same name as what is in the stored procedure. For your application, you'd need to determine the best way to pass the selection parameters to the above Crystal Report.