August 5, 2009 at 9:02 am
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
August 5, 2009 at 9:34 am
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
Consultant - Straight Path Solutions
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
August 5, 2009 at 9:44 am
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???
August 5, 2009 at 9:54 am
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.
Jack Corbett
Consultant - Straight Path Solutions
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
August 5, 2009 at 9:59 am
I replace it with :
Insert into ManageStudent._Student
Select * From ManageStudent.StudentTemp
The problem is still not solved.
August 5, 2009 at 10:30 am
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.
Jack Corbett
Consultant - Straight Path Solutions
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
August 5, 2009 at 10:48 am
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 :
StudentIDLastNameFirstNameBirthdayBirthplaceSex ProvinceIDAddressTelephoneClassIDNationalityIDReligionIDSV_ImageGradeIDSystemIDStatusIDNote
1Thi?u QuangHuy3/26/1987 0:001Nam158 Nguy?n Thái Bình P12 Q.Tân Bình 711211a
2Tr?n Son Lam3/27/1987 0:001Nam159 Nguy?n Thái Bình P12 Q.Tân Bình 711211a
3Hoàng QuangNgh?3/28/1987 0:001Nam160 Nguy?n Thái Bình P12 Q.Tân Bình 711211a
4Nguy?n LâmHuy3/29/1987 0:001Nam161 Nguy?n Thái Bình P12 Q.Tân Bình 711211a
5Lê NhuQu?nh3/30/1987 0:001N?162 Nguy?n Thái Bình P12 Q.Tân Bình 711211a
6Kim Chi3/31/1987 0:001N?163 Nguy?n Thái Bình P12 Q.Tân Bình 711211a
August 10, 2009 at 12:51 pm
Could you actually attach an Excel file with the data?
Thanks.
Jack Corbett
Consultant - Straight Path Solutions
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
August 10, 2009 at 5:44 pm
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
August 11, 2009 at 9:19 am
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
***
December 12, 2011 at 1:57 pm
I'm having the same issue with the following query. Is there anything evident within it which woudl be causing:
"Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
USE [MobileFormsServer]
GO
/****** Object: View [dbo].[vwsc_BoatingWarningasXML] Script Date: 12/12/2011 13:15:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vwsc_BoatingWarningasXML]
AS
SELECT BoatingWarning.UniqueKey,
[OUTPUT] = CONVERT(VARCHAR(MAX),(
select
* from
( SELECT BoatingWarning.CitationNo AS [CitationNumber],
BoatingWarning.CitationDateTime AS [CitationDateAndTime],
--BoatingWarning.LoginName,
--BoatingWarning.UniqueUserID,
--BoatingWarning.Void,
BoatingWarning.Companion AS [Companion],
BoatingWarning.CompanionNumberType AS [CompanionNumberType],
BoatingWarning.CompanionNumber AS [CompanionNumber],
BoatingWarning.CompanionUniqueKey AS[CompanionUniqueKey],
BoatingWarning.CountyOf AS [CountyOf],
BoatingWarning.CountyOfNo AS [CountyofNumber],
BoatingWarning.CityOf AS [CityOf],
BoatingWarning.CityOfNo AS [CityofNumber],
BoatingWarning.OfficerAgency AS [OfficerAgency],
BoatingWarning.Location AS [Location],
BoatingWarning.Latitude AS [Latitude],
BoatingWarning.Longitude AS [Longitude],
BoatingWarning.MNINo AS [MNINumber],
BoatingWarning.NameFirst AS [FirstName],
BoatingWarning.NameMiddle AS [MiddleName],
BoatingWarning.NameLast AS [LastName],
BoatingWarning.NameSuffix AS [NameSuffix],
BoatingWarning.Street AS [StreetAddress],
BoatingWarning.AddressOther AS [OtherAddress],
BoatingWarning.AddDiffThanReg AS [AddressDifferentThanRegular],
BoatingWarning.City AS [CityAddress],
BoatingWarning.[State] AS [StateAddress],
BoatingWarning.ZipCode AS [AddressZipCode],
BoatingWarning.Phone AS [PhoneNumber],
BoatingWarning.DateOfBirth AS [DateOfBirth],
BoatingWarning.Race AS [Race],
BoatingWarning.Ethnicity AS [Ethnicity],
BoatingWarning.Sex AS [Sex],
BoatingWarning.Height AS [Height],
BoatingWarning.[Weight] AS [Weight],
BoatingWarning.Hair AS [HairColor],
BoatingWarning.Eyes AS [EyeColor],
BoatingWarning.BusinessName AS [BusinessName],
BoatingWarning.BusinessPhone AS [BusinessPhone],
BoatingWarning.IDNo AS [IDNumber],
BoatingWarning.IDState AS [StateOfID],
BoatingWarning.IDType AS [TypeOfID],
BoatingWarning.IDExpires AS [ExpirationDateofID],
BoatingWarning.VesselRegNo AS [VesselRegistrationNumber],
BoatingWarning.VesselRegState AS [VesselRegistrationState],
BoatingWarning.VesselRegExpires AS [VesselRegistrationExpirationDate],
BoatingWarning.VesselDocNo AS [VesselDocNumber],
BoatingWarning.VesselFuel AS [VesselFuel],
BoatingWarning.VesselPropulsion AS [VesselPropulsionType],
BoatingWarning.VesselHP AS [VesselHorsepower],
BoatingWarning.VesselYear AS [VesselMakeYear],
BoatingWarning.VesselMake AS [VesselMake],
BoatingWarning.VesselType AS [TypeofVessel],
BoatingWarning.VesselLength AS [LengthofVessel],
BoatingWarning.VesselLengthType AS [VesselLengthType],
BoatingWarning.VesselColor AS [ColorOfVessel],
BoatingWarning.VesselHIN AS [VesselHIN],
BoatingWarning.VehicleYear AS [YearOfVehicle],
BoatingWarning.VehicleMake AS [MakeOfVehicle],
BoatingWarning.VehicleModel AS [ModelOfVehicle],
BoatingWarning.VehicleTagNo AS [VehicleTagNumber],
BoatingWarning.VehicleTagNoState AS [StateOfVehicleTag],
BoatingWarning.VehicleTagExpires AS [ExpirationDateofVehicleTag],
BoatingWarning.VehicleVIN AS [VehicleVINNumber],
BoatingWarning.VehicleColor AS [ColorofVehicle],
BoatingWarning.OfficerNotes AS [NotesByOfficer],
BoatingWarning.OfficerOrgUnit AS [OfficerOrgUnit],
BoatingWarning.OfficerRank AS [RankOfOfficer],
BoatingWarning.OfficerName AS [NameOfOfficer],
BoatingWarning.OfficerIDNo AS [OfficerIDNumber],
--BoatingWarning.OfficerSignature,
BoatingWarning.UserCreatedDateTime AS [UserCreatedDateTime],
BoatingWarning.Printed AS [HasBeenPrinted],
BoatingWarning.PrintedDateTime AS [DateAndTimeOfPrint],
--BoatingWarning.UserCompleted,
--BoatingWarning.UserCompletedDateTime,
--BoatingWarning.UserTransmitted,
--BoatingWarning.UserTransmittedDateTime,
--BoatingWarning.SystemTransmitAck,
--BoatingWarning.SystemTransmitAckDateTime,
BoatingWarning.RuleNumber AS [RuleNumber],
BoatingWarning.FishSpecies AS [SpeciesOfFish],
BoatingWarning.FishComments AS [CommentsOnFish],
BoatingWarning.HuntSpecies AS [SpeciesHunted],
BoatingWarning.HuntComments AS [CommentsOnHunt],
BoatingWarning.QualSpecies AS [QualSpecies], --Need clarification on what this field represents,
BoatingWarning.QualComments AS [QualComments], --Ditto as above,
BoatingWarning.OtherViolation1 AS [OtherViolationOne],
BoatingWarning.OtherViolation2 AS [OtherViolationTwo],
BoatingWarning.CompanionNTNumberType AS [CompanionNTNumberType], --Ditto as above
BoatingWarning.ReportStatus AS [Status of Report],
-- BoatingWarning.ViolationUniqueKey,
BoatingWarning.Violation AS [Violation],
BoatingWarning.ViolationTypeCode AS [ViolationTypeCode],
BoatingWarning.ViolationType AS [TypeOfViolation],
BoatingWarning.ViolationLevelCode AS [LevelCodeForViolation],
BoatingWarning.ViolationLevel AS [LevelOfViolation],
BoatingWarning.ViolationLevelCourtAppearanceMandatory AS [ViolationLevelCourtAppearanceMandatory],
BoatingWarning.ViolationDescription AS [DescriptionOfViolation],
BoatingWarning.ViolationInstructions AS [ViolationInstructions],
BoatingWarning.ViolationCode AS [ViolationCode],
-- BoatingWarning.CodeViolationUniqueKey,
BoatingWarning.CodeViolation AS [CodeViolation], --Could alias this better with more info on what it represents
BoatingWarning.CodeViolationDescription AS [CodeViolationDescription],
BoatingWarning.CodeViolationInstructions AS [CodeViolationInstructions],
BoatingWarning.CodeViolationCounty AS [CodeViolationCounty],
BoatingWarning.DescriptionOfViolations AS [DescriptionOfViolations],
BoatingWarning.OfficerAgencyGroup AS [OfficerAgencyGroup],
BoatingWarning.WarningBoatCodesExist AS [WarningBoatCodesExist],
CASE BoatingWarning.WarningBoatCodesExist
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [BoatingWarningCodeExists],
ISNULL((select WarnBoatCodes.WarningCode from BoatingWarningBoatCodes WarnBoatCodes where WarnBoatCodes.BoatingWarningUniqueFKey = BoatingWarning.Uniquekey), '') AS
[BoatingWarningCode],
BoatingWarning.WarningFishCodesExist AS [WarningFishCodesExist],
CASE BoatingWarning.WarningFishCodesExist
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [FishingWarningCodeExists],
ISNULL((select WarnFishCodes.WarningCode from BoatingWarningFishCodes WarnFishCodes where WarnFishCodes.BoatingWarningUniqueFKey = BoatingWarning.Uniquekey), '') AS
[FishingWarningCode],
BoatingWarning.WarningHuntCodesExist AS [WarningHuntCodesExist],
CASE BoatingWarning.WarningHuntCodesExist
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [HuntingWarningCodeExists],
ISNULL((select WarnHuntCodes.WarningCode from BoatingWarningHuntCodes WarnHuntCodes where WarnHuntCodes.BoatingWarningUniqueFKey = BoatingWarning.Uniquekey), '') AS
[HuntWarningCode],
BoatingWarning.WarningQualityCodesExist AS [WarningQualityCodesExist],
CASE BoatingWarning.WarningQualityCodesExist
WHEN '1' THEN 'YES'
WHEN '0' THEN 'NO'
END AS [QualityWarningCodeExists],
ISNULL((select WarnQaulCodes.WarningCode from BoatingWarningQualityCodes WarnQaulCodes where WarnQaulCodes.BoatingWarningUniqueFKey = BoatingWarning.Uniquekey), '') AS
[QualityWarningCode]
FROM BoatingWarning BoatingWarning
) ResourceWarning
FOR XML AUTO, ELEMENTS, TYPE
) )
from boatingwarning
December 12, 2011 at 2:09 pm
Disregard. Found it:
FROM BoatingWarning BoatingWarning
December 12, 2011 at 2:15 pm
Re-Regard. It's still arguing the same point after removing the second half of FROM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply