SQL in Crystal: Associated statement is not prepared

[Madawc]

I had a record selection that was too complex for Crystal, so I used SQL to assemble the data I wanted. (SQL Version 8).

I still wanted to use Crystal's superior reporting features, so I added it into a Crystal report (Crystal 10).

My SQL used a series of temporary tables, and this was the thing that gave trouble. In SQL I could drop them at the end and be sure they were gone. In case of a failure half-way through I could also drop them at the start and get just a warning.

With SQL in Crystal, things were trickier. First time, it refused to run with a "DROP" at the start. But thereafter it would find a duplicate my temporary file names and I had to DROP them at the start, or else change them each time. This applied even if I closed the report or closed and re-started Crystal.

Having got this far, I then began to get a message

Query Engine Error 'HY007:[Microsoft][ODBC SQL Server Driver] Associated statement is not prepared

This would happen within the SQL when I tried to edit it. Also within the run whenever I added a new data item from the regular database.

By closing, reopening and restarting each time it fails, I have got the report to run. But if anyone can figure what I should be doing, I'd appreciate it.

Note that this is purely a problem of SQL in Crystal. SQL as such is fine.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

Temporary tables will clean themselves up after the session closes if you use a prefix of # or ## (the first being a local, the latter a global temp table), you don't need to do it yourself (though it's a fairly common thing).

So the short answer is you don't need a drop at the start, just write good code, and if you feel that you need to issue a drop in case there are difficulties, do so in an error trap and make sure that the SP still does a select of the same fields in the end.

As you know, if you make any changes to the SP, prior to excuting it in Crystal, do a Database->Verify Database to update the report.

Here's the BO whitepaper for writing SQL Server SPs with Crystal:

http://support.businessobjects.com/library/kbase/articles/c2005680.asp?ref=devzone_reporting_main

-k

 

[Madawc]

I do use the # prefix and the tables do not clean themselves up. This may be due to my set-up: I was hoping to learn exactly why.

The whitepaper is about SQL in Crystal 8.5, which was different in its use of SQL and did not have this problem.

I did find a work-round. In SQL, I had been dropping tables at the end, so I could re-run the same code without ending and restarting the session. For SQL in Crystal, I had been dropping the tables at the start and also the end. When I just dropped them at the start, it ran OK.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Madawc]

I found a solution at the SQL forum: make the 'drop' conditional. Since the code isn't obvious, I'll post it here in case anyone else has the same problem.

IF EXISTS (select * from tempdb.dbo.sysobjects where name like '#Temp_AA%')
	DROP 	TABLE #Temp_AA

Put at the start of the SQL, this seems to always work OK.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10