sql error

Question

Post reply

sql error

rams.prsk

Ten Centuries

Points: 1241
More actions
October 5, 2011 at 1:24 am

#258489

Hi All,
Iam getting the following error when running the following query :
DECLARE @Message XML
set @Message = '<?xml version="1.0" encoding="UTF-8"?>
<MessagesWaiting version="B000">
<ReturnMessage id="3698002190"/>
<ReturnMessage id="3698991460"/>
<ReturnMessage id="3699245994"/>
<ReturnMessage id="3699967832"/>
<ReturnMessage id="3700323664"/>
<ReturnMessage id="3700528934"/>
</MessagesWaiting>'
DECLARE @status INT;
DECLARE @CreatedAt DATETIME;
INSERT INTO [SW_ReturnMessages] (WaitingMessagesID,[Status], LastUpdatedAt, ReturnMessageID)
SELECT
SCOPE_IDENTITY(),
@status,
@CreatedAt,
[Message].rows.value('@id[1]', 'VARCHAR(50)') AS ReturnMessageID
FROM @Message.nodes('/MessagesWaiting/ReturnMessage') [Message](rows)
Msg 515, Level 16, State 2, Line 13
Cannot insert the value NULL into column 'WaitingMessagesID', table 'IDTDev.dbo.SW_ReturnMessages'; column does not allow nulls. INSERT fails.
where WaitingMessagesID is primary key

Viewing 6 posts - 1 through 5 (of 5 total)

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

mimran18 SSC-Addicted Points: 451 More actions · Answer 1

Make WaitingMessagesID column as an identity column and

change the query like.

INSERT INTO [SW_ReturnMessages] ([Status], LastUpdatedAt, ReturnMessageID)

SELECT

@status,

@CreatedAt,

[Message].rows.value('@id[1]', 'VARCHAR(50)') AS ReturnMessageID

FROM @Message.nodes('/MessagesWaiting/ReturnMessage') [Message](rows)

MI
http://raresql.com

rams.prsk Ten Centuries Points: 1241 More actions · Answer 2

rams.prsk

Ten Centuries

Points: 1241

October 5, 2011 at 2:04 am

#1390528

No Luck still

mimran18 SSC-Addicted Points: 451 More actions · Answer 3

Hi,

I made a sample here.

Drop table [SW_ReturnMessages]

GO

Create table [SW_ReturnMessages]

(WaitingMessagesID int identity(1,1),

[Status] nvarchar(50) ,

LastUpdatedAt datetime,

ReturnMessageID nvarchar(50))

GO

DECLARE @Message XML

set @Message = '<?xml version="1.0" encoding="UTF-8"?>

</MessagesWaiting>'

DECLARE @status INT;

DECLARE @CreatedAt DATETIME;

INSERT INTO [SW_ReturnMessages] ([Status], LastUpdatedAt, ReturnMessageID)

SELECT

'Updated',

getdate(),

[Message].rows.value('@id[1]', 'VARCHAR(50)') AS ReturnMessageID

FROM @Message.nodes('/MessagesWaiting/ReturnMessage') [Message](rows)

Select * from [SW_ReturnMessages]

MI
http://raresql.com

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 4

i would like to make some comments on this,

1) would u please like to post ur ddl for [SW_ReturnMessages] table.

2) you are inserting into [SW_ReturnMessages] table as waitingmessageID as scope_identity()

scope_identity() will give you the latest identity inserted in your present session which might be null and your waitingmessageID column is not null thats why it is giving you this error.

as i am changing your table schema it might work

try this

create table SW_ReturnMessages( WaitingMessagesID bigint identity(1,1),[Status] nvarchar(20), LastUpdatedAt datetime, ReturnMessageID bigint)

--select *from SW_ReturnMessages

DECLARE @Message XML

set @Message = '<?xml version="1.0" encoding="UTF-8"?>

<MessagesWaiting version="B000">

<ReturnMessage id="3698002190"/>

<ReturnMessage id="3698991460"/>

<ReturnMessage id="3699245994"/>

<ReturnMessage id="3699967832"/>

<ReturnMessage id="3700323664"/>

<ReturnMessage id="3700528934"/>

</MessagesWaiting>'

DECLARE @status nvarchar(20)='active';

DECLARE @CreatedAt DATETIME=getdate();

INSERT INTO [SW_ReturnMessages] ([Status], LastUpdatedAt, ReturnMessageID)

SELECT

@status,

@CreatedAt,

@Message.value('(/ReturnMessage/@id)[1]', 'bigint') AS ReturnMessageID

select *from [SW_ReturnMessages]

output of this :

WaitingMessagesIDStatusLastUpdatedAtReturnMessageID

1active2011-10-05 15:41:37.647NULL

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 5

sry need slight modification in the xml. value part of the code

you have to use this to get the value from @Message.

@Message.value('(MessagesWaiting/ReturnMessage/@id)[1]', 'nvarchar(40)') AS ReturnMessageID