Sage 100

Working through a form slow printing issue and need clarification. In Sage 100 Standard/Advanced Sage creates temp work files for Crystal forms and reports. However, in Premium I only see temp work files for registers, not forms or reports. I don't see any temp tables created in SQL nor under the company's standard MAS90 folders where the other work table reside. I tried previewing the form, leaving it open and queried the appropriate work table in SQL but it was empty of records and no new tables were found. So where are the temp tables used when printing for forms and reports from SQL? Thanks.

------------------------------
Bill Pfahnl
Nims & Associates
------------------------------

I'm not a Premium guru, but I've read somewhere that it uses SQL Server's version of temporary files that are only visible to the session that created them. I wanted to access them also, but gave up.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------

The temp tables are created in TempDB for performance but also done so for concurrency reasons by using a "#" (no quotes) ​in front of the name. Using a "#" means it's a local temporary table. Each session, which means each instance of form or report printing, gets its own unique temp table that is not visible to another sessions. That's very good for multiple users running the same form/report concurrently but not good if you're trying to say run a .rpt file directly from Crystal Reports Designer too see the data. IOW the trick in Standard/Advanced to swap a base work file for a populated temp work file during Preview is not possible.

Having said all that, you can still see all the local temporary table names with this command and if you can figure out which one is yours for the form/report you just previewed, you can possibly do something more with it. 

SELECT * FROM tempdb.sys.tables WHERE name LIKE '#%'

------------------------------
Alnoor Cassim

Email: alnoor.cassim@90minds.com
Ph:
------------------------------

Thank you @Alnoor Cassim and @Dan Burleson. This is what I need. Luckily it's not a form issue, where there's a problem with the form itself. It's an issue where some workstations take up to 3 minutes to compile and print the form where when run from the server it only take 10 seconds. This only started a month ago and no changes had been made to the form, Sage 100 or SQL. I'm now in a battle with IT.
​​

------------------------------
Bill Pfahnl
Nims & Associates
------------------------------

Original Message:
Sent: 07-09-2022 02:25
From: Alnoor Cassim
Subject: Premium (SQL) Crystal temp work files - where are they?

The temp tables are created in TempDB for performance but also done so for concurrency reasons by using a "#" (no quotes) ​in front of the name. Using a "#" means it's a local temporary table. Each session, which means each instance of form or report printing, gets its own unique temp table that is not visible to another sessions. That's very good for multiple users running the same form/report concurrently but not good if you're trying to say run a .rpt file directly from Crystal Reports Designer too see the data. IOW the trick in Standard/Advanced to swap a base work file for a populated temp work file during Preview is not possible.

Having said all that, you can still see all the local temporary table names with this command and if you can figure out which one is yours for the form/report you just previewed, you can possibly do something more with it.

SELECT * FROM tempdb.sys.tables WHERE name LIKE '#%'

------------------------------
Alnoor Cassim

Email: alnoor.cassim@90minds.com
Ph:
------------------------------

Thanks @Alnoor Cassim your response just helped me solve an issue I had given up on. Now I can capture the temporary work file name in scripting with the identical code I've been using with Std & Adv.
​ ​

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------

Original Message:
Sent: 07-09-2022 02:25
From: Alnoor Cassim
Subject: Premium (SQL) Crystal temp work files - where are they?

The temp tables are created in TempDB for performance but also done so for concurrency reasons by using a "#" (no quotes) ​in front of the name. Using a "#" means it's a local temporary table. Each session, which means each instance of form or report printing, gets its own unique temp table that is not visible to another sessions. That's very good for multiple users running the same form/report concurrently but not good if you're trying to say run a .rpt file directly from Crystal Reports Designer too see the data. IOW the trick in Standard/Advanced to swap a base work file for a populated temp work file during Preview is not possible.

Having said all that, you can still see all the local temporary table names with this command and if you can figure out which one is yours for the form/report you just previewed, you can possibly do something more with it.

SELECT * FROM tempdb.sys.tables WHERE name LIKE '#%'

------------------------------
Alnoor Cassim

Email: alnoor.cassim@90minds.com
Ph:
------------------------------

For those who are more visual, this is how I usually query temp tables in Premium. 
Refresh the list of temp tables, then click and drag the name into the query window...


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 07-09-2022 02:25
From: Alnoor Cassim
Subject: Premium (SQL) Crystal temp work files - where are they?

The temp tables are created in TempDB for performance but also done so for concurrency reasons by using a "#" (no quotes) ​in front of the name. Using a "#" means it's a local temporary table. Each session, which means each instance of form or report printing, gets its own unique temp table that is not visible to another sessions. That's very good for multiple users running the same form/report concurrently but not good if you're trying to say run a .rpt file directly from Crystal Reports Designer too see the data. IOW the trick in Standard/Advanced to swap a base work file for a populated temp work file during Preview is not possible.

Having said all that, you can still see all the local temporary table names with this command and if you can figure out which one is yours for the form/report you just previewed, you can possibly do something more with it.

SELECT * FROM tempdb.sys.tables WHERE name LIKE '#%'

------------------------------
Alnoor Cassim

Email: alnoor.cassim@90minds.com
Ph:
------------------------------

Good point @Kevin Moyes and here is how one can connect Crystal to those temporary files:


------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------

Original Message:
Sent: 07-11-2022 13:16
From: Kevin Moyes
Subject: Premium (SQL) Crystal temp work files - where are they?

For those who are more visual, this is how I usually query temp tables in Premium.
Refresh the list of temp tables, then click and drag the name into the query window...


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------


Original Message:
Sent: 07-09-2022 02:25
From: Alnoor Cassim
Subject: Premium (SQL) Crystal temp work files - where are they?

The temp tables are created in TempDB for performance but also done so for concurrency reasons by using a "#" (no quotes) ​in front of the name. Using a "#" means it's a local temporary table. Each session, which means each instance of form or report printing, gets its own unique temp table that is not visible to another sessions. That's very good for multiple users running the same form/report concurrently but not good if you're trying to say run a .rpt file directly from Crystal Reports Designer too see the data. IOW the trick in Standard/Advanced to swap a base work file for a populated temp work file during Preview is not possible.

Having said all that, you can still see all the local temporary table names with this command and if you can figure out which one is yours for the form/report you just previewed, you can possibly do something more with it.

SELECT * FROM tempdb.sys.tables WHERE name LIKE '#%'

------------------------------
Alnoor Cassim

Email: alnoor.cassim@90minds.com
Ph:

I set up a working database, and copy the work table data there...

use KevinTest;
select *
into SY_CompanyListingWrk_1 from tempdb.[dbo].[##SY_CompanyListingWrkKM071114967366];
select * from SY_CompanyListingWrk_1​

In CR, set Data Source to this working database / table... and you can edit the data if you need to see what different things look like in the report.  (You can't edit tempdb data).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 07-11-2022 14:13
From: Dan Burleson
Subject: Premium (SQL) Crystal temp work files - where are they?

Good point @Kevin Moyes and here is how one can connect Crystal to those temporary files:

Then use Crystal's Set Location command. This is helpful to debug forms and standard report changes instead of having to constantly preview, close and preview repeatedly.

​

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------


Original Message:
Sent: 07-11-2022 13:16
From: Kevin Moyes
Subject: Premium (SQL) Crystal temp work files - where are they?

For those who are more visual, this is how I usually query temp tables in Premium.
Refresh the list of temp tables, then click and drag the name into the query window...


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.


Original Message:
Sent: 07-09-2022 02:25
From: Alnoor Cassim
Subject: Premium (SQL) Crystal temp work files - where are they?

The temp tables are created in TempDB for performance but also done so for concurrency reasons by using a "#" (no quotes) ​in front of the name. Using a "#" means it's a local temporary table. Each session, which means each instance of form or report printing, gets its own unique temp table that is not visible to another sessions. That's very good for multiple users running the same form/report concurrently but not good if you're trying to say run a .rpt file directly from Crystal Reports Designer too see the data. IOW the trick in Standard/Advanced to swap a base work file for a populated temp work file during Preview is not possible.

Having said all that, you can still see all the local temporary table names with this command and if you can figure out which one is yours for the form/report you just previewed, you can possibly do something more with it.

SELECT * FROM tempdb.sys.tables WHERE name LIKE '#%'

------------------------------
Alnoor Cassim

Email: alnoor.cassim@90minds.com
Ph: