Skip to Content
0
Oct 08, 2014 at 03:48 PM

Can I have temp tables in a Command Object?

670 Views

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

Attachments

pastedImage_4.png (18.0 kB)
pastedImage_5.png (20.0 kB)