jo stovall (6/25/2009)
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' ,',')
idxvalue
013.0
113.1
214.0
314.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 recognises 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?
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.
As a side bar, you'd probably get a lot more "hits" on your question if you asked it in the proper forum instead of a thread dedicated to the discusssion of an article.
--Jeff Moden
Change is inevitable... Change for the better is not.