Stored Procedure

Question

In early versions of SQL, I usually created Stored Procedures to join tables and put an output to a new table, then use the new table in crystal report. I used it since year 2000 until 2007. Everytime I run the report the new table is updated always.

Recently, I use SQL 2008 and use the same method. The problem is when I run crystal report, changes in the new table data is not included. I have to execute the stored procedure within sql server manually to update the data used by crystal report.

Answer 1

Why you are inserting data to a table? Why you are not passing output of your joins to Crystal Report? What kind of report are you using in there?

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

It is a complicated sales report  for a liquefied petroleum gas in crystal that uses 7 tables. unfortunately, crystal cannot handle left outer join effectively. So I have to make the joins using SQL Procedures (2 inner joins and 2 left outer joins). The result is very correct, actually I pass on to the new table the fields I need in crystal. I then use this table as a new datasource for the crystal report.

My problem with the 2008 version is that since sales and credit notes are entered almost every couple of minutes, when we run the report repeatedly, the additional data are not included in the new table unless I execute the stored procedure again.

In older versions, the data is updated always every time we run the crystal report.

Is there a way that I don't have to run the stored procedure manually before running the crystal report? some sort of shall we say, it automatically updates the table once crystal report is executed. any possible command I need to insert into the stored procedure?

Thanks.

Answer 3

Did you say in Crystal Report, Refresh on Every time ran the report?

Or uncheck "Save Data in report"??

Again, I did not understand why are you saving your joins output to a table? 

I have designed a lot of complex report which were include more than 10 table and have multiple sub reports, but did not INSERT the output to a physical table.

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 4

I said before we run Crystal report, I have to manually execute the stored procedure in SQL server so that the data that crystal picks up is updated (which is not supposed to be the case)

l

Answer 5

I haven't worked with Crystal for over 5 years now, but even back then the SQL could have left outer joins. Even if you want your logic in a stored procedure, you can get the results returned directly from the stored procedure. Here's an example:

http://www.pdssoftware.com/newsletter/mar07/page6.htm

 

---

Craig Bryden - Please mark correct and/or helpful answers

Answer 6

Thanks for your advise guys... Just that last time I worked with SQL, crystal, and aspx was from 1999 until 2007 (older versions 7.0 and 2000)... I just found a little awkward with version 2008.

Answer 7

Thanks for your advise guys... Just that last time I worked with SQL, crystal, and aspx was from 1999 until 2007 (older versions 7.0 and 2000)... I just found a little awkward with version 2008.

So much is clear: this has nothing to do with SQL 2008. It's due to a flaw in your implementation.

If I understand this correctly, you first run a stored procedure that saves data into a table. Then you run a report against that table. To avoid having to run the procedure separately, invoke it from Crystal.

The best solution is to not have that table at all. Just run the procedure from Crystal and consume the result set directly. I have not worked much with Crystal, but we use Crystal in the system I work with, and the "database" for all our Crystal reports are a .TTX which defines the result set. (I don't think we call procedures directly from Crystal, though, instead we pass a recordset from Visual Basic.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

If you have problems with the JOINs in Crystal, why don't you then create Views that joins the tables and then use this/these views in Crystal? Then you don't have to populate data to a table.

---

Steen Schlüter Persson (DK)