SP-Convert data type nvarchar to numeric 1

[WordTechinc]

I am a beginner and need help. I am getting error message

System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric. at System.Data.SqlClient.SqlCommand.ExecuteReader (Commandbehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)


I tried ISNULL(COLUME1,0)but still get same error.

SP code;

INSERT INTO gdtResultDT_MR
(
JobNo, [zip code], [prospective count], [responder count], [reduction], [percresponse],ProsDentCount,
RespDentCount,ProsNonDentCount,RespNonDentCount,ProsRNCount,RespRNCount,RNreduction,RNpercresponse,ProsNonRNCount,RespNonRNCount,NonRNreduction,NonRNpercresponse,
RNind,NonRNind,RNNonRNind,ProsMRCount,RespAllCount,RespHistoryCount,RespNonHistoryCount,MRind,prosORMRCount
)

SELECT @JobNo,
zip_code,
prospective_count,
responder_count,
isnull(reduction,0),
isnull(percresponse,0),
denproscount,
denrespcount,
nondenproscount,
nondenrespcount,
RN_prospective_count,
RN_responder_count,
RNreduction,
RNpercresponse,

NonRN_prospective_count,
NonRN_responder_count,
NonRNreduction,
NonRNpercresponse,

RNind,
NonRNind,
RNNonRNind,


isnull(MR_prospective_count,0),
All_responder_count,
History_Responder_count,
NonHistory_responder_count,
MRind,
ORMR_prospective_count


FROM OpenXML(@DocId,'Root/gdtResultDT',2)
WITH
(
zip_code char(10),
prospective_count numeric(18, 0),
responder_count numeric(18, 0),
reduction real,
percresponse real ,
denproscount numeric(18, 0),
denrespcount numeric(18, 0),
nondenproscount numeric(18, 0),
nondenrespcount numeric(18, 0),

RN_prospective_count numeric(18, 0),
RN_responder_count numeric(18, 0),
RNreduction real,
RNpercresponse real,

NonRN_prospective_count numeric(18, 0),
NonRN_responder_count numeric(18, 0),
NonRNreduction real,
NonRNpercresponse real,

RNind numeric(18,0),
NonRNind numeric(18,0),
RNNonRNind numeric(18,0),

MR_prospective_count numeric(18, 0),
ALL_responder_count numeric(18, 0),
History_responder_count numeric(18, 0),
NonHistory_responder_count numeric(18, 0),
MRind numeric(18, 0),
ORMR_prospective_count numeric(18, 0)

)myXML

 

[gmmastros]

It's hard to tell based on the information you posted, but I am willing to offer a guess, but please realize it's just a guess.

It appears as though you are digging data from an xml document and trying to store it in a table. It also appears as though you expect your data to be numeric (by the way, it's usually better to use int instead of numeric(18,0) because it uses less space and performs slightly better).

Anyway, with XML, if an element does not exist, you get NULL, which should be fine, but if you have an empty element that you attempt to convert to numeric, you will get this error.

Ex:[tt]
<root><data id=""/></root>[/tt]

I suggest you check your XML to see if this is happening anywhere.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WordTechinc]

Thank you. You saved my life. I spent 5 days to solve this problem. I passed empey element on XML. Thank you very much.

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom