Map Fields, store procedure results is "Expr1000"

[Former Member]

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.

[former_member260594 · Accepted solution]

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?

[Former Member · Accepted solution]

Hi Graham.

The connection is over ODBC.

The server is running in other city. and i connect over a VPN.

i connect from my machine over ODBC to the server.

i don't know what is the problem with the CR9 ...

the SP return 6 records ... but only one column ! (Expr1000).

This "Expr1000" is the first column of the SP.

[Former Member · Accepted solution]

Hi guys ... i have some info that helps.

I try with another machine on my net, with CR6 .. and works perfectly !!!

next. From my machine with the CR9 using ADO, the SP give me all the fields.

Then .. the problem is ODBC with CR9 !! ... but i don't understand why works with CR6 and not CR9.

bye

[Former Member · Accepted solution]

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