ANOTHER SERIOUS PROBLEM

Question

ANOTHER SERIOUS PROBLEM

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
May 20, 2004 at 4:02 am

#108050

WHAT IAM DOING IS INSERTING VALUES IN 2 TABLES, 1 IS MASTER TABLE AND ANOTHER IS CHILD TABLE
MASTER TABLE IS GETTING UPDATED SUCESSFULLY BUT WHEN I TRY TO INSERT VALUES IN THE SECOND TABLE I GET THIS FOLLOWING ERROR MESSAGE
THAT IS WHEN I AM STORING A NUMERIC VALUES IN A VARIABLE AND
BUT AT THE TIME OF STORING THE SQL STRING WITH THE VARIABLE NUMERIC VALUES I GET THIS FOLLOWING ERROR MESSAGE I TRIED VARIOUS THINGS BUT ITS NOT GOING IAM USING SQL-2000, I HAD THIS PROBLEM IN 7.0 BUT I SOLVED IT NOW ITS AGAIN
THIS IS MY CODE SNIPPET------------
declare @insertSql1 nvarchar(4000)
declare @msgCode as numeric
set @insertSql=N'Insert into RMCPL_MAIL_HD(RMCPL_DT,RMCPL_SUB,rmcpl_usr) values('''+ @todydate + ''','''+@subject+''',27)'
select @insertSql
select @insertSql
exec sp_executesql @insertSql
SET @MSGCODE=@@identity
set @insertSql1=N'Insert into Rmcpl_mail_Tr(RMCPL_MSG_CD,RMCPL_TOCD,RMCPL_FRMCD,RMCPL_FLAG,RMCPL_SNT_TYPE,RMCPL_Type) values(' + @msgcode + ','''+@usrCode+''','''+@RMCPL_FLAG +''','''+@RMCPL_FLAG + ''',0)'
-----------THIS IS THE ERROR MESSAGE BELOW------------
Syntax error converting the nvarchar value 'Insert into Rmcpl_mail_Tr(RMCPL_MSG_CD,RMCPL_TOCD,RMCPL_FRMCD,RMCPL_FLAG,RMCPL_SNT_TYPE,RMCPL_Type) values(' to a column of data type int.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 1

Sukhoi,

Again, as in the post I answered for you previously, you need to do some conversions in the concatenization. You have to CONVERT the date to VARCHAR or CHAR. See "Books-On-Line" under "CONVERT" for all of the different format codes possible when converting DATETIME values to VARCHAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 2

thanks a Lot , for ur suggestion but better alternative will be to declare all the variables as varchar, it works d00d,

even while inserting u can use varchar, and it goes without any Errors. even if the datatype in the backend is numeric, just dont give quotation marks

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]