Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Subquery returned more than 1 value. This is not permitted Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 4:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:53 AM
Points: 281, Visits: 562
I am getting the above error when running the below code.

This has started happening on this bit of sql after a backup and restore

anyone any ideas?

SET IDENTITY_INSERT Seminars ON

INSERT INTO xTable(xID, yID, zID, Title, Hours, MaxPeople, StatusID, IH, PFID)
VALUES
(-1, -1, 71, 'one', 0, 0, 1, 0, 0),
(-2, -2, 71, 'two', 0, 0, 1, 0, 0),
(- 3, - 3, 71, 'three', 0, 0, 1, 0, 0),
(- 4, - 4, 71, 'four', 0, 0, 1, 0, 0),
(- 5, - 5, 71, 'five', 0, 0, 1, 0, 0),
(- 6, - 6, 71, 'six', 0, 0, 1, 0, 0),
(- 7, - 7, 71, 'seven', 0, 0, 1, 0, 0),
(- 8, - 8, 71, 'eight', 0, 0, 1, 0, 0),
(- 9, - 9, 71, 'nine', 0, 0, 1, 0, 0),
(- 10, - 10, 71, 'ten', 0, 0, 1, 0, 0)

SET IDENTITY_INSERT Seminars OFF

thanks in advance
Post #1337144
Posted Monday, July 30, 2012 4:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
There's no subquery there. Have you got any triggers on the table?

The normal cause of this error is a subquery returning > 1 value, which is of course invalid as the return value is going into 1 row.
Post #1337151
Posted Monday, July 30, 2012 4:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:53 AM
Points: 281, Visits: 562
ahhhhhhhhhhh

someone has been medalling

thanks for the reply
Post #1337154
Posted Monday, July 30, 2012 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
erics44 (7/30/2012)
ahhhhhhhhhhh

someone has been medalling

thanks for the reply


If it was a trigger then you seriously need to fix that trigger to handle multiple row statements. Don't just work around the issue by running a whole bunch of single row inserts.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1337251
Posted Monday, July 30, 2012 8:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:53 AM
Points: 281, Visits: 562
nope trigger fixed
Post #1337293
Posted Monday, July 30, 2012 8:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
erics44 (7/30/2012)
nope trigger fixed


Sweet!!


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1337298
Posted Monday, July 30, 2012 9:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 42,812, Visits: 35,931
erics44 (7/30/2012)
nope trigger fixed


And trigger developer educated?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1337353
Posted Monday, July 30, 2012 9:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:53 AM
Points: 281, Visits: 562
GilaMonster (7/30/2012)
erics44 (7/30/2012)
nope trigger fixed


And trigger developer educated?


ha, i wish

i wouldnt know where to start to pick appart the existing code in the sql databases

the most annoying thing is the amount of union queries there are, splitting tables and putting them back together and people are continuing to develop like that
Post #1337356
Posted Tuesday, July 31, 2012 6:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 4,348, Visits: 6,157
erics44 (7/30/2012)
GilaMonster (7/30/2012)
erics44 (7/30/2012)
nope trigger fixed


And trigger developer educated?


ha, i wish

i wouldnt know where to start to pick appart the existing code in the sql databases

the most annoying thing is the amount of union queries there are, splitting tables and putting them back together and people are continuing to develop like that


Please take my business card!! I would LOVE to work in your environment for a week or two!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1337819
Posted Tuesday, July 31, 2012 6:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:53 AM
Points: 281, Visits: 562
the guy who wrote it is really protective of his code too

if i change anything he has a paddy

i have found myself approaching things in a rubbish way just to stay common with the existing methods

i wrote a couple of table functions and he had a paddy because they weren't stored proceedure (full of unions )

if anyone has any ideas how to deal with someone like that then please let me know
Post #1337848
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse