Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bobyliu
Starting Member
14 Posts |
 Posted - 2005-01-12 : 15:55:59
|
| Hi everybody,I am trying to use the updatetext function to copy the text field contents of a table stored in the table variable to a premenant table. However, I am getting some syntax error.Here is what i am doingUpdatetext Send.Data @DestPtrval 0 0 @Mytable.info_text @SrcPtrvalThe error is expected and it should help if I use an alias for @mytable. However, how do I give @Mytable an alias while using updatetext? Has anyone ever done this before? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-12 : 20:28:53
|
| Don't kill me if I'm wrong (meaning: check Books Online to be sure) but I'm positive you can't use any text functions on a table variable. Use a temp table instead. A text pointer has to refer to an actual position on a disk file, a table variable does not have this (more accurately, it CAN, but it cannot be addressed) |
 |
|
|
bobyliu
Starting Member
14 Posts |
Posted - 2005-01-12 : 22:41:52
|
| Actually that will make great sense. a text pointer is a pointer to the physical location. The thing is that I am dealing with a very busy and big table, so I am trying to stay away from temp tables. Looks like I will just have to use a premenant table... Anyone got a better idea? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-13 : 07:40:33
|
| Temps tables are fine, unless you'll have tons of people running this procedure at the same time (or other procedures that use temp tables) Don't assume they'll perform badly without testing, or that a regular table will perform any better. |
|
|
|
bobyliu
Starting Member
14 Posts |
Posted - 2005-01-13 : 12:18:59
|
| yeah... that's actualy exactly what's going on. It is a very busy server and we got lots of hot spots all over. A lot of procs were using the temp db, so I have try to kick everyone off it. What are you think of a premanant table? It just sounds so bothering that I have to manually delete the data in the table each time... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-13 : 20:38:34
|
| You'll end up with a hotspot regardless of where you put it, but it might take some heat off tempdb.If you've got extra hard disk(s) handy that are not being used for the database, you can use it to:-create a new filegroup for tempdb and place your temp table on it instead of primary, or:-do the same thing with a regular table in another database, or:-create a new DB specifically for high-traffic procedures such as this and put the table thereThis lets you get parallel I/O operations and moves your hot spots off the main drive. However, you won't gain much unless it's on a separate physical drive, not the same or even a separate partition on the same disk. |
|
|
|
|
|
|
|
|
use updatetext with the table variable