cancel
Showing results for 
Search instead for 
Did you mean: 

error message 42000 and WIS 10901

Former Member
0 Kudos

Hi, I have downloaded BO edge 3.1 from SAP website recentlty installed it myself.

While creating objects some are giving error messages when I try to parse them. there are some that parse sucessfully but when I try to use them in webi report they give me an error message. I have listed them below please advise what could be possible resons and how I can fix them.

Object name:Count distinct Companies

Selects statement:Count (distinct (@Select(Portfolio Transactions\Ticker)))

Parse:failed

Error message:Parse failed: Exception:DBD, [Microsoft][ODBC Microsoft Access Drive] Undefined function 'distinct' in expression.State:42000

webi report error message: A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'distinct' in expression.. (WIS 10901)

Object name: Distinct Companies

Select statement: Distinct(@Select(Portfolio Transactions\Ticker))

Parse: ok

Webi report error: yes

Webi report error message: A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Distinct' in expression.. (WIS 10901)

Object name: Distinct Count Companies

Select statement: Distinct (Count (@Select(Portfolio Transactions\Ticker)))

Parse: ok

Webi report error: yes

Webi report error message: A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Distinct' in expression.. (WIS 10901)

Object name: Trans Date - month name

Select statement: datename(mm,PORTFOLIOTRANSACTIONS.TRAdateparNS_DATE)

Parse: failed

Parse failed: Exception:DBD, [Microsoft][ODBC Microsoft Access Drive] Undefined function 'datename' in expression.State:42000

Webi report error: yes

Webi report error message: A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'datename' in expression.. (WIS 10901)

Object name: Trans Date - month n

Select statement: datepart(m,PORTFOLIOTRANSACTIONS.TRAdateparNS_DATE)

Parse: ok

Webi report error: yes

Webi report error message: A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.. (WIS 10901)

Object name: Trans Date - day

Select statement: datename(dw,PORTFOLIOTRANSACTIONS.TRAdateparNS_DATE)

Parse: failed

Parse failed: Exception:DBD, [Microsoft][ODBC Microsoft Access Drive] Undefined function 'datename' in expression.State:42000

Webi report error: yes

Webi report error message: A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'datename' in expression.. (WIS 10901)

Object name: Trans Date -Year n

Select statement: datepart(yyyy,PORTFOLIOTRANSACTIONS.TRAdateparNS_DATE)

Parse: ok

Webi report error: yes

Webi report error message: A database error occured. The database error text is: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.. (WIS 10901)

Object name: Count Companies

Select statement: Count (@Select(Portfolio Transactions\Ticker))

Parse: ok

Webi report error: ok

Thanks

Ali

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

The error messages are telling you exactly what is wrong.

Microsoft Access does not support a DISTINCT count. You can count, but you cannot apply the distinct check on the count, therefore you have to remove that clause from your object definition if you want the object to function. Or you can try using a more robust database that does support distinct counts.

The rest of the errors that I read are also syntax errors based on your object definitions. For example:

MicrosoftODBC Microsoft Access Drive Undefined function 'datename' in expression.

That tells you that Access does not offer a "datename" function and you need to find an alternative.

I suspect that you are taking a universe based on SQL Server and trying to use it for Access? There might be a document on the Microsoft web site that details that conversion process (what functions map to which alternate functions available on Access).

Former Member
0 Kudos

Hi Dave,

Thanks for your reply .

Yes, you are right about universe based on SQL, during installation it asked for database details, in response I did not entered any database details hence it automatically installed SQL (by default).

I will appreciate if you could also advise on below

- Is there any way I can configure it to work for access?

- Do I have to re install BO and select access database while installation?

- Will it work OK, If I use an SQL database for practice?

Ali

Former Member
0 Kudos

You don't have to reinstall BusinessObjects just to change databases... your challenge is that you appear to have a universe built for MS SQL Server pointing to MS Access. All you have to do is correct the syntax of each object that is failing. As I said, DISTINCT does not work for MS Access, so remove it. Find the appropriate function for Access that replaces the other date manipulation functions being used and you should be fine.

If you installed the "demo kit" with your BusinessObjects software, you should have the Island Resorts and eFashion universes to play with. They are both based on Access.

Finally, just to be clear, you can address any number of different databases with BusinessObjects. I have Oracle, DB2, Teradata, and Access universes all on my laptop, and all at the same time. You just need to make sure the syntax of your universe objects matches the target database.

Answers (0)