Skip to Content
0
Former Member
Apr 08, 2016 at 02:29 PM

Convert Crystal Reports 2008 to CR 4.1 for enterprise with Command object

32 Views

Is it possible to convert a report, which contains a Command object?

The command object contains creating a temp table, a cursor, select from the temp table and dropping the temp table. Is freehand SQL supported at all?

I don't have much background in Crystal Reports (but I am a Business Object Analyst) but I have a very important Crystal Reports 2008 report, which I need to convert a to Crystal Reports for Enterprise 4.1 SP5.

It prints picking slips every ten minutes, when there is data.

Command SQL

-- store the invreserveids for picking slips to be printed in temp1 table

CREATE TABLE #Temp1 (invreserveid INT)

INSERT INTO #Temp1 (invreserveid)

SELECT invreserveid FROM YTPickingSlip3EGM

-- create temp2 table and populate with UPDATE statements from ytpickingslip3EGM (note this is EGM-IST only)

create table #Temp2 (sqlcommand varchar(500))

DECLARE @sqlCommand1 nvarchar(1000)

SET @sqlCommand1 = 'SELECT substring(SqlString,1,500) from ytpickingslip3EGM'

insert into #Temp2

EXECUTE sp_executesql @sqlCommand1

-- execute the UPDATE commands stored in the temp2 table and then delete temp2

-- *** NOTE - this is the part which writes the flags etc back to the database

DECLARE @sqlcommand varchar(500)

DECLARE db_cursor CURSOR FOR

SELECT sqlcommand FROM #Temp2 ORDER BY 1

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @sqlcommand

WHILE @@FETCH_STATUS = 0

BEGIN

-- PRINT @sqlcommand

EXEC (@sqlcommand)

FETCH NEXT FROM db_cursor INTO @sqlcommand

END

CLOSE db_cursor

DEALLOCATE db_cursor

drop table #Temp2

-- use the invreserveids saved in temp1 to get and print the picking slips

SELECT *

FROM YTPickingSlip2

WHERE invreserveid IN (SELECT invreserveid FROM #Temp1)

DROP TABLE #Temp1

-- end of process

Attachments

Picking slip.png (29.2 kB)