problem with "subquery returned more than 1 value"

Hi guys,
I'm getting an error of 'subquery returned more than 1 value' when I try to
run this, which I guess is because the @.remoteID is being set to a SELECT
that returns multiple rows. How do I go about this so that I can accomplish
checking for all existing IDs on the local server (against the remote
server); updating each matching ID; or else inserting a new one?
I think I've got the overall concept right, at least in terms of the
updates/inserts, but need to clarify for SQL Server the exact row to be
involved in any update. Then again, since I'm still trying to learn I could
also be way off...
CollectionID (identity) is the primary key in the table.
#######
ALTER PROCEDURE dbo.usp_CollectionUpdate
AS
BEGIN
DECLARE @.serverUP varchar(100)
SET @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
BEGIN
-- is the remote server available
IF LEN(@.serverUP) > 0
DECLARE @.getCount int
SET @.getCount = ( SELECT COUNT(CollectionID)
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- are there rows to update
IF @.getCount > 0
DECLARE @.remoteID int
SET @.remoteID = ( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- match found so update the id
IF @.remoteID IN ( SELECT CollectionID
FROM dbo.tblCollection )
UPDATE dbo.tblCollection
SET LastCollection = t2.LastCollection, Notes = t2.Notes
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
WHERE (dbo.tblCollection.CollectionID = @.remoteID)
-- no match so insert a new one
ELSE
SET IDENTITY_INSERT dbo.tblCollection ON
INSERT INTO dbo.tblCollection (CollectionID, LastCollection, Notes,
SourceID)
SELECT CollectionID, LastCollection, Notes, SourceID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
END
END
END
END
#######
Any help is appreciated. Thanks.
Message posted via http://www.webservertalk.comIf you get more than one result back from the query you should go for the IN
Clause rather than the EQUAL, but that wont work for you here because you
are going to apply the result set to a variable, so i try to reconstruct
your procodure, without able to test it anyway:
Perhaps you should go with this exmaple just as a suggestion, dont know if
the logical is the right one:
ALTER PROCEDURE dbo.usp_CollectionUpdate
AS
BEGIN
DECLARE @.serverUP varchar(100)
SET @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
BEGIN
-- is the remote server available
IF LEN(@.serverUP) > 0
DECLARE @.getCount int
SET @.getCount = ( SELECT COUNT(CollectionID)
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- are there rows to update
IF @.getCount > 0
DECLARE @.remoteID int
SET @.remoteID = ( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
UPDATE dbo.tblCollection
SET LastCollection = t2.LastCollection, Notes = t2.Notes
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
INNER JOIN
( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
tSubquery
ON tSubquery.CollectionID = dbo.tblCollection.CollectionID
-- no match so insert a new one
SET IDENTITY_INSERT dbo.tblCollection ON
INSERT INTO dbo.tblCollection (CollectionID, LastCollection, Notes,
SourceID)
SELECT tc1.CollectionID, tc1.LastCollection, tc1.Notes, tc1.SourceID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection tc1
LEFT JOIN dbo.tblCollection tc
ON tc.CollectionID = tc1.CollectionID
Where tc.CollectionID IS NULL
END
END
END
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi
@.serverUP could also be affected as linked servers get placed in sysservers
too. That table can have 1 or more rows.
If you wanted to keep the code simple, having cursors might be the answer,
look at DECLARE CURSOR in BOL for @.serverUP and @.remoteID.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:d9a70799ec74474f8a11040b58bece66@.SQ
webservertalk.com...
> Hi guys,
> I'm getting an error of 'subquery returned more than 1 value' when I try
> to
> run this, which I guess is because the @.remoteID is being set to a SELECT
> that returns multiple rows. How do I go about this so that I can
> accomplish
> checking for all existing IDs on the local server (against the remote
> server); updating each matching ID; or else inserting a new one?
> I think I've got the overall concept right, at least in terms of the
> updates/inserts, but need to clarify for SQL Server the exact row to be
> involved in any update. Then again, since I'm still trying to learn I
> could
> also be way off...
> CollectionID (identity) is the primary key in the table.
> #######
> ALTER PROCEDURE dbo.usp_CollectionUpdate
> AS
> BEGIN
> DECLARE @.serverUP varchar(100)
> SET @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> BEGIN
> -- is the remote server available
> IF LEN(@.serverUP) > 0
> DECLARE @.getCount int
> SET @.getCount = ( SELECT COUNT(CollectionID)
> FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
> WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
> BEGIN
> -- are there rows to update
> IF @.getCount > 0
> DECLARE @.remoteID int
> SET @.remoteID = ( SELECT CollectionID
> FROM
> [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
> WHERE (DATEDIFF(d, LastCollection, GetDate()) =
> 1) )
> BEGIN
> -- match found so update the id
> IF @.remoteID IN ( SELECT CollectionID
> FROM dbo.tblCollection )
> UPDATE dbo.tblCollection
> SET LastCollection = t2.LastCollection, Notes = t2.Notes
> FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
> WHERE (dbo.tblCollection.CollectionID = @.remoteID)
> -- no match so insert a new one
> ELSE
> SET IDENTITY_INSERT dbo.tblCollection ON
> INSERT INTO dbo.tblCollection (CollectionID, LastCollection, Notes,
> SourceID)
> SELECT CollectionID, LastCollection, Notes, SourceID
> FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
> END
> END
> END
> END
> #######
> Any help is appreciated. Thanks.
> --
> Message posted via http://www.webservertalk.com|||From reading your SP, I have several comments
1) your indenting implies that you want the whole set of statements after
the IF @.getCount > 0 to run only if @.GetCount > 0... But the way you wrote i
t
the only statement that will be conditionally executed is the declare ...
Everything aftre that will run whatever @.getCount is. IS that what you want
?
If you want an entire block of multiple statements, to run conditionally -
based on an If statement, then the entire block must be placed in a Begin /
End construction, and must immediately follow the IF.
DECLARE @.remoteID int
SET @.remoteID = ( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- match found so update the id
IF @.remoteID IN ( SELECT CollectionID
FROM dbo.tblCollection )
UPDATE dbo.tblCollection
SET LastCollection = t2.LastCollection, Notes = t2.Notes
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
WHERE (dbo.tblCollection.CollectionID = @.remoteID)
-- no match so insert a new one
ELSE
SET IDENTITY_INSERT dbo.tblCollection ON
INSERT INTO dbo.tblCollection
(CollectionID, LastCollection, Notes,SourceID)
SELECT CollectionID, LastCollection, Notes, SourceID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
END
2. Next, WHy are you calculating and storing the value of @.ServerUP? The
only thing you are using it for is to test whether the SRVName attribute in
theremote table is zerilength ... You can do that without storing teh value.
using One of the following:
If (Select Len(SRVNAME)
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers ) > 0
-- (If you really want to check the len of the data in an existing row)
or,
If Exists (Select * From [xxx.xxx.xxx.xxx].master.dbo.sysservers )
-- If you really only need to check if there's row there at all....
3. Same issue with detecting if there are any records with
(DateDiff(d, LastCollection, GetDate()) = 1 IF @.getCount > 0 You don't
need to store the value of this in a variable... It looks lije you're just
trying to alternatly run an Insert for rows not already in the destination,m
and an Update fr th ones tha tare... So, Just do that. Run both the Update -
on the rows which are already in Destination, - and the Insert for the rows
that are not, (in that order).
Anyway, assuming that in item 2 you need to check the length.. your stored
Proc could be rewritten as:
-- ****************************************
ALTER PROCEDURE dbo.usp_CollectionUpdate
AS
Set NoCount On
If (Select Len(SvrName)
From [xxx.xxx.xxx.xxx].master.dbo.sysserversIf) > 0
Begin
Update dbo.tblCollection Set
LastCollection = T2.LastCollection,
Notes = T2.Notes
From [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection T2
Join dbo.tblCollection C
On C.CollectionID = T2.CollectionID
Where DATEDIFF(d, T2.LastCollection, GetDate()) = 1
-- ----
SET IDENTITY_INSERT dbo.tblCollection ON
Insert dbo.tblCollection (CollectionID, LastCollection,
Notes,SourceID)
Select CollectionID, LastCollection, Notes, SourceID
From [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection T
-- And Don't you need to restrict Insert to rows not already in
there '
Where Not Exists (Select * From dbo.tblCollection
Where CollectionID - T.CollectionID)
SET IDENTITY_INSERT dbo.tblCollection Off -- Got to set it back !!!
End|||Thanks...
This certainly seems much easier; and thanks for pointing out my errors -
this helps me understand the 'why' as I go along. So hopefully I don't make
the same mistakes going forward.
You guys (ALL) are awesome - an invaluable resource for those like me who
are just getting started with SQL Server.
Message posted via http://www.webservertalk.com