Varchar(max) to sql_variant - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Varchar(max) to sql_variant

Author

Topic

fjudzon
Starting Member

19 Posts

Posted - 2009-12-15 : 09:26:51

Hi,
I have a table with a varchar(max) field and I need to insert it into another table with a sql_variant field.
it shows me an error. how could I do that without losing information?

thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 09:30:25

Post the code you used along with the error message

Madhivanan

Failing to plan is Planning to fail

fjudzon
Starting Member

19 Posts

Posted - 2009-12-15 : 09:38:13

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)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 09:41:41

Try

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

Madhivanan

Failing to plan is Planning to fail

fjudzon
Starting Member

19 Posts

Posted - 2009-12-15 : 09:44:55

"Explicit conversion from data type varchar(max) to sql_variant is not allowed."

fjudzon
Starting Member

19 Posts

Posted - 2009-12-15 : 10:34:18

any ideas??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 01:32:44

The only possible way is

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources