• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

working with text varibale in stored procedure

Hi,

Can anybody help me out here.

I have a table with a text type field. In a stored procedure I need to get the data from this field and pass it to a external com object (using sp_OASetProperty @object, 'propertyName', @myTextValue)

But Somehow it seemd I can't declare text type variables in a SP. The most I could do is use 8000 charecters long varchars, but this might not be enough...

Any thoughts?

ThX!!
0
palloquin
Asked:
palloquin
  • 3
  • 2
1 Solution
 
mnyeCommented:
varchar max is 8000 characters in sql.  if you need more use nText (1,073,741,823 characters) or Image (2,147,483,647 bytes).  You will not be able to search in each field until you enable text searching on that table. also this will hamper you database resonse time for any query pulled off that table.

hth
matt
0
 
palloquinAuthor Commented:
Hi,

Let me give you some more info and points:

I have a table with e-mail templates, I need to process some data and then create e-mail messages (based on a template) and store them in the email queue table.

The email template and the final message need to be able to be larger then 8000 characters.

I need to do some stuff to get the right info on the right place in the template (REPLACE statements).

BUT I CAN'T USE nText type variables in my stored procedures SO I can't get the data into a variable to modify it...

Any thoughts?
0
 
mnyeCommented:
I assume you are using nText to store the messages?  If so, use an common identifier to split the message up, do you analysis on the data and then concat the parts when you insert it into the table.

but if you have just one template do you need to store that in the database?  wouldnt that be more of a frontend task?  then you could just populate the frontend template with the variables (name, address, etc.)

hth

matt
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
palloquinAuthor Commented:
Hi,

I'm sorry but it wouldn't realy work the way you suggested, I do not have fixed points to split the message. It's a big blob..... Rightnow I'm rewriting the procedure into a DTS task... Not really nice but all I can think of right now...
0
 
mnyeCommented:
you can execute the DTS task from a stored procedure if you're just looking for automation.  thats probably your best bet anyway.  sorry i couldnt be of more help.

late
matt
0
 
MindphaserCommented:
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now