can insert into but can't update a text column

I can insert into but can't update a text column - see following example :
1) create a table with a text column
CREATE TABLE [dbo].[TestLongText] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[FileContent] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
2) insert text from another table into this table
insert into TestLongText(FileContent) select FileContent from
OtherTableWithTextCol where Id = 2
-- works fine
3) try to update this text column with identical text from the other table
update TestLongText set FileContent = (select FileContent from
OtherTableWithTextCol where Id = 2) where id = 1
-- returns this error: The text, ntext, and image data types are invalid in
this subquery or aggregate expression.
Try something like:
[code]
update TestLongText
set FileContent = o.FileContent
from TestLongText t,OtherTableWithTextCol o
where o.Id = 2 and t.Id =1
[/code]
Cristian Lefter, SQL Server MVP
"David Laub" <dlaub@.wheels.com> wrote in message
news:Oa%23NfYUNFHA.3560@.TK2MSFTNGP14.phx.gbl...
>I can insert into but can't update a text column - see following example :
> 1) create a table with a text column
> CREATE TABLE [dbo].[TestLongText] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [FileContent] [text] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> 2) insert text from another table into this table
> insert into TestLongText(FileContent) select FileContent from
> OtherTableWithTextCol where Id = 2
> -- works fine
> 3) try to update this text column with identical text from the other table
> update TestLongText set FileContent = (select FileContent from
> OtherTableWithTextCol where Id = 2) where id = 1
> -- returns this error: The text, ntext, and image data types are invalid
> in
> this subquery or aggregate expression.
>
|||Thanks!! That solved it - i forgot about doing an update based on a
join instead of a subquery!
*** Sent via Developersdex http://www.codecomments.com ***

Can I use dynamic SQL in a stored procedure to call another stored

Hi,
I am having one stored procedure named SP1 which is having one input and one
output parameter as follows
CREATE PROCEDURE dbo.SP1
@.xmldoc TEXT
,@.FLAG_EXEC VARCHAR(5)
AS
BEGIN
...
END
I have write another stored procedure named SP2 which is creating a dynamic
SQL statement like
CREATE PROCEDURE dbo.SP2
AS
BEGIN
DECLARE @.CURR_PROC_NAME VARCHAR(125)
DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
DECLARE @.STR_RET_QRY_MN VARCHAR(125)
DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
@.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE'' '
print @.STR_EXECUTE_QRY
EXEC (@.STR_EXECUTE_QRY)
END
but it always says to declare @.STR_RETURN_STRING variable. Can I do like
above? If yes then how? If no then why?
With thanx in advance,Hi Rajendra,
this does not work because each EXEC is running in it's own process.
That means in fact that proc2 does not know anything about the variables
you have declared in proc1.
You should handle it as follows:
Proc2 (Sub-Proc) writes the ReturnValue into a table
Proc2 (Calling Proc) reads after the run of Proc2 the value from the table
Another way is to work with "sp_executesql". See BOL for details...
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm|||Rajendra (Rajendra@.discussions.microsoft.com) writes:
> I have write another stored procedure named SP2 which is creating a
> dynamic SQL statement like
> CREATE PROCEDURE dbo.SP2
> AS
> BEGIN
> DECLARE @.CURR_PROC_NAME VARCHAR(125)
> DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
> DECLARE @.STR_RET_QRY_MN VARCHAR(125)
> DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
> SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
> @.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE''
'
> print @.STR_EXECUTE_QRY
> EXEC (@.STR_EXECUTE_QRY)
> END
> but it always says to declare @.STR_RETURN_STRING variable. Can I do like
> above? If yes then how? If no then why?
You are making it too complicated. Just say:
EXEC @.CURR_PROC_NAME @.STR_XML_AS_VARCHAR, STR_RET_QRY_MN OUTPUT,
@.FLAG_EXEC = 'FALSE'
This will execute the procedure of which the name is in @.CURR_PROC_NAME.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Can I use a UDF in an IF statement?

Is it possible to use a user defined function in an IF statement? The UDF returns a scalar value. Like:
IF dbo.DoSomething (@.x)

The only way I can see to call a UDF is with
SELECT @.y = dbo.DoSomething (@.x)

Thank you!

YES but you have to compare its result with something or your function should return bit or integer value IF dbo.DoSomething (@.x)=1