We help IT Professionals succeed at work.

linked server   one connection whole procedure

406 Views
Last Modified: 2008-09-20
i have following part of procedure

-- PRODUCT SPECIALIST
      -- Cursor om alle benodigde data op te halen
      DECLARE CSR_ProdSpec CURSOR FAST_FORWARD
      FOR      SELECT DISTINCT BrandID, ProductGroupID FROM Item.ItemTemp

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

      -- Loopen zolang er records gevonden zijn
      WHILE (@@FETCH_STATUS = 0)
       BEGIN
            -- 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
        END

      -- Cursor afsluiten en verwijderen
      CLOSE CSR_ProdSpec
      DEALLOCATE 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

greetings
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
it is setup permanently the linked server    Lnk_DolmenReportingEproc2

the openquery statement just demands you to put the name of the linked server in the statement
and in sql statement we use the 4 part name

it is just that the openquery statement (which we use to call a function in oracle) makes
every time a connection i think instead that i want that the connection once made
has to stay open for whole the procedure

Commented:
Oh yes, I see what you mean.

Commented:
Can you set up the oracle provider so it does connection pooling?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.