Hi everybody!

I've been doing regular SQL statements and calling stored procedures for a while but I just tried to use a user defined fucntion in an SQL statement and I get an error:

(FOC1400) SQLCODE IS 208 (HEX: 000000D0) XOPEN: 42S02
: Microsoft OLE DB Provider for SQL Server: [42S02] Invalid object name 'd
: bo.fnWeekdaysDateDiff'. [42S02] Invalid object name 'dbo.fnWeekdaysDateD
: iff'. [42000] Statement(s) could not be prepared. [] Deferred prepare co
: uld not be completed.

I've searched the Inforesponse Support database and it's not in Supported nor Unsupported features for SQL

The SQL works fine outside WF:

SELECT
tbl0107_frmMedInfo_CCReports_ID AS ID,
'Investigation' AS TYPE,
CASE InvestigationStatus
WHEN 2 THEN dbo.fnWeekdaysDateDiff (InvestigationOpenDate, InvestigationClosedDate)
ELSE dbo.fnWeekdaysDateDiff (InvestigationOpenDate, GetDate())
END AS NBDAYS,
CASE InvestigationStatus
WHEN 0 THEN '(New)'
WHEN 1 THEN '(Open)'
WHEN 2 THEN '(Closed)'
WHEN 3 THEN '(Pending)'
END AS STATDESC
FROM pms_Infomanager..tbl0107_frmMedInfo_CCReports

What is wrong?

Thanks!

I think this is an MS SQL Issues. If you look at the error message MS SQL Returned this error message. If you have multiple SQL Connections or multiple database make sure you set the correct one. If multiple Database try using the fully qualified name if the FROM Statement.

Hope this helps

My question would be; Does the user for the Data Adapter connection on the WF Reporting Server have the same priveledges in SQL server as you do when you run the query standalone?

Isn't it what I'm doing?

FROM pms_Infomanager..tbl0107_frmMedInfo_CCReports


(pms_Infomanager is the database name and tbl0107_frmMedInfo_CCReports is the table name)

quote:

Originally posted by KevinG:
My question would be; Does the user for the Data Adapter connection on the WF Reporting Server have the same priveledges in SQL server as you do when you run the query standalone?

Yes! the IBI user has execute privileges on the function

quote:

Originally posted by TexasStingray:
I think this is an MS SQL Issues. If you look at the error message MS SQL Returned this error message. If you have multiple SQL Connections or multiple database make sure you set the correct one. If multiple Database try using the fully qualified name if the FROM Statement.

Hope this helps

Thanks for your idea TexasStingray, I've added the databasename in front of the ownername when calling the function and it worked!

pms_Infomanager.dbo.fnWeekdaysDateDiff (InvestigationOpenDate, InvestigationClosedDate)

Thanks again for the quick reply.

Cheers.

Sorry Flipper,

Let me back up a minute...Does the data adapter on your WF Reporting Server (EDASERVE) use Explicit, Password PassTHRU, or trusted security?

Kevin

quote:

Originally posted by KevinG:
Sorry Flipper,

Let me back up a minute...Does the data adapter on your WF Reporting Server (EDASERVE) use Explicit, Password PassTHRU, or trusted security?

Kevin

trusted

OK... "Trusted Security" is one major difference between your configuration and the ones I have used over the years. We use explicit security. We have control that way over the priveledges the "Explicit" user has in the RDBMS. Since trusted security uses an impersonated operating system login, could it be that whatever login is used by the IWay adapter does not have permissions to the user defined function? It has been a couple of years since I have dealt directly with MSSQL Server and WebFOCUS, but we have no difficulty with our configuration against DB2...other than occasionally building a SQL statement that is too large to pass.

Anyone else have any other ideas?

Kevin

Thanks Kevin but I already have my answer... the dbname was missing in front of the ownername in my select, works fine now.