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: 293
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: 293
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

Re: Running Linked Server Out-of-proc

Postby Moderator » Mon Jun 16, 2014 5:23 pm

Hi,
somehow this question was marked as answered, but I cannot find the answer. If this is still an issue, would you e-mail more details to our Support address?

Thank you!
User avatar
Moderator
Site Admin
 
Posts: 293
Joined: Wed Oct 29, 2008 11:27 pm


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

Who is online

Users browsing this forum: No registered users and 3 guests

cron