SQL Server 2008 :: Receiving Subquery Returned More Than 1 Value Error?

Aug 27, 2015

I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.

I first created up a User-Defined Table Type

My stored proc is as follows:

ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl]
@MtlTblMaterialType READONLY
AS
BEGIN
Set NOCOUNT ON;

[code]....

View 9 Replies


ADVERTISEMENT

SQL Error : 'Subquery Returned More Than 1 Value'

Sep 9, 2004

Hi i am a newbie in sql so any help would be appreciated. i have a serverlog table that initially had one server and different functions types, my sql query got a percentage of the execution times for the different functions and all worked well, however as soon as i put more servers in to the table i got the following error...

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

Here is my SQL, the problem is with the sub queries returning more than one server type... Hope somebody can help and thanks in advance..


SELECT server, [function], prelimquery * 100 AS [%OverallProcessingTime]
FROM (SELECT server, [function], subquerya /
(SELECT subqueryb
FROM (SELECT totalallreqexec *
(SELECT AVG([exec_time])
FROM llserverlogs) AS subqueryb
FROM (SELECT COUNT(*) AS totalallreqexec
FROM llserverlogs) DERIVEDTBL) DERIVEDTBL) AS prelimquery
FROM (SELECT server, [function], NoRequestExecutions *
(SELECT avgexectime
FROM (SELECT DISTINCT server, AVG(exec_time) AS avgexectime
FROM llserverlogs
GROUP BY server) DERIVEDTBL) AS subquerya
FROM (SELECT DISTINCT server, [function], COUNT(*) AS NoRequestExecutions
FROM llserverlogs
GROUP BY server, [function]) DERIVEDTBL) DERIVEDTBL) DERIVEDTBL

View 2 Replies View Related

Subquery Returned More Than 1 Value Error

Jun 5, 2006

Hi group
I get the above error in my SP when the tblHolidayDates has more than one record. Here is my SP:
begin

if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))= 3 AND
DATEPART(dw,(select holidaydate from tblHolidayDates)) = 1 -- Monday Date
SELECT su.SuspensionID, su.ConsentNo, si.SiteID,
si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc,
si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached,
rd.RefValue AS NoticeDays, si.MergeID
FROM Suspension su
INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN RefData rd ON si.NotReq = rd.RefCode
WHERE su.Status = 'ACT'
and si.Archived = 'N'
and (datediff(day, getdate()-10 , si.SuspensionStart) = rd.RefValue
OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2)
or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1)
end

Any pointers much appreciated.

View 11 Replies View Related

Error: 'SubQuery Returned More Than 1 Value'

Jun 26, 2007

I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1. The code normally works however, every once in a while an exception is thrown:




Code Snippet

com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.

Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?

Stored Procedure:




Code Snippet

ALTER Procedure [dbo].[addRecord]
@userID int,
@itemID int,
@info varchar(50),
@comment varchar(50),
@output int output
AS


Declare @dateSubmitted datetime
set @dateSubmitted = getDate();

--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@userID, @info, @dateSubmitted);

---Get the ID Assigned in the record table. The Item table
---has a Foreign key on this column.
Declare @assignedID int
set @assignedID = (select ID from RecordTable where DateSubmitted = @dateSubmitted)

/**Set the output parameter.*/
set @output = @assignedID;

--Now update the Item Table.
insert into Item
(ID, RecordID, Comment)
values (@item,@assignedID, @comment);

View 8 Replies View Related

Subquery Returned More Than 1 Value. Error On Update SQL

Sep 27, 2004

Hi guys,

I am just trying to do a simple update statement and am getting this error

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.

My statment is like this:

Code:


update [Clients]
SET [OPT-OUT] = 0 WHERE [OPT-OUT] is null


I have used this against another table with success, but get the error against that one. Anyone have any idea?

View 4 Replies View Related

SqlBulkCopy And Triggers Cause Subquery Returned More Than 1 Value Error

Feb 22, 2008

Hi,
I am using .NET sqlBulkCopy to insert several rows at once into my sql table.  Yesterday I created a simple trigger on this table to take the primary key of the inserted row and place into another table.  Fairly straightforward I thought, but I keep getting this error when inserting 2 or more rows (works fine with one row):
"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."
To test this, I changed the sqlBulkCopy into individual INSERT statements in a for each loop and then it works perfectly, so I know that it's definitely the sqlBulkCopy causing the problem, not the trigger itself.  Here is my trigger code anyway:
alter trigger tg_InsertClaim on AuditItemfor insertas
declare @AuditItemID as intset @AuditItemID = (select i.AuditItemID from inserted i inner join dbo.AuditItem a ON i.AuditItemID = a.AuditItemID)
insert into Claim (AuditItemID) VALUES (@AuditItemID)
Thanks!

View 2 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues]    @Uid intASBEGIN    SET NOCOUNT ON;        select                                  DATEPART(year, c.fy)as fy,                                                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1%      JRF' ) as survivorship,                (select contribeamount from wh_contribute where and contribename like  'Gross Earnings' and ) as ytdgross,                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5%      JRP') as totalcontrib,                                                       from    wh_contribute c                       where    c.uid=@Uid                 Order by fy Asc .....what is the wrong here??  " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap... 

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Subquery Returned More Than 1 Value But Work FINE (field_xxx=subquery)

Jul 19, 2007

Hi guys,



A have a problem that I need understand.... I have 2 server with the same configuration...



SERVER01:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics

********************************

SERVER02:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics



OK, the problem is that if a run the below query in server01, i get error 512:



Msg 512, Level 16, State 1, Line 1

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



But, if run the same query in the server02, the query work fine -.



I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.



Any idea WHY?



SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,

dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,

'SD' ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,

0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,

0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,

0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,

0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,

0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,

0 vl_naoexec_premio,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf

FROM dbo.opf_saldo_ctb_opc_flx,

dbo.opf_header_operacao ,

dbo.opf_movimento_operacao

WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )

and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o

where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )

and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')

group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao,

dbo.opf_header_operacao.ds_tipo_transacao ,

ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente



Thanks

Nilton Pinheiro

View 9 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

Mar 6, 2008

I am getting an error as

Msg 512, Level 16, State 1, Line 1

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

while running the following query.





SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,

LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,

LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,

LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,

ManagerName=(SELECT E.FirstName+' '+E.LastName

FROM EmployeeDetails E

INNER JOIN LUP_EmpProject

ON E.Empid=LUP_EmpProject.Empid

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid

WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)



FROM EmployeeDetails

INNER JOIN LUP_EmpDepartment

ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid

INNER JOIN LUP_FIX_DeptDetails

ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid

AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date

FROM LUP_EmpDepartment

WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid

ORDER BY LUP_EmpDepartment.Date DESC)

INNER JOIN LUP_EmpDesignation

ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid

INNER JOIN LUP_FIX_DesigDetails

ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid

AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date

FROM LUP_EmpDesignation

WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid

ORDER BY LUP_EmpDesignation.Date DESC)

INNER JOIN LUP_EmpProject

ON EmployeeDetails.Empid=LUP_EmpProject.Empid

AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate

FROM LUP_EmpProject

WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid

ORDER BY LUP_EmpProject.StartDate DESC)

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid



WHERE EmployeeDetails.Empid=1

PLEASE HELP.................

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

May 14, 2008

Hi,

I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Any ideas as to why??

SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')

Thanks

Jacquues

View 4 Replies View Related

Receiving &<Unsupported Data Type&> From Subquery

Apr 7, 2007

I have a table that records sales leads and I need to calculate the total number of leads per user and the total amount owed, then then show only the users who have recieved 3 leads. The UserID is an integer.

Here is what i have:

SELECT * FROM
(
SELECT DISTINCT UserID,
(SELECT COUNT(*)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalLeads,
(SELECT SUM(Price)
FROM LeadMatches
WHERE (UserID = L.UserID)) AS TotalAmount
FROM LeadMatches AS L
)
WHERE TotalLeads = 3

This should work but it produced the following result:

UserID | TotalLeads | TotalAmount
---------------------------------------------------------------
<Unsupported Data Type> | 3 | 75.00
<Unsupported Data Type> | 2 | 50.00

I have no idea why the result produceing <Unsupported Data Type> for the UserID and have been unable to find a way around as of yet. Any help would be appreciated.

View 3 Replies View Related

Receiving There Is An Invalid Number Of Result Bindings Returned For The ResultSetType: ResultSetType_Rowset

Sep 20, 2007



I am trying to set up an Execute SQL Task that will feed into a foreach loop for processing of data. I am using an ADO.NET Connection with Direct input with full result set. I have tried using "select * from <table>" and also "select <column1>, <column2> from <table>". I have user variables set in the package and have the table cfolumn names listed in result name and the appropriate package variable in variable name on the Result Set tab. There is a total of 53 columns per row and the number of rows that should be returned can be anywhere from none to several hundred. Upon trying to run the package, I am receiving
Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings
returned for the ResultSetType: "ResultSetType_Rowset".

Anyone have any ideas here? Thanks in advance!

View 2 Replies View Related

Subquery Returned More Than 1 Value...

Mar 9, 2006

can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.

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.

update purchaseorders set orderstatusID = 4 where purchaseorderid in (
select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null
)

View 1 Replies View Related

Subquery Returned More Than 1 Value

Dec 7, 2011

I am getting the below error while trying to run sql query

Msg 512, Level 16, State 1, Line 1

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

but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.

SELECT substring(sc.container_id,0,14) container_id,
imc.item_class,
sh.ship_to, sh.shipment_id,
cust.ADDRESS1,
sh.carrier,

[code]...

View 3 Replies View Related

Subquery Returned More Than 1 Value?

Mar 25, 2015

i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role

My code is:

Code:
select top 100
username,
(Select b.name
from application b
inner join userroleapplication c
on b.applicationID = c.applicationID ) Application,
(select name from role d, userroleapplication gr
where gr.roleID = d.roleID and gr.userID = a.userID) role
from users a
where username in
(
'username'
)

i know where is the problem but i cant figure how to fix this

What i need as a output

Username | Application | Role

"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.

View 2 Replies View Related

Subquery Returned More Than 1 Value?

Apr 15, 2008

Hi

I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query

The table with data is like this,one file maybe belong to many group.

Filename FilegroupID

file1 1
file2 2
file1 3
file1 4
file3 1
file2 5


I need write a query to get the result like this

file1 1,3,4
file2 2,5
file3 1

I try to write a query with subquery but I get error message

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

I use SQL Server 2005.but I need the subquery return data like "1,3,4".

Please help me.

Thanks

Mark

View 7 Replies View Related

Subquery Returned More Than 1 Value

Dec 18, 2013

I have a simple update query that looks like this:

Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID

I am receiving the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."Yes, it is all contained in the same table.

View 5 Replies View Related

Subquery Returned More Than 1 Value

Jan 26, 2008

Okay,
I want to fill a table in one database with some info from an identical table from another database. When I run this query, I get the following error:

"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."

But the point of the subquery was to in fact return more than one value.

View 2 Replies View Related

Subquery Returned More Than 1 Value

Aug 28, 2007

Okay, this is what I have as my store procedure:

DECLARE @parentPage varchar(250)

-- Insert statements for procedure here

SET @parentPage = (SELECT [frntPage] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID] AND [OLissue].[frntPage] IS NOT NULL AND [Outlook].[parent] IS NOT NULL))


IF @parentPage IS NOT NULL


IF exists (SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID]))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)

ELSE


SELECT DISTINCT NULL ID, 'No parent page yet' title FROM [Outlook]

And I kept getting this error when testing in SQL Management Studio.


Msg 512, Level 16, State 1, Procedure parentPage, Line 19

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

View 1 Replies View Related

Subquery Returned More Than 1 Value....

Apr 9, 2008

I'm having problem Inserting data from 1table into another table. I'm getting 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 get the reason why I'm having this error message but I don't know how to fix code... Help please? Thanks

Here's an example of what I'm trying to do...

table1 has:
ID | Name | countryID
1 | Joe | 101
2 | Ben | 102
3 | Tom | 102



Now I wrote this code to insert some of the data from table 1 into table2...

insert into Table2(CompName, ID) values('Information',(select ID from table1 where countryid=102))

Please Help! Thanks...

View 5 Replies View Related

Select Subquery Returned More Than 1 Value

Dec 24, 2007

select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1
I need to write that kind of sql to return me single value a and multiple values b on each of lines, like
 a            b
----------------------------
tom        small,big,hugh
But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'.  Please help me find a solution, thanks!

View 4 Replies View Related

Message: Subquery Returned More Than 1 Value. ...

Aug 6, 2004

when I run the query
UPDATE dbhal.dbo.tblUser
SET vchrFirstName = FirstName,
vchrLastName = LastName,
vchrLogin = LoginName,
bitActive = Active,
vchrEmailAddress = EmailAddress
FROM TempEmployee, tbluser where EmployeeID = intEmp
and vchrcompid = 'sam'

and there is trigger

CREATE TRIGGER UPDATE_tblUser
ON tblUser
FOR UPDATE AS
DECLARE
@CompanyId as varchar(20),
@intUid as int
SET @CompanyId =(Select vchrCompID from deleted)
Set @intUid = (Select intUid from deleted)
IF @CompanyId = 'sam'
BEGIN
UPDATE [dbPortal].[dbo].tblUser
SET
intEmp = (Select intEmp from inserted),
vchrCompID = (Select vchrCompID from inserted),
vchrPwd = (Select vchrPwd from inserted),
vchrLogin = (Select vchrLogin from inserted),
vchrFirstName = (Select vchrFirstName from inserted) ,
vchrLastName = (Select vchrLastName from inserted),
vchrEmailAddress = (Select vchrEmailAddress from inserted),
bitActive = (Select bitActive from inserted),
intPWDAttempt = (Select intPWDAttempt from inserted),
vchrCreatedWho = (Select vchrCreatedWho from inserted),
dtmCreatedDate = (Select dtmCreatedDate from inserted),
vchrModifiedWho = (Select vchrModifiedWho from inserted),
dtmModifiedDate = (Select dtmModifiedDate from inserted)
WHERE
intUID = @intUid
END


I get the error.
What can I do to get this to run?

View 9 Replies View Related

'Subquery Returned More Than 1 Value' In OpenQuery

Nov 14, 2006

I'm getting the message

"Subquery returned more than 1 value." etc when running a query on a
linked server through OpenQuery. This returns around 6 values
(some of them are fairly long varchar's).
When running another query the same way which returns 2 values,
it works fine.

The problem query also works fine when run locally. It's calling a User-Defined Functions which returns a table, but the second query which works fine also does this.

Where can this subquery be ? Inside the UDF (shouldn't matter, should it?) ?

View 6 Replies View Related

Subquery Returned More Than One Value That Is Not Permitted

Jan 23, 2013

Am getting error:- Subquery returned more than one value,that is not permitted when sub query follows

=,<,>.................................Below is my query
declare @WorkOrder as nvarchar(10)
Set @WorkOrder =( Select X.PrjCode from OPRJ X Where X.PrjCode='[%1]')

SELECT L.Project ,l.U_PINo as [Indent No], M.DocNum AS 'GRN No', M.DocDate as 'Date',
M.CardName as 'Vendor Name',L.ItemCode,l.Dscription,l.Quantity,l.unitMsr as'UOM',l.Price,l.DiscPrcnt ,L.LineTotal,
isnull((SELECT (TaxSum) FROM PDN4 where statype=-90 and DocEntry=M.DocEntry and L.LineNum =PDN4.LineNum),0) +

[Code] .....

View 1 Replies View Related

SQL Server 2008 :: DeadLock In SubQuery With Selfjoin

Oct 3, 2015

Below query. its getting more time to exec and got deadlock. So, query to avoid deadlock.

SELECT m1.Value AS InterfaceName, m1.MessageDateTime, m2.GroupId, COUNT(mError.Id) AS ErrorCount
FROM (
SELECT m1.Value, MAX(m1.MessageDateTime) as MessageDateTime FROM Message m1
WHERE m1.TypeId = 9 AND (m1.Value LIKE 'F02' )
GROUP BY m1.Value

[Code] ....

View 2 Replies View Related

Update Trigger !!!! Subquery Returned More Than 1 Value.

Jun 7, 2001

Any help would be appreciated.
Thanks.
Steve

The error below is encounter when a mass update is attempted on our employee table.

Server: Msg 512, Level 16, State 1, Procedure emp_lock_id, Line 8
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.

The Trigger code is below


CREATE TRIGGER emp_lock_id ON dbo.EMPLOYEE
FOR UPDATE
AS
DECLARE @NEXT_NUM DECIMAL(17, 12)
DECLARE @NEW_NUM DECIMAL(17, 9)
DECLARE @OLD_LOCK_ID DECIMAL
DECLARE @NEW_LOCK_ID DECIMAL
SET @OLD_LOCK_ID = (SELECT EMP_LOCK_ID FROM DELETED)
SET @NEW_LOCK_ID = (SELECT EMP_LOCK_ID FROM INSERTED)
IF @OLD_LOCK_ID <> @NEW_LOCK_ID
BEGIN
RAISERROR('Record Modified by different user.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
SET @NEXT_NUM = (convert(decimal(17,12), getdate()))
SET @NEW_NUM = @NEXT_NUM * 1000
UPDATE EMPLOYEE SET EMP_LOCK_ID = (@NEW_NUM - floor(@NEW_NUM)) * 1000000000
FROM EMPLOYEE, INSERTED
WHERE EMPLOYEE.EMP_EMPLOYEE_ID = INSERTED.EMP_EMPLOYEE_ID
END

View 6 Replies View Related

UPDATE Statement: Subquery Returned More Than 1 Value

Oct 3, 2007

I am struggling with syntax for an update statement.
Goal: If a RelationID has invoice history then HasHistory=1


UPDATE [Tbl_Relations]
SET [HasHistory]= 1
FROM [Tbl_Relations] r INNER JOIN
(SELECT RelationID
FROM vw_Invoice_history
GROUP BY RelationID
) inv ON r.RelationID= inv.RelationID

Result: Subquery returned more than 1 value

What am I doing wrong?

Thanks in advance,
Frans

View 9 Replies View Related

SQL Server 2008 :: Subquery In SSAS Named Calculation

May 8, 2015

I am trying to get a date diff in a named calculation using inner join I am getting

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

I have checked online and have been able to get some of my codes right by using the where condition in place of the inner join, but I have not been able to work around the datediff one

(select
DATEDIFF(day,TDate,[DID])
FROM [dv].[dbo].[Dail] as D
inner join
[dv].[dbo].[ANT] as A
on
a.AID = d.AID)

View 0 Replies View Related

A Transport-level Error Has Occurred When Receiving Results From The Server

Nov 27, 2006

Hi all,

I
am trying to run a stored procedure which retreives 3 lakhs of records
and updates the data and moves few of those records to some tables.
Since the number of records are more , the time taken for the storede
procedure is around 30 minutes when i directly execute it in query
analyzer.

But I need to execute it from Visula Studio.Net 2005
(c#). Whole of the application contains only one form with a single
button.When I click on the button , this stored procure has to be
executed. But I am getting an error as shown below.
"A
transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The specified network name
is no longer available.)"

Database used is SqlServer 2000

How to solve this error?. Any ideas are really appreciated.

Thanks and Regards,
Sukanya.

View 4 Replies View Related

A Transport-level Error Has Occurred When Receiving Results From The Server. - After Installing New Instance Of SQL Server 2005 W/NO App Changes

Sep 27, 2006

We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking.  The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end.  This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions.   So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help! 

View 2 Replies View Related

Receiving Error Message SERVER FAILED TO CONFIGURE THE SERVER.

May 24, 2005

I installed Micrsoft Server Desktop Engine from the CD I received in
my ASP.net book. The installation went fine and then I went to the DOS
prompt and did the "setup SAPWD..." and it ran. I restarted my computer and
opened ASP Matrix and went to the Data tab and proceed to connect to
the server. It gave me an error message it couldn't connect.

So I
uninstalled MSDE and made sure the sql2ksp3 folder was gone. So I went
ahead and installed the MSDE again from the CD again and then went to
DOS
to do the setup. It ran through the setup until the very end with 11
seconds remaining and received error message " Setup failed to
configure the server. Refer to the server error logs and setup logs for
more information. I have tried it several times and it always
stops around 11 seconds. I have tried to install the download from
microsoft.com
 instead the CD and that didn't work also. So at this point I
am stuck. PLEASE HELP!

I also checked my Registry keys for a file called PendingFileRenameOperations and it wasn't in the registry keys.

If anyone has any suggestions I would appreciate it.

View 5 Replies View Related

Receiving An Error Message That There Is Not Sufficient Disk Apce To Intall SQL Server (but There Is)

Feb 6, 2008

I downloaded this program, and when trying to intall I am receiving an error meesage that there is insufficient disk space to install.

I have 98GB available on C and 107 GB available on D. I am on an IBM Intellistation dual processor 3,08 Ghz with 4GB RAM and 240 GB Disk (those are "G"'s), so I have tons of capacity. This has to be a problem with the .msi - any suggestions?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved