Text Variables

Question

Text Variables

Keith Lunevich-369414

Right there with Babe

Points: 727
More actions
January 17, 2007 at 8:39 am

#115814

Hello,

I am trying to move some working ASP/SQL code into a SQL Job using SQL 2000. The problem that I am having is with trying to access a text variable. Simply put, what I need to do within SQL is to query a database table (one of the fields is a text data type) and then to assign these results to variables which are then used as parameters while calling a stored procedure.

Within SQL you can not declare a text variable (Declare @MyText text as it gives this error: "The text, ntext, and image data types are invalid for local variables."

Now I could make this a varchar(8000) variable but this is not large enough to hold all of the information and will be truncated.

Does anybody know of a way to make this work?

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 1

What is in the text? I've worked around it in the past by processing the text data in 8000 byte chunks. If you post a little more info, maybe we can come up with a workaround.

SQL guy and Houston Magician

Keith Lunevich-369414 Right there with Babe Points: 727 More actions · Answer 2

I am using this for an email sending program in which users subscribe to certain groups and they receive an email when new information is sent out.

I am pulling information from an email queue table which contains information such as to, from, subject, content, etc.

I then pass this information to a stored procedure which sends out the email.

Most of the content of the various emails that get sent out are under 8000 characters but there are a few that are 3 times that size. I need to account for all possibilities and thus need to use the text data type.

Any thoughts?

Thanks

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 3

Hmm.

SQL Server isn't really the ideal place to be sending emails from. Are you using SQLMail? CDO?

SQL guy and Houston Magician

Keith Lunevich-369414 Right there with Babe Points: 727 More actions · Answer 4

I am using the existing sp_send_cdosysmail stored procedure with a few modifications for other email settings.

It sends these emails through a specified SMTP server.

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 5

Well. One option would be to build the call to your mail proc dynamically:

DECLARE @Body1 NVARCHAR(8000),
        @Body2 NVARCHAR(8000),
        @Body3 NVARCHAR(8000),
        @Body4 NVARCHAR(8000),
        @Body5 NVARCHAR(8000),
        @Body6 NVARCHAR(8000)
        
SELECT @body1 = ISNULL('first 8000 bytes ',''),
@body2 = ISNULL('second 8000 bytes ',''),
@body3 = ISNULL('third 8000 bytes',''),
@body4 = ISNULL('fourth 8000 bytes',''),
@body5 = ISNULL(NULL,''), --None of the values can be null
@body6 = ISNULL(NULL,'') --None of the values can be null

EXEC('dbo.myProc @FromAddress = ''from@blah.com'', @toaddress = ''to@blah.com'' @Body = ''' + @Body1 + @body2 + @body3 + @body4 + @body5 + @body6 + '''')

This is just an example, Use substring to pull out the blocks of text from the text field.

SQL guy and Houston Magician

Keith Lunevich-369414 Right there with Babe Points: 727 More actions · Answer 6

Hi Robert,

Thanks for the suggestion.

I was kinda thinking that is the route that I will have to take but I was hoping for a simpler/cleaner solution. I was also thinking of using a query of function (to get the text field's content) and placing this directly in the dynamic stored procedure call.

I was holding off from doing this to see if other possibilities arose from the forum.

Thanks for your help.

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 7

No Problems. There are not a lot of options for this in 2000. My honest recommendation would be to write a simple app that periodically polls the queue and sends the emails itself. You could probably do it in 30 minutes.

This will scale a lot better, you would have much more ability to log and debug, and you can let the database worry about the data, and your application worry about application logic.

SQL guy and Houston Magician