sql error - 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

General SQL Server Forums

New to SQL Server Programming

sql error

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-05 : 03:28:09

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

rams

Ehan
Starting Member

19 Posts

Posted - 2011-10-05 : 04:04:41

Check your usage of SCOPE_IDENTITY()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 04:11:24

are you having any insert before? if not, SCOPE_IDENTITY() will be returning null.
Actual what value are you trying to populate for WaitingMessagesID?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources