Skip to Content
avatar image
Former Member

Map Fields, store procedure results is "Expr1000"

Hi guys.

This is my problem.

I have a sql server 2005 database. In this database i have a store procedure (3100 lines, with temporal tables, unions .. etc).

When i try to create the report in CR9, i do this :

1) i'm go to "set datasource location"

2) select the database

3) select the store procedure

4) put some parameters

5) Accept

When i want to add the fields to the report ....

the only results from the SP is a result field called "Expr1000".

I execute the store in my sql server and this give me all the columns results (174 columns).

My question is:

What is the error , that in CR9 i can't see all the columns results , and only i can see "Expr1000".

Thanks !!!!!

Pablo Reynoso.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 04, 2008 at 06:34 PM

    Pablo,

    What connection method are you using? ODBC or OLEDB?

    Do you get the correct results in a third party testing utility such as QTADO or QTODBC? (available for download at http://home.att.net/~gpoulose/ ? This is a better test than doing it within MSSQL as that uses a native connection to your database.

    How many result sets is the SP returning?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      This is definitely not the solution for everyone but reading this thread made me do a little more checking on my own.

      I too was receiving the "Expr1000" field from the SP when linking it into a new report or even when trying to MAP fields in an existing report template. In the Map Fields dialog the source SP had no fields displayed for Mapping.

      What I discovered was S.U.E. (stupid user error). While testing some changes to the source SP I changed the return result from returning data to returning the actual sql code I was constructing in the SP. I often write the code into a local string variable and then use the 'exec(@str);' command as the final step of the SP. I changed that code to 'Select @str;' which returned the @str string value when I executed the SP from within SQL Manager - to see the actual sql code.

      I never changed the code back to using the 'exec(@str);' so I was returning a 4,000+ char string from the SP which when loaded into Crystal the SP "field" became "Expr1000".

      After making this little change, returning the data instead of a single LARGE string the report behavior returned to what is was before the testing snafu.

      Hope this helps someone else, at least maybe point them to their code changes and not necessarily the CR template.

      Dave