Convert Text to varchar

Question

Convert Text to varchar

rhei78

SSC Journeyman

Points: 83
More actions
May 11, 2008 at 7:12 pm

#91822

Hi everyone,
We recently updated our server from SS2000 SP2 to SP4. All the other application works fine except for one which uses a UDF which accepts Text as parameter and returns varchar(4000). It as working perfectly before the update. Does anyone know what could be the reason?
thanks
Below is the code for the UDF
Create Function uf_makesafe(
@intext text)
}
returns varchar(4000)
AS
begin
declare @thistext varchar(4000)
Set @thistext = cast(@intext as varchar(4000))
Set @thistext = replace(@thistext,char(10),'')
return (@thistext)
end

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

heh... looks like homework... you forgot to remove the extra brace character from the example you copied out of Books Online. 😉

Also, you say this worked and now it doesn't. What doesn't work about it and what error message are you getting?

--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)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Another question would be... if you're converting TEXT to VARCHAR(4000), why don't you just pass VARCHAR(4000)? In fact, why waste time... do the conversion without a UDF...

--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)

rhei78 SSC Journeyman Points: 83 More actions · Answer 3

Thanks Jeff.

It was actually an existing project handed over to me for maintenance. Sorry for the typo there. Anyways, the error that I am getting is this:

ConnectionCheckforData (Checkfordata())

Server : Msg 11, level 16, State 1, Line 0

General Netowrk Error, Check your network documentation

Connection broken

And when I check the event viewer I get an

Error : 3624, Severity 20, State 1

My concern is the application was working perfectly before the update. But now it seems to crash on that specific statement which calls the UDF.

Thanks.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Error 3624 indicates that there is data corruption. Please check the ERRORLOG for any messages.

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

I know it sounds strange, but the first thing I'd try is dropping and recreating 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)