Sql-server – Crystal Reports and stored procedures with parameters

crystal-reportssql serverstored-procedures

I work with Crystal Report 2008 sp2, and during creation of a new report template I've encountered a problem. I created a stored procedure, which prepares and pivots needed data on the server side, and filters it using a parameter.

When I try to add this stored procedure to the report template in Crystal Editor, I receive SQL error 102 – incorrect syntax near ')'. Although I can easily add user defined function to my report with exactly the same parameter.

What could be the source of the problem?

P.S. Stored procedure does run and return correct data if I run it in the SSMS.

Best Answer

As long as you are returning a record set from the store procedure try using a command object

in the command object:

EXEC mystoredProc ({?variable1}, {?variable2})

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Mysql – List of Stored Procedures/Functions Mysql Command Line

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

Best Answer

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

Mysql – List of Stored Procedures/Functions Mysql Command Line

Related Topic