User-defined function not recognized

Question

Post reply

User-defined function not recognized

jo stovall

Mr or Mrs. 500

Points: 509
More actions
June 25, 2009 at 1:56 pm

#155875

We are converting SS2000 DTS packages to SSIS 2005.
I need help using a User Defined Function within a query on a table.
The function performs correctly.
select * from fn_Split('13.0,13.1,14.0,14.1' ,',')
idx value
0 13.0
1 13.1
2 14.0
3 14.1
This is the table to run the function against, with a few sample record results.
Select versions from versiontable
13.0,13.1,14.0,14.1
13.0,13.1,14.0,14.1
13.0,13.1,14.0,14.1
13.0,13.1,14.0,14.1
11.0,11.1,12.0,12.1,13.0,13.1,14.0
I tried this. Now it no longer recognisee the function. Is the syntax wrong?
select fn_Split(versions, ',') as SingleVersion from versiontable where versions is not null
Server: Msg 195, Level 15, State 10, Line 1
'fn_Split' is not a recognized function name.
Tried this also.
select dbo.fn_Split(versions, ',') as Version from versiontable where versions is not null
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.fn_Split'.
Any suggestions to get the query to work or even better, a nifty method to accomplish this within my data flow task using one of the Data Flow Transformation. Seems like the pivot might be useful for this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Let's see if it actually made it from one server to another. What do you get when you run the following?

SELECT *

FROM sys.Objects

WHERE Name = 'fn_Split'

If the answer is nothing, then you're either in the wrong database or the CREATE FUNCTION code didn't actually work.

Ummm.... another problem may be the simple matter of privs. It would probably be easiest if you grant EXECUTE to the PUBLIC user on the function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jo stovall Mr or Mrs. 500 Points: 509 More actions · Answer 2

Thanks, Jeff.

When I ran your SQL statement, I got this:

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.Objects'.

Privileges, you think?

In my original post, I indicated that the function works when you pass it a literal string.

It only gags when you try to use the function with a table.

Not in the wrong db, but have done that before quite a bit.

Privileges may indeed be the issue.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

jo stovall (6/25/2009)
Thanks, Jeff.
When I ran your SQL statement, I got this:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.Objects'.
Privileges, you think?
In my original post, I indicated that the function works when you pass it a literal string.
It only gags when you try to use the function with a table.
Not in the wrong db, but have done that before quite a bit.
Privileges may indeed be the issue.

If you're running the query in SQLServer 2000, it would be dbo.SysObjects.

I just went back and took a look at your original post... a split function of this nature usually cannot be used against a table in the normal fashion. You would have to use a Cross-Apply. Of course, you can split the whole table wthout a function to begin with. Please see the following article... (and sorry for the posting delay)....

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)