problem calling sql function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

problem calling sql function

Author

Topic

exorbitant
Starting Member

17 Posts

Posted - 2008-12-23 : 07:46:06

Hi all,

I've a sql function that takes a string of comma separated uniqueidentifiers, split them, put them in a table and return the table. I am calling this function in a stored procedure but there raised an exception always as "Invalid object name: dbo.<function name>"
I've tried by changing the name of function, checked for user permission, copying the function in another database and tried it in other database as well but same problem always...

any suggestion???

Thanks and regards,

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-23 : 07:49:41

Can you post the code where you are calling the function.
A function call should ideally look like this:

select [dbo].[CompareStrings_Fn] ('greater london','london inner area')

exorbitant
Starting Member

17 Posts

Posted - 2008-12-23 : 08:06:06

quote:
Originally posted by sakets_2000

Can you post the code where you are calling the function.
A function call should ideally look like this:

select [dbo].[CompareStrings_Fn] ('greater london','london inner area')

Here is the stored procedure where I am calling the function:

CREATE PROCEDURE InsertOrUpdateIntoCompany_ByNF
@COMPANYNAME NVARCHAR(50),
@ADDRESS NVARCHAR(255),
@BUSINESSPHONE NVARCHAR(50),
@PROVINCE UNIQUEIDENTIFIER,
@CITY NVARCHAR(50),
@COUNTRY UNIQUEIDENTIFIER,
@ZIPCODE VARCHAR(50),
@POSITION NVARCHAR(50),
@SALUTATION NVARCHAR(50),
@LASTNAME NVARCHAR(50),
@FIRSTNAME NVARCHAR(50),
@UserId UNIQUEIDENTIFIER,
@COMPANYID UNIQUEIDENTIFIER,
@ApplicationId UNIQUEIDENTIFIER,
@DISPLAYNAME NVARCHAR(50),
@WEBADDRESS VARCHAR(255),
@LONGITUDE DECIMAL,
@LATITUDE DECIMAL,
@ACTIVATE CHAR(1),
@DELETE CHAR(1),
@COMPANYINFO NVARCHAR(1000),
@CATEGORYID VARCHAR(3000),
@ADDRESSINGOOGLE CHAR(1),
@PROCROLE VARCHAR(50),
@TEMPTAG CHAR,
@TEMPDATE DATETIME,
@FAX NVARCHAR(50)

AS
IF(@PROCROLE='INSERT')
BEGIN
INSERT INTO COMPANY
(COMPANYNAME,ADDRESS,BUSINESSPHONE,PROVINCE,CITY,COUNTRY, ZIPCODE,[POSITION],SALUTATION,LASTNAME,FIRSTNAME, UserId,COMPANYID,ApplicationId,DISPLAYNAME,WEBADDRESS,LONGITUDE, LATITUDE,ACTIVATE, [DELETE],ADDRESSINGOOGLE,COMPANYINFO,TEMPTAG,TEMPDATE,FAX)
VALUES
(@COMPANYNAME,@ADDRESS, @BUSINESSPHONE,@PROVINCE,@CITY,@COUNTRY, @ZIPCODE,@POSITION,@SALUTATION,@LASTNAME, @FIRSTNAME, @UserId,@COMPANYID,@ApplicationId,@DISPLAYNAME, @WEBADDRESS,@LONGITUDE, @LATITUDE,@ACTIVATE, @DELETE,@ADDRESSINGOOGLE,@COMPANYINFO,@TEMPTAG,@TEMPDATE,@FAX)

DELETE FROM COMPANYDEALSIN WHERE COMPANYID=@COMPANYID

INSERT INTO COMPANYDEALSIN
(COMPANYID ,DEALSINID)
VALUES
(@COMPANYID,dbo.SplitUniqueidentifiersArray(@CATEGORYID))
END

ELSE IF(@PROCROLE='UPDATE')
BEGIN
UPDATE COMPANY
SET COMPANYNAME =@COMPANYNAME, ADDRESS =@ADDRESS, BUSINESSPHONE =@BUSINESSPHONE, PROVINCE =@PROVINCE, CITY =@CITY, COUNTRY =@COUNTRY, ZIPCODE =@ZIPCODE,
[POSITION] =@POSITION, SALUTATION =@SALUTATION, LASTNAME =@LASTNAME, FIRSTNAME =@FIRSTNAME, DISPLAYNAME =@DISPLAYNAME, WEBADDRESS =@WEBADDRESS,
LONGITUDE =@LONGITUDE, LATITUDE =@LATITUDE, ACTIVATE =@ACTIVATE, [DELETE] =@DELETE, ADDRESSINGOOGLE =@ADDRESSINGOOGLE, COMPANYINFO =@COMPANYINFO, TEMPTAG =@TEMPTAG,TEMPDATE =@TEMPDATE,FAX=@FAX

DELETE FROM COMPANYDEALSIN WHERE COMPANYID=@COMPANYID

INSERT INTO COMPANYDEALSIN
(COMPANYID ,DEALSINID)
VALUES
(@COMPANYID,dbo.SplitUniqueidentifiersArray(@CATEGORYID))
END
GO

And here is the function:

CREATE FUNCTION dbo.SplitUniqueidentifiersArray
(
@OrderList varchar(3000)
)
RETURNS
@ParsedList table
(
OrderID uniqueidentifier
)
AS
BEGIN
DECLARE @OrderID varchar(50), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS uniqueidentifier)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END
RETURN
END

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-23 : 08:30:24

You sure you have permissions on the objects ?

exorbitant
Starting Member

17 Posts

Posted - 2008-12-23 : 08:35:57

quote:
Originally posted by sakets_2000

You sure you have permissions on the objects ?

Yeah I am sure about it. I am trying to run it as user "sa" and it is a user of administrator group you know having permissions of administrator.
So, what can I do to resolve this issue. I am totally fed up...

Thanks and regards,

exorbitant
Starting Member

17 Posts

Posted - 2008-12-23 : 12:39:51

is there any help???????????????? can anyone figure out the problem??? any suggestion?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-23 : 12:58:47

The object is probably not owned by dbo.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

exorbitant
Starting Member

17 Posts

Posted - 2008-12-24 : 05:24:43

quote:
Originally posted by tkizer

The object is probably not owned by dbo.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

OK thanks,
but how can I change its ownership? How can I grant access of this function to dbo?

Thanks and regards,

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-24 : 05:54:21

quote:
Originally posted by tkizer

The object is probably not owned by dbo.

But the code of his function specifies the owner name

CREATE FUNCTION dbo.SplitUniqueidentifiersArray...

Are we sure that SplitUniqueidentifiersArray exists in the same schema ??

quote:
but how can I change its ownership? How can I grant access of this function to dbo?

If you created the function as a member of sysadmin role, the owner would be dbo only. Regardless, you could use sp_changeobjectowner to change the same.

exorbitant
Starting Member

17 Posts

Posted - 2008-12-24 : 06:39:17

quote:
Originally posted by sakets_2000

quote:
Originally posted by tkizer

The object is probably not owned by dbo.

But the code of his function specifies the owner name
CREATE FUNCTION dbo.SplitUniqueidentifiersArray...

Are we sure that SplitUniqueidentifiersArray exists in the same schema ??

quote:
but how can I change its ownership? How can I grant access of this function to dbo?

If you created the function as a member of sysadmin role, the owner would be dbo only. Regardless, you could use sp_changeobjectowner to change the same.

Yeah the function is in the same schema. I've defined function in the User Defined Functions of the same database.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-24 : 07:01:27

Can you just check for the owner ?? Does it say its owned by dbo ?

exorbitant
Starting Member

17 Posts

Posted - 2008-12-24 : 07:11:40

quote:
Originally posted by sakets_2000

Can you just check for the owner ?? Does it say its owned by dbo ?

Yeah it says that the owner is dbo and owned by dbo.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-24 : 07:32:28

Are you able to just the run the function separately without the sp?

exorbitant
Starting Member

17 Posts

Posted - 2008-12-24 : 08:03:26

quote:
Originally posted by sakets_2000

Are you able to just the run the function separately without the sp?

How can I run the function separately without stored procedure?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-24 : 09:01:31

exec dbo.SplitUniqueidentifiersArray('<SomeRandomCategoryId>')

exorbitant
Starting Member

17 Posts

Posted - 2008-12-24 : 09:27:54

quote:
Originally posted by sakets_2000

exec dbo.SplitUniqueidentifiersArray('<SomeRandomCategoryId>')

I've tried the following combinations:

1) exec dbo.SplitUniqueidentifiersArray('{E418C0F3-D729-423D-83BB-0C59FB47F376},{E418C0F3-D729-423D-83BB-0C59FB47F377}')

2) exec dbo.SplitUniqueidentifiersArray('{E418C0F3-D729-423D-83BB-0C59FB47F376}')

3) exec dbo.SplitUniqueidentifiersArray('abc')

4) exec dbo.SplitUniqueidentifiersArray('abc,def')

but everytime I get the same exception as:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '{E418C0F3-D729-423D-83BB-0C59FB47F376},{E418C0F3-D729-423D-83BB-0C59FB47F377}'.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-24 : 09:32:29

quote:
Originally posted by exorbitant

quote:
Originally posted by sakets_2000

exec dbo.SplitUniqueidentifiersArray('<SomeRandomCategoryId>')

I've tried the following combinations:

1) exec dbo.SplitUniqueidentifiersArray('{E418C0F3-D729-423D-83BB-0C59FB47F376},{E418C0F3-D729-423D-83BB-0C59FB47F377}')

2) exec dbo.SplitUniqueidentifiersArray('{E418C0F3-D729-423D-83BB-0C59FB47F376}')

3) exec dbo.SplitUniqueidentifiersArray('abc')

4) exec dbo.SplitUniqueidentifiersArray('abc,def')

but everytime I get the same exception as:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '{E418C0F3-D729-423D-83BB-0C59FB47F376},{E418C0F3-D729-423D-83BB-0C59FB47F377}'.

remove the brackets and try.

exec dbo.SplitUniqueidentifiersArray 'abc'

Do that in your sp as well.

exorbitant
Starting Member

17 Posts

Posted - 2008-12-24 : 10:04:55

quote:
Originally posted by sakets_2000

quote:
Originally posted by exorbitant

quote:
Originally posted by sakets_2000

exec dbo.SplitUniqueidentifiersArray('<SomeRandomCategoryId>')

I've tried the following combinations:

1) exec dbo.SplitUniqueidentifiersArray('{E418C0F3-D729-423D-83BB-0C59FB47F376},{E418C0F3-D729-423D-83BB-0C59FB47F377}')

2) exec dbo.SplitUniqueidentifiersArray('{E418C0F3-D729-423D-83BB-0C59FB47F376}')

3) exec dbo.SplitUniqueidentifiersArray('abc')

4) exec dbo.SplitUniqueidentifiersArray('abc,def')

but everytime I get the same exception as:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '{E418C0F3-D729-423D-83BB-0C59FB47F376},{E418C0F3-D729-423D-83BB-0C59FB47F377}'.

remove the brackets and try.
exec dbo.SplitUniqueidentifiersArray 'abc'

Do that in your sp as well.

It throws an exception when i tried to execute it as:

exec dbo.SplitUniqueidentifiersArray 'abc'

the exception is as:

Server: Msg 2809, Level 18, State 1, Line 1
The request for procedure 'SplitUniqueidentifiersArray' failed because 'SplitUniqueidentifiersArray' is a function object.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-24 : 10:15:59

Your function is a table valued functiton.
Can you change the code in your sp where you are using the function to something like this and check,

INSERT INTO COMPANYDEALSIN
(COMPANYID ,DEALSINID) 
select @COMPANYID,* from dbo.SplitUniqueidentifiersArray(@CATEGORYID)

Or, just check if this works for you:


Begin tran
INSERT INTO COMPANYDEALSIN
(COMPANYID ,DEALSINID) 
select 'xyz',* from dbo.SplitUniqueidentifiersArray('xyz')
rollback

exorbitant
Starting Member

17 Posts

Posted - 2008-12-24 : 13:01:27

quote:
Originally posted by sakets_2000

Your function is a table valued functiton.
Can you change the code in your sp where you are using the function to something like this and check,

INSERT INTO COMPANYDEALSIN
(COMPANYID ,DEALSINID)
select @COMPANYID,* from dbo.SplitUniqueidentifiersArray(@CATEGORYID)

Or, just check if this works for you:

Begin tran
INSERT INTO COMPANYDEALSIN
(COMPANYID ,DEALSINID)
select 'xyz',* from dbo.SplitUniqueidentifiersArray('xyz')
rollback

wowwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww thats great. you even can't imagine what you've done for me. I was trying to figure out this problem for last 4 months and its just resolved now. Thanks a lotttttttttttttttttttttttt. thankssssssssss thanksssssssssss thanksssssssssss a lottttttttttttt reallyyyyyyyyyyyyyy..........

if u get chance to be in Pakistan ever in your life then surely you'll be my special guest...

the thing that worked for me is:

INSERT INTO COMPANYDEALSIN
(COMPANYID ,DEALSINID) 
select @COMPANYID,* from dbo.SplitUniqueidentifiersArray(@CATEGORYID)

thanks a lot again really...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-24 : 13:27:57

no problem.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources