Skip to Content
author's profile photo Former Member
Former Member

8,81 PL07 database login issue for Crystal layout

Hi All

Upgraded from 8.8 PL18 to 8.81 PL07 running on Windows Server 2003 and SQL Server 2008 R2.

Testing on server.

Opened an existing doc layout which works fine in 8.8 PL18 based on OINV in Crystal designer, created a new datasource using SAP B1 data source type pointing to mu upgraded database.

Mapped all tables and the stored procedure to the required tables in the new datasource for the main report and subreports.

Did test run in Crystal and worked OK

Did 'Preview in SAP Business One' and got an error after entering the docentry and objectid fields as follows.

Database Connector Error :''

Failed to retrieve data from the database

Error in file ~crw{lots of junk for the temp file name}{more junk}.rpt

Database Connector Error

Looked in the SQL Profiler and had the error below when the parameter prompt was displayed in B1.

2011-08-01 14:20:07.93 Logon Error: 18456, Severity: 14, State: 8.

2011-08-01 14:20:07.93 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

I saved the report into B1 and tried to print an invoice and had the same errors.

In the profiler the error is preceded by calls reading the details froim the RDOC table. Looking at the queries after the error it looks like it is trying to read the various tables in the layout.

Rob

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Sep 07, 2011 at 12:48 PM

    Hi Rob,

    after upgrade to 8.81 PL07 we have the same issue on reports.

    What does it means: 'Changed the fields to be other than nvarchar(max) and it all worked OK'.

    What can I change in the report or in the field definition in sap???

    Thanks

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Domenico

      When B1 creates a character UDF it creates it in the database as nvarchar(max) rather than the exact size that was specified for the UDF.

      Crystal can not use these for joins or filters, and it seems that in PL07 it doesnt like them at all.

      All you need to do is in the UDF screen in SAP, add a key to the table which consists of all of the character UDF's in the table. This will create an index in SQL Server and this will force the fields to be the specific size. You can then remove the key because the additional index in the database may cause performance issues.

      When you open the report in Crystal designer you then Verify Database and all should be OK.

      Rob

  • author's profile photo Former Member
    Former Member
    Posted on Aug 02, 2011 at 12:06 AM

    Problem was due to UDF's on OINV which were created in the database as nvarchar(max) and which were used in a formula field in the report.

    Under 8.8 PL18 these worked OK since the fields were not in either a join or a database selection but it seems that in 8.81 it is a bit more sensitive.

    Changed the fields to be other than nvarchar(max) and it all worked OK

    Hope this is helpful to others

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Carl

      CR will not be able to use the fields for joins or filters. You will probably have the problem when calling through SAP since I also experienced it where I was pulling an nvarchar(max) field into a view so it is not just SAP getting confused with its own fields.

      The simple solution with the view is to cast the field in the view select.

      Rob

  • Posted on Sep 08, 2011 at 09:57 AM

    Great Rob, many thanks...I have just solved the issue!

    However this is an heavy bug that forced me to identify all the alphanumeric UDF involved in my reports!!!!

    So I think I will open a support case at SAP.

    Domenico

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.