?
Solved

working with text varibale in stored procedure

Posted on 2003-03-25
6
Medium Priority
?
212 Views
Last Modified: 2012-08-14
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
Comment
Question by:palloquin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:mnye
ID: 8203129
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
 

Author Comment

by:palloquin
ID: 8224526
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
 
LVL 7

Expert Comment

by:mnye
ID: 8224870
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:palloquin
ID: 8224941
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
 
LVL 7

Expert Comment

by:mnye
ID: 8224977
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
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
ID: 8524588
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question