Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Data Corruption » Subquery returned more than 1 value. This is...

Subquery returned more than 1 value. This is not permitted when the subquery...

Rate Topic

Display Mode

Topic Options

Author

Message

thieuquanghuy

Posted Wednesday, August 05, 2009 9:02 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 3:22 AM
Points: 10, Visits: 21

I want to read an excel file. In that excel file, ID column is generated automatically in stored procedure. I insert them to a temp table, then I update the column ID. After that I insert it to main table. While querying , it shows the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." I don't know why and where it happen. Please help me! It's so important to me. Thanks in advance!
Stored procedure code is below:

PROCEDURE [ManageStudent].[sp_insertStudentFormExcelFile]
-- Add the parameters for the stored procedure here
@fileurl nvarchar(400),
@prefixStudentID varchar(4),
@classid decimal(3,0)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @SQLString nvarchar(400),
@rowcount int,
@counter int,
@studentnumber int,
@studentid varchar(7)
Select @studentnumber = (SELECT count(*) from _Student where StudentID like @prefixStudentID+'___' )
-- Insert statements for procedure here
SET @SQLString = 'SELECT StudentID, LastName, FirstName, Birthday, Birthplace, Sex, ProvinceID, Address, Telephone, ClassID, NationalityID, ReligionID, SV_Image, GradeID, SystemID, StatusID, Note
FROM
OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @fileurl+';
Extended Properties=''''Excel 12.0'''''')...[Sheet1$]'

INSERT INTO ManageStudent.StudentTemp EXEC(@SQLString)
select @rowcount = (select count(*) from ManageStudent.StudentTemp )
set @counter = 1

while @counter <= @rowcount
begin
set @studentnumber = @studentnumber+1
set @studentid = @prefixStudentID +'000'+@studentnumber
if @studentid < 1000000
set @studentid = '0' + @studentid
update ManageStudent.StudentTemp set StudentID = @studentid, ClassID = @classid where StudentID = Convert(varchar(7),@counter)
set @counter = @counter+1

end
insert into ManageStudent._Student exec('Select * from ManageStudent.StudentTemp')
delete from ManageStudent.StudentTemp

END

Post #765563

Jack Corbett

Posted Wednesday, August 05, 2009 9:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 7,471, Visits: 7,030

I don't see anywhere in that stored procedure where you'd get more than 1 value from a subquery. The only thing I can suggest to troubleshoot is to run just portions of the code until you find the section that causes the error.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #765599

thieuquanghuy

Posted Wednesday, August 05, 2009 9:44 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 3:22 AM
Points: 10, Visits: 21

insert into ManageStudent._Student exec('Select * from ManageStudent.StudentTemp')

Text above create the error. I see it quite simple, so I don't know why it has error???

Post #765616

Jack Corbett

Posted Wednesday, August 05, 2009 9:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 7,471, Visits: 7,030

I don't see why that would throw the error you are getting, but I was going to comment that using the Dynamic SQL, Exec(Sql string) is unnecessary. Try removing that.

Post #765626

thieuquanghuy

Posted Wednesday, August 05, 2009 9:59 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 3:22 AM
Points: 10, Visits: 21

I replace it with :

Insert into ManageStudent._Student
Select * From ManageStudent.StudentTemp

The problem is still not solved.

Post #765630

Jack Corbett

Posted Wednesday, August 05, 2009 10:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 7,471, Visits: 7,030

I really don't think this is where you are getting the error. Can you post the table definitions, some sample data, and attach a sample excel sheet?

Can't fix it if I can't test it.

Post #765654

thieuquanghuy

Posted Wednesday, August 05, 2009 10:48 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, August 12, 2009 3:22 AM
Points: 10, Visits: 21

sure, I post it to you.
USE [SV]
GO
/****** Object: Table [ManageStudent].[_Student] Script Date: 06/17/2009 11:38:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ManageStudent].[_Student](
[StudentID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Birthday] [datetime] NOT NULL,
[Birthplace] [decimal](3, 0) NULL,
[Sex] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProvinceID] [decimal](3, 0) NULL,
[Address] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Telephone] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClassID] [decimal](3, 0) NULL,
[NationalityID] [decimal](2, 0) NULL,
[ReligionID] [decimal](2, 0) NULL,
[SV_Image] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GradeID] [decimal](2, 0) NULL,
[SystemID] [decimal](1, 0) NULL,
[StatusID] [decimal](2, 0) NULL,
[Note] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK__Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Excel file :

StudentID LastName FirstName Birthday Birthplace Sex ProvinceID Address Telephone ClassID NationalityID ReligionID SV_Image GradeID SystemID StatusID Note
1 Thiều Quang Huy 3/26/1987 0:00 1 Nam 1 58 Nguyễn Thái Bình P12 Q.Tân Bình                7 1 1 2 1 1 a
2 Trần Sơn Lam 3/27/1987 0:00 1 Nam 1 59 Nguyễn Thái Bình P12 Q.Tân Bình                7 1 1 2 1 1 a
3 Hoàng Quang Nghị 3/28/1987 0:00 1 Nam 1 60 Nguyễn Thái Bình P12 Q.Tân Bình                7 1 1 2 1 1 a
4 Nguyễn Lâm Huy 3/29/1987 0:00 1 Nam 1 61 Nguyễn Thái Bình P12 Q.Tân Bình                7 1 1 2 1 1 a
5 Lê Như Quỳnh 3/30/1987 0:00 1 Nữ 1 62 Nguyễn Thái Bình P12 Q.Tân Bình                7 1 1 2 1 1 a
6 Kim Chi 3/31/1987 0:00 1 Nữ 1 63 Nguyễn Thái Bình P12 Q.Tân Bình                7 1 1 2 1 1 a

Post #765673

Jack Corbett

Posted Monday, August 10, 2009 12:51 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 7,471, Visits: 7,030

Could you actually attach an Excel file with the data?

Thanks.

Post #768100

Victor Alvarez-1138365

Posted Monday, August 10, 2009 5:44 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 21, 2009 1:18 PM
Points: 20, Visits: 50

Hello ,

The stored procedure use 2 table . StudentTemp and _Student, would be possible to attach the definition for the both tables ?,

with regards,

Victor Alvarez
http://sqlpost.blogspot.com

Post #768283

Steve-70615

Posted Tuesday, August 11, 2009 9:19 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 576, Visits: 420

Can you post the table definition of the temp table?

ManageStudent.StudentTemp

Additionally what does this SELECT statement return when run before the INSERT?
'Select * from ManageStudent.StudentTemp' before the insert.

I know you posted the DML, but that doesn't always script the triggers if not selected in Management Studio. Is there a trigger on the table?

And to clarify, the INSERT that's failing is the one at the bottom of your code snipet?

***
insert into ManageStudent._Student exec('Select * from ManageStudent.StudentTemp')
delete from ManageStudent.StudentTemp
***

Post #768710

« Prev Topic | Next Topic »

Permissions