June 5, 2006 at 3:32 am
The problem we are having is that the we are using sql 2000. Some data is not compatible.
We have User defined Functions in our SQL database. This feature is not present version 7 Do you have any suggestions on what to do ?
How we can we get user defined functions to work in version 7?
HELP
Regards
Ritesh
June 5, 2006 at 8:17 am
Sorry!!!,Ritesh
There is no such feature in Sql Sever 7.0 , but you can do one thing.You can load the Sql Server 7.0 data into Sql server 2000 with the help of DTS wizard. and then check the loaded data...
This is only the way... according to me.
AMIT GPTA
MCDBA.
June 5, 2006 at 8:22 am
I need to be able to go back a version as the server it will sit on is going to have sql 7.
What can I do with the user defeined functions. ?
Can you convert it to a store procedure or something?
Ritesh
June 5, 2006 at 8:28 am
Ritesh,
Pls send the procedure and function to me and arrange a copy of database backup (if you can).
E-mailId: mailgupta_amit@yahoo.com
Regards,
Amit Gupta
June 5, 2006 at 8:37 am
I have emailed the User defiend functions
June 5, 2006 at 9:06 am
Ritesh,
I want to ask a Question to you ?
Why you want to work in Sql Server 7. It does't allow you to execute a user defined function..
Regards,
Amit Gupta..
June 5, 2006 at 9:10 am
We are trying to move the database on a live server to another site. The server currenlty use version 7 and hold other DB on it. We need to use their server.
so we got no choice.
June 6, 2006 at 7:42 am
can you post the udfs here so we can see what they are trying to do?
in some cases, you might be able to replace them, but in other situations, you might have to tweak your application to not use the udfs.
Lowell
June 6, 2006 at 8:00 am
Below are the 3 UDFs
CREATE FUNCTION dbo.udfMonthName
( @monthnumber int,
@short bit = 0
)
RETURNS varchar(15)
AS
BEGIN
DECLARE @monthname varchar(15)
If @monthnumber > 12 Or @monthnumber < 1
Begin
SELECT @monthname = 'Unknown'
End
Else
Begin
If @short = 1
Begin
SELECT @monthname = Case @monthnumber
When 1 Then 'Jan'
When 2 Then 'Feb'
When 3 Then 'Mar'
When 4 Then 'Apr'
When 5 Then 'May'
When 6 Then 'Jun'
When 7 Then 'Jul'
When 8 Then 'Aug'
When 9 Then 'Sep'
When 10 Then 'Oct'
When 11 Then 'Nov'
When 12 Then 'Dec'
End
End
Else
Begin
SELECT @monthname = Case @monthnumber
When 1 Then 'January'
When 2 Then 'February'
When 3 Then 'March'
When 4 Then 'April'
When 5 Then 'May'
When 6 Then 'June'
When 7 Then 'July'
When 8 Then 'August'
When 9 Then 'September'
When 10 Then 'October'
When 11 Then 'November'
When 12 Then 'December'
End
End
End
RETURN (@monthname)
END
------------------------------------------------------------------------------
CREATE FUNCTION dbo.udfWordPosition (@str varchar(8000), @numwords int) RETURNS int
As
Begin
/*
Returns the position of the space after the xth word. i.e in the string 'The cat sat on the mat' WordPosition will
return 12 if given 3 as the number of words, being that the space after the third word (sat) is the 12th character
in the string.
*/
DECLARE
@wordcount int,
@spacesfound int,
@lastspace int,
@tmpstr varchar(8000)
SELECT @spacesfound = 0, @tmpstr = @STR, @lastspace = 1
Begin
SELECT @lastspace = 1
End
Else
Begin
--This is the number of words in the string
SELECT @wordcount = dbo.udfxWords(@str)
If @wordcount >= @numwords
Begin
While @spacesfound < @numwords
Begin
If CharIndex(' ', @STR, @lastspace) > 0
Begin
SELECT @lastspace = CharIndex(' ', @STR, @lastspace) + 1, @spacesfound = @spacesfound + 1
End
End
End
End
RETURN (@lastspace - 1)
End
------------------------------------------------------------------------------------------------------------
CREATE FUNCTION dbo.udfxWords (@str varchar(8000)) RETURNS int
As
Begin
DECLARE
@words int,
@tmpstr varchar(8000)
SELECT @words = 0
If @STR Is Null
Begin
SELECT @words = -1
End
Else
Begin
SELECT @tmpstr = @STR
While CharIndex(' ', @tmpstr) > 0
Begin
SELECT
@words = @words + 1,
@tmpstr = Case When Len(@tmpstr) - CharIndex(' ', @tmpstr) >= 0 Then
LTrim(SubString(@tmpstr, CharIndex(' ', @tmpstr), Len(@tmpstr) - CharIndex(' ', @tmpstr)))
Else
''
End
End
SELECT @words = @words + 1
End
RETURN (@words)
End
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply