troubleshooting Question

linked server one connection whole procedure

Avatar of kvdi765
kvdi765 asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005
4 Comments1 Solution411 ViewsLast Modified:
i have following part of procedure

      -- Cursor om alle benodigde data op te halen
      FOR      SELECT DISTINCT BrandID, ProductGroupID FROM Item.ItemTemp

      -- Cursor openen om de data te overlopen
      OPEN CSR_ProdSpec
      -- Eerste record opvragen
      INTO @Brand, @ProdGroup

      -- Loopen zolang er records gevonden zijn
      WHILE (@@FETCH_STATUS = 0)
            -- Voor het merk in Eproc enkel de eerste 5 characters nemen
            SET @TempBrand = LEFT(@Brand,5)

            -- De ProductSpecialist ophalen voor dit artikel
            SET @OracleCall = 'SELECT @TempEmplId = EmplId FROM OPENQUERY(Lnk_DolmenReportingEproc2, ''SELECT OFFR.PKG_CREART.sf_bepaal_productmanager(''''' + @TempBrand + ''''',''''' + @ProdGroup + ''''',''''' + @DefaultMgr + ''''') AS EmplId FROM DUAL'')'

            SET @OracleParams = N'@TempEmplId varchar(5) OUTPUT'
            EXEC sp_executesql @OracleCall, @OracleParams, @TempEmplId = @TempSpecialist OUTPUT

            -- De P van het EmployeeID afhalen
            SET @ProdSpecialist = LEFT(@TempSpecialist,4)

            -- Items met deze Brand en ProdGroup updaten met de product specialist
            UPDATE Item.ItemTemp
            SET ProductSpecialist = @ProdSpecialist
            WHERE BrandID = @Brand
            AND ProductGroupID = @ProdGroup

            -- Volgende record ophalen
            FETCH NEXT FROM CSR_ProdSpec
            INTO @Brand, @ProdGroup

      -- Cursor afsluiten en verwijderen
      CLOSE CSR_ProdSpec

for each record in the cursor we submit a function on a oracle database with a linked server
my opinion is that if we have 5000 records in the cursor, that he is gonna make 5000 times
a connection with oracle through the linked server
for 5000 records it takes 15 minutes at the moment

is there a possibility to open the linked server connection once in the beginning of the procedure
and end it at the end of the procedure
this will save us much time i think

any help is welcome


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros