Invalid object - sql server 2005

[^Johnny2Bad]

I am having difficulty running a server end function called GetCustomerID. Here's the offending piece of code.


SQLStr = "SELECT CustomerID FROM dbo.GetCustomerID('" + IPAddr + "')"
SQLComm.CommandText = SQLStr
txtVisitNo.Text = Str(SQLComm.ExecuteScalar())


whenever I execute it I receive the following error message....
Invalid object name 'dbo.GetCustomerID'

Has anybody got an idea as to why this is occuring? The function is definatley on the server.

Cheers,
Jonathan

[eclipsed4utoo]

is GetCustomerID expecting a string as a parameter?

shouldn't the syntax be "owner.database.functionName"?

[^Johnny2Bad]

Thankyou for your reply.

Yes the stored function is expecting a string. I also tried owner.database.GetCustomerID, where owner is the name of the actual owner and database is the name of the database.

I'm going to play around with it a bit more, seems that some people out there prefer to call their stored functions via stored procedures. As there is no stored function option in commandtype.

Cheers,
Jonathan.

[Alsvha]

A fully qualified name is "database.owner.objectname". (not owner.database.objectname)
However your connection to the SQL Server should most often include the database in the connection, so you don't have to provide a fully qualified name, because of security and user/login administration issues. Possible the login/user is also set up incorrectly.
Also be careful to check up on if your stored procedure in fact is located in the dbo scheme and not something else.

But try out database.owner.objectname to begin with, to see if that helps the problem.

[^Johnny2Bad]

I'm afraid database.user.functionname also didn't work.

Could it be possible that a Service Pack for sql server could solve this issue? I have not applied any since installation and my download quota is a bit low.

Thanks,
Jonathan.

[Alsvha]

I'm afraid database.user.functionname also didn't work.

Could it be possible that a Service Pack for sql server could solve this issue? I have not applied any since installation and my download quota is a bit low.

Thanks,
Jonathan.

Now you say "functionname" - is it a Stored Procedure or a Function?

Are you sure you access the right database?

[^Johnny2Bad]

It is a stored function, and I am positive I am using the correct database.

Thanks,
Jonathan.

[eclipsed4utoo]

It is a stored function, and I am positive I am using the correct database.

Thanks,
Jonathan.

is this for MySQL?

[Shuja Ali]

Shouldn't you be using the function like this SQLStr = "SELECT dbo.GetCustomerID('" + IPAddr + "')"

[^Johnny2Bad]

Shuja hit the nail on the head, I was calling the function incorrectly. And just as a side note this is Microsoft SQL Server 2005.

So to call a function from SQL you use the following syntax...

SELECT dbo.functionname(param1,..., paramn)


Thankyou Shuja and all the other kind people of this site,
Jonathan.