July 10, 2002 at 9:31 am
I'm using Acess XP and SQL 2000. I have a user-defined scalar function that returns the value "A" and is located on the SQL Server. When calling this function in the Access XP ADP as as a user who is the dbo and also a system administrator, the function returns "A". If the same function is called by a user who is the dbo and NOT the system administrator, the function returns "". To open the function I double click on it in the Access XP query window.
What is going on? Is there a permission that needs to be granted to the user who is the dbo and NOT the system administrator? Is there a driver bug? Any help would be appreciated. Thank you in advance.
Here's the function:
ALTER FUNCTION dbo.ufnReturnA ()
RETURNS char(1)
AS
BEGIN
RETURN 'A'
END
July 10, 2002 at 10:29 am
Is the user calling this as dbo.function?
Steve Jones
July 10, 2002 at 10:34 am
Yes. The function call is dbo.ufnReturnA. I have been testing by using Access XP and double clicking on the function ufnReturnA. If the Access XP connection is via a user who is dbo and supervisor, it works. If not a supervisor it does not work. In the query window the query is named "ufnReturnA (dbo)" suggesting that the Access XP connection does not recogize the user login as a dbo.
quote:
Is the user calling this as dbo.function?Steve Jones
http://www.sqlservercentral.com/columnists/sjones
July 10, 2002 at 10:43 am
That's possible. Apologies, but I'm not an Access person. Hopefully Andy or someone else will chime in.
Steve Jones
July 10, 2002 at 10:59 am
Take a look at the code it is generating for the query... it might just be building it wrong.
You could always create the query manually:
Create a new query in design view.
Go to the SQL View.
Select Pass Through Query from the Query menu.
Set the connection in the properties menu.
Then type your SQL Statement:
select dbo.udfReturnA()
Click on run and see if it works.
This works fine for me in Access 2000.
July 10, 2002 at 11:48 am
Since this is a Access Data Project, pass through queries are not available. I think it might work, however, to create the functions on the fly in the ADP, since the ownership goes to the current user. Perhaps, I can try creating a set of scalar functions for the user that is not a supervisor.
At 7/10/02, 12:45 MST
Although it is puzzeling, I've moved on. The code that calls the scalar functions works fine in the ADP as long as the dbo prefix is present. The above problem appears to be an inconsistency in the way the Access XP ADP query window is implemented. I am guessing double clicking on the "ufnReturnA (dbo)" function prefixes the call to SQL with username.ufnReturnA (dbo) and something gets lost along the way.
Thank you everyone for your responses.
quote:
Take a look at the code it is generating for the query... it might just be building it wrong.You could always create the query manually:
Create a new query in design view.
Go to the SQL View.
Select Pass Through Query from the Query menu.
Set the connection in the properties menu.
Then type your SQL Statement:
select dbo.udfReturnA()
Click on run and see if it works.
This works fine for me in Access 2000.
Edited by - Bill Hallinan on 07/10/2002 12:47:09 PM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply