?
Solved

Convert function error

Posted on 2004-10-19
8
Medium Priority
?
401 Views
Last Modified: 2008-02-01
Please help me to solve this error:

insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, @result, 'Strategy' from table1

Server: Msg 260, Level 16, State 1, Line 10
Disallowed implicit conversion from data type varchar to data type binary, table 'db.dbo.RMPII', column '_STAMP3'. Use the CONVERT function to run this query.

Any idea?
0
Comment
Question by:Vasi04
  • 3
  • 3
  • 2
8 Comments
 

Author Comment

by:Vasi04
ID: 12355456
'db.dbo.RMPII' = table2
0
 

Assisted Solution

by:a_tya
a_tya earned 800 total points
ID: 12355561
What datatype is "_STAMP3" field? if it is varchar try this:
insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, convert(varchar(100), @result), 'Strategy' from table1
0
 
LVL 1

Expert Comment

by:giriky99
ID: 12355578
Hi,

You can use the  your query like this..


case 1..If  _STAMP3 column in Table2 Datatype is varchar(50)

insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, convert(@result,varchar(50)), 'Strategy' from table1

case 1..If  _STAMP3 column in Table2 Datatype is int

insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, convert(@result,int), 'Strategy' from table1


case 1..If  _STAMP3 column in Table2 Datatype is Money
insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, convert(@result,Money), 'Strategy' from table1

Try the above statements....Also check the data in that @result  variable .


 



0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Expert Comment

by:giriky99
ID: 12355611
Hi,

You can use  your query like this..


case 1..If  _STAMP3 column in Table2 Datatype is varchar(50)

insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, convert(@result,varchar(50)), 'Strategy' from table1

case 2..If  _STAMP3 column in Table2 Datatype is int

insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, convert(@result,int), 'Strategy' from table1


case 3..If  _STAMP3 column in Table2 Datatype is Money
insert into table2
(_INTERNID, _STAMP2, _STAMP3, _TYPE)
select _INTERNID, _STAMP2, convert(@result,Money), 'Strategy' from table1

Try the above statements....Also check the data in that @result  variable .
0
 

Author Comment

by:Vasi04
ID: 12355618
Hi _stamp3 is binary. I used this way cast (@result as binary). but still get error. @result is some alphanumeric B5DK3YBZP

I am running this query in QA:
------------------------------------
declare @result varchar(100)
declare @hsecs numeric
declare @date datetime

set @hsecs = 1
set @date = getdate()
exec shortstamp @date, @hsecs, @result OUTPUT
select @result

insert into table2
(_INTERNID, _CHGDATE, _CHGTIME, _STAMP2, _STAMP3, _RMP_TYPE)
select _INTERNID, Convert(varchar(10),getdate(),101), Convert(varchar(10),getdate(),101), _STAMP2, cast(@result as binary), 'Strategy' from table1
-----------------------------------------

Server: Msg 8152, Level 16, State 11, Line 10
String or binary data would be truncated.
The statement has been terminated.
------------------------------------------------

0
 

Expert Comment

by:a_tya
ID: 12355649
please send the table structure for table1 and table2. I need to know what datatypes are you using..
0
 

Expert Comment

by:a_tya
ID: 12355661
my guess is that you are trying to insert a larger field in a smaller one, that's why the data is truncated.
Hope this helps u.
0
 
LVL 1

Accepted Solution

by:
giriky99 earned 1200 total points
ID: 12355853
Hi ,

You that @result having alphanumaric values and you are trying insert into binary column.

My suggetion to change the datatype of   _STAMP3 column to Varchar. and the your query.

or
put where condition to check for numaric as below.

insert into table2
(_INTERNID, _CHGDATE, _CHGTIME, _STAMP2, _STAMP3, _RMP_TYPE)
select _INTERNID, Convert(varchar(10),getdate(),101), Convert(varchar(10),getdate(),101), _STAMP2, cast(@result as binary), 'Strategy' from table1 where ISNUMERIC(@result )=1
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

616 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