May 4, 2006 at 5:53 am
I have written a user defined function in sql server to get the gmt time. Below is the function i have written
CREATE FUNCTION GetGMT (@InDateTime datetime)
RETURNS datetime AS
BEGIN
DECLARE @OutDatetime datetime
SET @OutDatetime = dateadd(n,-150,@InDateTime)
RETURN @OutDatetime
END
I have only one login name for the sql server ie 'sa'. Now when i try to access the function like the following
select GetGMT(getdate())
it does not work
but if i specify select dbo.GetGMT(getdate())
it works.
Is it compulsory to give the full qualified name of the object. The issue is when this application is deployed on the client, the owner can change.
May 4, 2006 at 5:56 am
Yes you do have to qualify with the schema owner all user defined functions.
But I am a bit confused on how this gives you GMT outside of your request.
May 4, 2006 at 6:32 am
Thanks for your reply
What if i am migrating the database to a new system (or might be i am hosting the application under shared hosting), then the username is the owner of the objects created. In that case do i have to use
select username.GetGMT(getdate())
Any other way i can accomplish it.
Also the function is not rightly written, we were just testing it
May 4, 2006 at 6:38 am
There's just no way around this one... BTW you should do that for ALL objects. It avoids a whole lot of troubles down the line.
Check it out in the best practices section of the articles.
May 4, 2006 at 7:20 am
While this thread was useful for you from the standpoint of understanding how to call udfs, you might want to look at the T-SQL GetUTCDate() function before you reinvent the wheel. It should be quite a bit faster than a T-SQL udf.
May 4, 2006 at 8:43 am
You can always create the objects whith the owner schema set explicit.
CREATE FUNCTION dbo.GetGMT(...
But yes for you to use the function you will have to use the two part name
[schema owner].[object]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply