Can I have temp tables in a Command Object?

[nscheaffer]

I have some very complex SQL that I am try to build into a report using a Command object.  I am trying to use Crystal Report for Enterprise 4.1 SP01 Patch 6 against a Microsoft SQL Server 2008 R2 database.  I have my query working in SQL Server Management Studio, but I am getting error in CR4E.

I have written two simplified queries to troubleshoot this.  This one uses a table variable ("@Affinities")...

DECLARE @Affinities TABLE
(
    AffinityID SMALLINT,
    AffinityName VARCHAR(50),
    AffinityNum VARCHAR(3)
)

INSERT INTO @Affinities    
        ( AffinityID ,
          AffinityName ,
          AffinityNum
        )

(SELECT AffinityID, AffinityName, AffinityNum
FROM dbo.luAffinity)

SELECT * 
FROM @Affinities

I get this error...

When I try using a local temporary table like this...

SELECT AffinityID, AffinityName, AffinityNum
INTO #Affinities
FROM dbo.luAffinity

SELECT * FROM #Affinities

DROP TABLE #Affinities

I get this error...

Both of these queries work in Crystal Reports 2013.  Is there a way to get this to work in CR4E?  I know that I can wrap my SQL in a stored procedure, but this would be easier.

Any ideas?

Noel

[fbio_bilicki]

Hi

the command below worked on crystal reports 2013 with sql server 2008

Care to run the command in SQL Studio, if you run it creates the table and not delete.

you can not delete the table at the end of command in crystal reports, you have to let it set there for crystal reports read data.

-- Delete Temporary Table  (Safety)
IF isnull((SELECT TABLE_NAME FROM TempDB.information_schema.tables where TABLE_NAME like '#TemporaryTest%'),'') <> ''
BEGIN
  DROP TABLE #TemporaryTest
END;

-- Create table 
CREATE TABLE #TemporaryTest (
    AffinityID SMALLINT,
    AffinityName VARCHAR(50),
    AffinityNum VARCHAR(3)
);

-- Insert Data
INSERT INTO #TemporaryTest VALUES
        (  1,
          'test',
          '001'
        );

--- select data
SELECT * FROM #TemporaryTest;

Rgs,

Fabio.

SAP 8.82 PL16 / SAP 9.0 PL13

[nscheaffer]

My report works in Crystal Reports 2013.  The issue is that I want to get it to work in CR4E.  However, it seems that I need to abandon that for now.

Noel

[JWiseman]

hi Noel,

the issue will most likely be with the INSERT INTO statement. a long time ago the C U D of crud operations would work fine in cr against sql server. however, due to changes in what can go through the driver layer, these will produce errors on most databases. this is not a crystal issue but based on what is allowed through the db's drivers.

the creation of temporary tables was also something that used to work in command objects. i'm not sure about cr4e as i don't really use it.

however, i would recommend using a SELECT WITH directive instead wherein a derived table is created. you'll need to ensure that your db & version support this. as a simple example of this...

WITH (

SELECT AffinityID, AffinityName, AffinityNum

FROM dbo.luAffinity

) AS A

SELECT *

FROM A

[nscheaffer]

So a CTE (common table expression), huh?  Seems reasonable, but I am not sure I'll be able to get it to work for my situation because I need to grab a bunch of data and then extract some data out of and XML column for each row in that first query using a cursor.

I'll report back what I come up with.  I guess one option is going back to Crystal Reports 2013, but that seems short-sighted given that it is going away in a year or so.

Noel

[JWiseman]

hi Noel,

the other way may be to use a stored procedure that handles that logic. you should in theory be able to call that stored proc in your custom sql that cr4e uses. calling a proc works within Command objects anyway.

as far as i have heard cr in the c++ designer will still be supported at least as long as bi 4.x is supported. don't quote me on that though

-jamie

[DellSC]

I doubt the "classic" version of CR will be going away anytime soon.  CR4E requires BusinessObjects or Crystal Server and there are way too many companies out there who are using Crystal without using either of those.  Also, CR4E is far from having the same functionality as classic Crystal - especially when it comes to formatting - so there's no way it will be able to replace classic Crystal at any time in the near future.

-Dell

[nscheaffer]

I tried a stored proc and still got the "Error on bindCol" error.

My manager found this post related to this issue...

In reading through the comments and suggestions I decided to try the simplest one offered by which was to use an ODBC connection instead of an OLEDB connection.  I tried my simplified scenarios and got this error...

My manager had recommended setting NOCOUNT on at the beginning of my SQL and then turning it off right before the statement that was returning the data I wanted.  That worked.  Here it is using a table variable...

DECLARE @Affinities TABLE
(
    AffinityID SMALLINT,
    AffinityName VARCHAR(50),
    AffinityNum VARCHAR(3)
)

SET NOCOUNT ON
INSERT INTO @Affinities   
        ( AffinityID ,
          AffinityName ,
          AffinityNum
        )
(SELECT *
FROM dbo.luAffinity)

SET NOCOUNT OFF

SELECT AffinityID, AffinityName, AffinityNum
FROM @Affinities

And using a local temp table...

SET NOCOUNT ON
SELECT AffinityID, AffinityName, AffinityNum
INTO #Affinities
FROM luAffinity

SET NOCOUNT OFF
SELECT * FROM #Affinities

I should note that when I tried to use a global temp table (## instead of #) it crashed CR4E.

Now to see if it works with my complex SQL.  I'll let you know.

Noel

[nscheaffer]

ARRRGGGHHHH!!!!!

[abhilash_kumar]

Hi Noel,

Have you tried creating a Relational Connection (via either CMC or IDT) and consuming that to build the command instead?

-Abhilash

[nscheaffer]

I am determined to get this to work so last evening I created an ODBC relational connection in IDT and then used that from CR4E just as you suggested.  The trick there that I always seem to get hung up on is I had to create a 64-bit ODBC data source on the server in order to be able to create the relational connection from within IDT.

So it my reports with its complex SQL (table variable, local temp table, XML processing and a cursor)  works from within CR4E designer.  Yeah!

I promoted my report and ODBC relational connection to our Test environment.  It promoted just fine.  I thought I was in business.  Not yet.  I get this when I try and run it from within the Test environment BI Launchpad...

I get the same error when I run it from the BI Launchpad for our Development environment.

I have checked the User Security on my connection and I have it set to Full Control for Everyone.  And I know that I am making a successful connection to the database because my dynamic LOVs are being populated.

I am going try promoting a report with my simplified example to our Test environment and see what happens.

Noel

[nscheaffer]

Reports with my simplified SQL work fine.

Not sure what to do next.

Noel

[nscheaffer]

I believe my issue lies with the fact that I am trying that I have a main report with a bunch of optional parameters being passed to the subreport as detailed in my document.  The subreport is where the complex SQL resides.  I can get the complex SQL to run using an ODBC connection if it is in the main report without a subreport.  However, then I lose the optional parameters.

So I guess I am going to go with Dell's advice and hope CR4E works for this sort of report architecture before classic Crystal goes away.

Noel