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.

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: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

If you get more than one result back from the query you should go for the IN
Clause rather than the EQUAL, but that won´t 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, don´t 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
---

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

Retrieving data from multiple tables
add column
ntext and update/insert triggers
Need help with SELECT statement please.
Rollback