CR2008SP4/SQL 2008 10.50.1617/ODBC SQL Native Driver/Win7-64bit

39 Views

Hi, hope somebody can help.

I've created a very simple report based on the results of a stored procedure which accepts a single parameter. The parameter value will dictate what resultset is passed back to the report. E.g.

CREATE PROCEDURE MyProc @MyParm int AS

If @MyParm = 1 GOTO DoThis

If @MyParm = 2 GOTO DoThat

GOTO ExitThisProcedure

DoThis:

SELECT MyFile, MyLocation

FROM MyTable

GOTO ExitThisProcedure

DoThat:

SELECT MyBox, MyContent

FROM MyTable2

GOTO ExitThisProcedure

ExitThisProcedure:

Obviously, if I pass @MyParm = 1, I want to return data from table MyTable, and if I pass @MyParm = 2, I want to return data from table MyTable2.

Now, this works perfectly well when I use the OLE DB (ADO) driver in CR2008, but my understanding is that in the future OLE DB will not be supported. When I use ODBC, I do not get the result set that I would expect. It appears to always return the first resultset regardless of the parameter value.

This whole scenario is occurring in the Crystal Reports designer environment. The SP works as expected if I run the various permutations in SSMS 2008.

My question is, is this a limitation of ODBC, the SQL Native Driver, 64-bit or something else? Or, is there something else I am missing?

Thanks in advance.

Regards

Julie