Running Linked Server Out-of-proc

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Running Linked Server Out-of-proc

Postby Moderator » Sun Sep 20, 2009 4:30 pm

Several of our customers were asking how to configure Linked Server to PostgreSQL to run "out-of-proc", i.e. in a separate process. The advantage is that possible memory leaks and crashes in the PGNP provider won't affect SQL Server process since PGNP provider runs in a separate surrogate process (dllhost.exe).
Here are steps:
1. Register a surrogate process for PGNP provider to make it visible in DCOMCNFG utility. For this either run OleView.exe utility (part of Visual Studio or SDK) or apply registry script (see below).
In OleView expand "All Objects" in the treeview on the left and select "PostgreSQL Native Provider" object. Goto "Implementation" tab and check "Use Surrogate Process" option. In the edit box below specify path to dllhost.exe, for example: C:\WINDOWS\system32\dllhost.exe. Then select "Registry" tab and you will see that DllSurrogate named value was added under AppId key.
Following scripts can be executed instead of running OleView.

For 64bit PGNP provider:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_CLASSES_ROOT\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP64.dll"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP64.dll"
"ThreadingModel"="Both"

For 32bit PGNP provider on 64bit Windows:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Wow6432Node\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"

For 32bit PGNP provider on 32bit Windows:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\System32\\dllhost.exe"
[HKEY_CLASSES_ROOT\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\System32\\dllhost.exe"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files (x86)\\PGNP\\PGNP.dll"
"ThreadingModel"="Both"

2. Run DCOMCNFG.exe utility. Expand "Component Services", "Computers", "My Computer" and select "DCOM Config" folder. In the right pane find application named "{3170DFF1-4803-42a0-A1B3-D14656857070}", right-click and select Properties. Configure Security and Identity in the corresponding tabs. Default values could work in many cases. Close the utility.

3. In SQL Server Management Studio uncheck "allow inprocess" option in PGNP provider properties. The changes are immediate and usually do not require restart of SQL Server or the computer.

Perform a query for the Linked Server. If issue occurs see Events Viewer for errors information. If a test query succeeded you can use Process Explorer (http://www.sysinternals.com) to find which surrogate process hosts the PGNP provider dll.
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Running Linked Server Out-of-proc

Postby peer1bi » Thu Apr 03, 2014 12:06 pm

I just upgraded PGNP provider to 1.4 version, and I wanted to use out-of-roc option from my linked servers. I don't have an OLEView.exe file and I tried to run the script you gave for 64bit PGNP version. I saved it in .bat file and tried to run but unsuccessfully. Then I tried to run it line by line. After the first line ([HKEY_CLASSES_ROOT\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}] ) I got this error message: The system cannot find the path specified. I got the same message after each line I ran. Also in your script, the PGNP64.dll file is in C:\\Program Files(x86)\\PGNP\\PGNP64.dll", but my file is in C:\\Program Files(x86)\\Intellisoft\\PGNP\\PGNP64.dll". Could that be the problem?
peer1bi
 
Posts: 3
Joined: Wed Mar 19, 2014 7:07 am

Re: Running Linked Server Out-of-proc

Postby Moderator » Thu Apr 03, 2014 10:00 pm

Save script in a file with .reg extension. Double-click on it to execute.
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Running Linked Server Out-of-proc

Postby peer1bi » Fri Apr 04, 2014 8:17 am

Thank you, I executed the file with .reg extension and it worked, but the linked servers don't. they work fine when I check back the "allow inprocess" option. However, when I uncheck it and try to run the query against linked servers I get this error message: Cannot create an instance of OLE DB provider "PGNP" for linked server 'server_name'. I checked the event viewer and there are no entries. I downloaded Process Explorer and checked the Handle of DLL substring for PGNP64.dll and it is sqlservr.exe 5792 DLL C:\Program Files (x86)\Intellisoft\PGNP\PGNP64.dll. This seams correct.
Just to make sure that the .reg file I ran has correct path, this is the file's contents:

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_CLASSES_ROOT\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files(x86)\\Intellisoft\\PGNP\\PGNP64.dll"
"ThreadingModel"="Both" [HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\{3170DFF1-4803-42a0-A1B3-D14656857070}]
"DllSurrogate"="C:\\Windows\\SysWow64\\dllhost.exe"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{3170DFF1-4803-42a0-A1B3-D14656857071}\InprocServer32]
@="C:\\Program Files(x86)\\Intellisoft\\PGNP\\PGNP64.dll"
"ThreadingModel"="Both"
peer1bi
 
Posts: 3
Joined: Wed Mar 19, 2014 7:07 am

Re: Running Linked Server Out-of-proc

Postby peer1bi » Mon Apr 14, 2014 11:17 am

Hi,
I fixed the previous issue, which was authentication, and my servers are now running out-of-proc. However, I am unable to execute any of my queries. Every time I try to run queries that grab data from linked servers I get the query result for may be a second, and then the message appears:
Msg 0, Level 11, State 0, Line 0
"A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."
Did any one else see this happening after moving PGNP to out-of-proc? Is there a way to fix this issue?

Thank you.
peer1bi
 
Posts: 3
Joined: Wed Mar 19, 2014 7:07 am

legd <a href=http://www.studiogo.co.uk/BottegaVeneta.php><st

Postby ljtbkqzsvyrg » Sun Apr 20, 2014 7:18 am

I will not focus on who from his office, upstairs office who lets out a moan. Sac Bottega Veneta Prix Celine Tote Team up your clubwear with a good pair of dress shoes; no sneakers or formal shoes please. Herve Leger Dresses Bottega Veneta Especially in the Chinese wedding, the bridal's dress will include cheongsam. Portafoglio Celine Celine Bags Online In 10 years, CBSA has helped over 26,000 students receive in excess of $170 million dollars in financial assistance. Bottega Veneta 26 is dad, i always remember, i have to say that i had no serious demerit, this is also a night from grandma population in the. Bottega Veneta Outlet Bottega Veneta Paris I live quite far from tibet, but tibet is my dream is in. Celine Bags Prices <a href=http://www.mauijimhorsetrials.org/CelineBags.php><strong>Celine Bags Online</strong></a> He put the cup to the next minister du, du minister to yefi said :can not do ah ! Celine Tote <a href=http://eltonder.com/BottegaVeneta.php><strong>Bottega Veneta Outlet</strong></a> How i see the world, will be what kind of world, that once complain and retreat, is threatening their futile damage, life just needs to change a point of view, there will be new discoveries, can have more flexibility, better understanding. Bottega Veneta Bags
xhtsuppv http://eltonder.com/CelineBags.php xbicermq
ljtbkqzsvyrg
 
Posts: 24
Joined: Fri Apr 18, 2014 7:16 pm

amub <a href=http://www.cdisa.ca/CelineBags.php><strong>Celi

Postby ljtbkqzsvyrg » Sun Apr 20, 2014 7:18 am

His heart has been deeply to bring out his tall image of perfection, tian shi-guo is in his own actions to answer who statement inch grass heart, changes of such a question of the millennium problems. Borse Celine Bottega Veneta Outlet Online With the addition of Chaps, we achieve a fuller spectrum of styles that address the fashion demands of today's male shopper. Bottega Veneta Bottega Veneta Prezzi MICROSRetail customers include over 340 premier companies such as Aeropostale, Barney's New York, The Finish Line, Ritz Camera, IKEA, The Limited, Nike Retail, Payless ShoeSource, Polo Ralph Lauren, Bare Escentuals, Tween Brands, Staples, Starbucks Coffee, Talbots, Guess, Peruvian Connection, Omaha Steaks, and Timberland. Bottega Veneta Taschen Bottega Veneta Handbags As part of the makeover, Web work shifted from Columbia's marketing department to an ecommerce team of about a dozen, including veterans of other companies. Sac Celine Solde Finally she also branch of distracted, unable to concentrate to do the work, not attentively read a book, or even can not be absorbed in a chat with a friend. Celine Bags Online Herve Leger Dresses Free to fly, not thought to seize all the time, feel the absolutely empty hands. Borsa Celine <a href=http://perfect-dream.fr/BottegaVeneta.php><strong>Bottega Veneta Soldes</strong></a> I don't know why, and recently miss him very much, feel that the brain does not already own. Herve Leger Bathing Suit <a href=http://perfect-dream.fr/BottegaVeneta.php><strong>Bottega Veneta</strong></a> Have a look, time is not very long, not until lunch time to watch tv. Bottega Veneta Online Shop
pnkaznxv http://civitavecchia-olbia.it/CelineBags.php zevawyhy
ljtbkqzsvyrg
 
Posts: 24
Joined: Fri Apr 18, 2014 7:16 pm

dgfo <a href=http://www.studiogo.co.uk/CelineBags.php><stron

Postby ljtbkqzsvyrg » Sun Apr 20, 2014 7:19 am

I don't know whether joan knew all this, but to go back to school, yoshikazu remain unchanged phase hold out and in, depend on each other, very close, all the while, live together peacefully. Celine Bags Prices Bottega Veneta Soldes This allows your feet to breathe, and to stay dry and comfortable. Celine Borse Prezzi Celine Bag Do what you know: It's what Burch does. Bottega Veneta Outlet Bottega Veneta The spacious interior with the car keys of little space d ring. Herve Leger Bandage Dress After growing up, enjoying the fresh air conditioning popsicle, comfort, a cup of tea, a piece of titanic , also can use the entire afternoon to shuttle at all times and in all countries, in the classic build emotional sky, find that the most gentle one act. Herve Leger Dresses Herve Leger God intends to take care of me, or i should have the privilege of fate. Bottega Veneta Price <a href=http://annabegins.co.uk/CelineBags.php><strong>Celine Tote Bag</strong></a> Have a good mood, we have self-confidence, have confidence have young and healthy. Herve Leger Dress <a href=http://www.mauijimhorsetrials.org/HerveLeger.php><strong>Herve Leger Dress</strong></a> I love you, i didn i have been waiting for you here i know you will come back, his hands stroked my cheeks, who put down, peaches, turned a full circle didn was coming back to you, oh i dead, oh oh. Bottega Veneta Paris
hhzhribj http://www.studiogo.co.uk/BottegaVeneta.php qyeanzbf
ljtbkqzsvyrg
 
Posts: 24
Joined: Fri Apr 18, 2014 7:16 pm

itji <a href=http://www.samurai-kommunikation.de/CelineTasch

Postby ljtbkqzsvyrg » Sun Apr 20, 2014 7:21 am

I feel the dream is so sad and helpless tears seems across dream dipped a pillow towel. Bottega Veneta Outlet Bottega Veneta Clutch We get a list of elementary students in our district that are not able to afford a lot of Christmas presents, and so, we raise money to take them shopping at Target, she said. Bottega Veneta Celine Boston Bag It is known as a hidden placket because you only see one seamat the bottom. Celine Bag Price Celine Bag Uk s signifies, Health club membership, Chocolate, The washing. Bottega Veneta Outlet Although some things in life are in my memory has become blurred, but for you i will always be with the passage of time and gradually. Celine Bag Uk Bottega Veneta Outlet However, please do not understand me on my back, find fault with. Herve Leger Dress Sale <a href=http://civitavecchia-olbia.it/BottegaVeneta.php><strong>Bottega Veneta Borse</strong></a> I once said, you are to me, is the unexpected surprise. Celine Bags Online <a href=http://www.studiogo.co.uk/HerveLeger.php><strong>Herve Leger Swimsuit</strong></a> I am the city by car, when to start exercising in the new track, i started with a few tears to say goodbye to the last scenery. Bottega Veneta
eudqjapq http://eltonder.com/HerveLeger.php silfidkp
ljtbkqzsvyrg
 
Posts: 24
Joined: Fri Apr 18, 2014 7:16 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 2 guests