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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Varchar(max) to sql_variant

Author  Topic 

fjudzon
Starting Member

19 Posts

Posted - 2009-12-15 : 11:11:09
I have a table declared as
DECLARE @table table (FieldId int, ProductId int, Value varchar(max), ReviewIndex int)

and when I try to do this:

insert into Stage.tb_Harvest_ProductData (SummaryFk, FieldFk, Value, CreatedBy)
select DISTINCT @ProductSummaryId, T1.FieldId, T1.Value, 'Stage.up_ParseHarvestXml'
from .....

It shows me the error
"Operand type clash: varchar(max) is incompatible with sql_variant"

the problem is that Stage.tb_Harvest_ProductData.Value is sql_variant and T1.Value is varchar (max)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 11:31:04
Does a cast not work?
cast(T1.Value as sql_variant)


Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-15 : 11:32:50
select DISTINCT @ProductSummaryId, T1.FieldId
,convert(varchar(max) ,T1.Value), 'Stage.up_ParseHarvestXml'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

fjudzon
Starting Member

19 Posts

Posted - 2009-12-15 : 12:26:45
if I use CONVERT:
"Operand type clash: varchar(max) is incompatible with sql_variant"

if I use CAST:
"Explicit conversion from data type varchar(max) to sql_variant is not allowed."
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-15 : 12:49:11
sql_variant can't hold varchar(max). You could try varchar(8000), but you might lose data.

select DISTINCT @ProductSummaryId, T1.FieldId
,convert(varchar(8000) ,T1.Value), 'Stage.up_ParseHarvestXml'

Jim



Everyday I learn something that somebody else already knew
Go to Top of Page

fjudzon
Starting Member

19 Posts

Posted - 2009-12-15 : 13:12:03
yes, I had tried that, but I didn't want to lose data...
So, I think I could not do that..
Go to Top of Page
   

- Advertisement -