SQL Server Vender Specific Function not working after Migration from Cognos 10

[MrChasCognos]

Greetings!

We are working on a Cognos Upgrade from 10.2.2 to Cognos CA and my reports that are using SQL server Functions, such as datepart are not working in Cognos 11.  We are on SQL SERVER version 11.0.6607.  There are more functions not working , but datepart just the one I am using as an example here.  Also these are reports that came from and were working in Cognos 10.2.2.

Any Ideas?  Wondering if there is something more/different that is needed to get Vender specific functions working in Cognos 11?

Thanks

Charlie

[MrChasCognos]

Some additional information.  I  tested all SQL Server Specific date functions, none of them are working in Cognos CA.  I also tested some of the non-date function SQL server Vender Specific functions and they are working...  This is a head scratcher....

[Lynn]

Ah, the dreaded "not working" problem. Could you share more about what "not working" means? Do you get an error message? If so, what is the message? Does it return a result that is not correct or as expected? If so, give us some examples of what you expect, what you get, and how you called the function. Is it something other than an error message or an unexpected result? Are there any other differences between your 10 and your 11 environment?

[MrChasCognos]

Ya, good one, I deserved that, should have provided error message..etc....   here is an example of errors receiving:  UDA-SQL-0219 The function "datepart" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042

Something interesting found out this morning, same code works in our Cognos CA UAT environment... wondering if it is something related to Configuration setup?    I don't have a lot of info on the differences between the environments what I was told before upgrade occurred that it was going to be an Intel/windows based environment vs we were on Unix in Cognos 10.2.2 where this is working... but as stated, new info, is that in the Cognos UAT environment , same report/functions are working. 

[MFGF]

Ya, good one, I deserved that, should have provided error message..etc....   here is an example of errors receiving:  UDA-SQL-0219 The function "datepart" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042

Something interesting found out this morning, same code works in our Cognos CA UAT environment... wondering if it is something related to Configuration setup?    I don't have a lot of info on the differences between the environments what I was told before upgrade occurred that it was going to be an Intel/windows based environment vs we were on Unix in Cognos 10.2.2 where this is working... but as stated, new info, is that in the Cognos UAT environment , same report/functions are working.

The error message indicates you are using Compatible Query Mode - at least on this server anyway. How is the connection to SQL Server defined? ODBC? OLE-DB? Native Client? Is it the same on the old server? Do you have the same SQL Server client libraries installed on both machines?

The error indicates you are trying to use a database function in an operation where local processing is required on the Cognos server, so the database function is not available to call. Do you see this for just a specific report or set of reports or is it every time you try to call a native function?

MF.