I want to learn how to create crystal report off of SQL Server 2005 Stored Procedure. So anybody please let me know from where I can download the documentation on "Requirements of SQL Server 2005 Stored Procedures for crystal report.
I am using Crystal Report XI. Any help is greatly appreciated.
Actually you don't need to use the Add Command. Simply connect to the Stored Procedure and CR will create the SQL to connect for you and prompt for any SP Paraemters required.
Any sample SP on MS SQL sample DB's will work. Only real requirement is that you use an IN/OUT cursor and be aware that the last SELECT statement in the SP is where crystal expects the data to come from.
I've tried both of these methods for an SP that runs fine in SQL Server 2005 query analyzer. In Crystal, I can see the stored procedure in the list and can call from a Command, but instead of a data return I get "Failed to retrieve data from the database: null"
I have noticed that this occurs when I use #Tmp Tables in my stored procedure. If I use dynamic sql with an EXEC() or just one long query the data connectivity works fine. In this particular case two #Tmp Tables are built with various data manipulations and the final select is dynamic with an EXEC() statement, but in the dynamic sql it joins two #tmp tables together - a situation I'd be hard pressed to avoid.
Why don't stored procedures with #Tmp Tables work? ,,,, going to try to find something about this in documentation, I've had zero success over the course of 5.5 months now...
When CR uses Stored Porcedures we only look at the last SELECT statement. We can't use TMP tables because they are owned by the system and not the user. Even though you created them under your logon credentials you are not the actual owner. Or so I have found so far.... CR does not have permissions to get to those temp tables. If you can find out dynamically what the table names are called then it may work. I don't have a sample but it has the usual table info with a random GUID or temp name, really long, and changes every time.
What should work is create a real table, not one of MS's tempTables and then push all your data to that table and then the last select statement returns all of your data from that temp table:
SELECT * FROM MYTEMPTABLE.DBO.TABLE.
Then CR should ahve no issues.
It may be that the JDBC driver has problems. Test it using OLE DB or ODBC.
Please refer to this [Crystal Reports & Stored Procedure PDF|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/40a8a5f2-a81e-2b10-da9f-9282db9fedae]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.