What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS SQL Server - issue with inserting data into table with trigger on it..
Posted on 2011-03-22
I get this error:
Msg 512, Level 16, State 1, Procedure TRIGGER_GRP_Msg_Queue_Reci
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Msg 512, Level 16, State 1, Procedure TRIGGER_GRP_Msg_Queue_Reci
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
2
9 Comments
Active 2 days ago
Most likely you are setting the value of a field to the result of a query, and that query is returning more than one value. For example:
update tbl
set X = (select Y from Z)
If there is more than one row returned from the sub-query, the update will fail.
update tbl
set X = (select Y from Z)
If there is more than one row returned from the sub-query, the update will fail.
okay here is the code section from trigger...
/*****************
---------
-- REPORT --
--------
******************/
--
-- if the msg is nothing text back to recepient and say...try
again--------------
if @campaign_message = ''
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'Oops, we did not understand ur reply. Probably due
2 spelling error
or u took 2 long to reply. Pls check spelling and resend to' +
@sms_server_number + '. U can reply STOP 2 end.')
END
----------------------------------------------------------------------
---------------
-- if recp...STOP...then STOP...
if @campaign_message = 'STOP'
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'You have been opted out of this campaign. Thank you for
your time.')
Update grp_campaign set stop = 'STOP' where to_number = @to_number and
sms_server_num = @sms_server_number
END
-- if recp...send message..repond appropriatelly...
if @campaign_message <> ''
BEGIN
-- 1. get the old message into a var
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
-- 2. get convo id...
declare @convo_id nvarchar(max)
set @convo_id = (SELECT convo_id from grp_campaign WHERE to_number =
@to_number)
-- 3. then tack on the newest response + NEW LINE SPACE!!!
declare @grp_tc_id nvarchar(max)
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
declare @new_part nvarchar(max)
set @new_part = @campaign_message
--set @new_part = @old_grp_msg + '%0a' + UPPER(@convo_id) + ' :' +
--'%0a' + @new_part + '%0a'
set @new_part = @old_grp_msg + ' <<< ' + UPPER(@convo_id) + ' >>> ' + ' :' +
' ' + @new_part + ' '
-- 4. get the last 500 chars into a new var update grp_campaign set
rolling_convo_msg = RIGHT(@new_part,250) where tc_id = @grp_tc_id
-- 5. send update msg to all participats insert into grp_message_queue
(to_number,rolling_convo_msg,sms_server_num)
SELECT to_number, rolling_convo_msg,sms_server_num from grp_campaign
where tc_id = @grp_tc_id
END
Active 2 days ago
Here is an example of a query that might cause a problem IF it returns more than one row (which is data dependent, so I can't tell by looking at it)
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
You can try adding DISTINCT, which will only fix the problem if the multiple-row issue is due to identical rows:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT DISTINCT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
or you can try adding TOP 1, which will cause the query to work, but may not return the value you expect, if there is more than one possible value:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT TOP 1 rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
This needs to be done for all queries like this in your trigger where a value from a query is being assigned to a variable.
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
You can try adding DISTINCT, which will only fix the problem if the multiple-row issue is due to identical rows:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT DISTINCT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
or you can try adding TOP 1, which will cause the query to work, but may not return the value you expect, if there is more than one possible value:
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT TOP 1 rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
This needs to be done for all queries like this in your trigger where a value from a query is being assigned to a variable.
copy/paste...messed it up a bit..
/*****************
---------
-- REPORT --
--------
******************/
--
-- if the msg is nothing text back to recepient and say...try
again--------------
if @campaign_message = ''
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'Oops, we did not understand ur reply. Probably due
2 spelling error
or u took 2 long to reply. Pls check spelling and resend to' +
@sms_server_number + '. U can reply STOP 2 end.')
END
----------------------------------------------------------------------
---------------
-- if recp...STOP...then STOP...
if @campaign_message = 'STOP'
BEGIN
INSERT INTO Message_Queue (TO_Number, Campaign_Message) Values
(@to_number,'You have been opted out of this campaign. Thank you for
your time.')
Update grp_campaign set stop = 'STOP' where to_number = @to_number and
sms_server_num = @sms_server_number
END
-- if recp...send message..repond appropriatelly...
if @campaign_message <> ''
BEGIN
-- 1. get the old message into a var
declare @old_grp_msg nvarchar(max)
set @old_grp_msg = (SELECT rolling_convo_msg from grp_campaign where
to_number = @to_number and sms_server_num = @sms_server_number)
-- 2. get convo id...
declare @convo_id nvarchar(max)
set @convo_id = (SELECT convo_id from grp_campaign WHERE to_number =
@to_number)
-- 3. then tack on the newest response + NEW LINE SPACE!!!
declare @grp_tc_id nvarchar(max)
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
declare @new_part nvarchar(max)
set @new_part = @campaign_message
--set @new_part = @old_grp_msg + '%0a' + UPPER(@convo_id) + ' :' +
--'%0a' + @new_part + '%0a'
set @new_part = @old_grp_msg + ' <<< ' + UPPER(@convo_id) + ' >>> ' + ' :' +
' ' + @new_part + ' '
-- 4. get the last 500 chars into a new var
update grp_campaign set
rolling_convo_msg = RIGHT(@new_part,250) where tc_id = @grp_tc_id
-- 5. send update msg to all participats
insert into grp_message_queue (to_number,rolling_convo_msg,sms_server_num)
SELECT to_number, rolling_convo_msg,sms_server_num from grp_campaign
where tc_id = @grp_tc_id
END
the issue has been explained above, aka queries like this:
we cannot solve/check that, you have to go through the data + code to isolate the issue.
and then solve it by not using such non-set programming code, but SQL (T-SQL) code
set @grp_tc_id = (SELECT TC_ID from grp_campaign where to_number =
@to_number and sms_server_num = @sms_server_number)
we cannot solve/check that, you have to go through the data + code to isolate the issue.
and then solve it by not using such non-set programming code, but SQL (T-SQL) code
update c
set rolling_convo_msg = RIGHT(@new_part,250)
from grp_campaign c
where to_number = @to_number
and sms_server_num = @sms_server_number
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 11 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.