PGNP Query crashes SQL Server 2008

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

PGNP Query crashes SQL Server 2008

Postby sray » Tue Feb 28, 2012 11:32 am

Faulting application sqlservr.exe, version 2007.100.4000.0, time stamp 0x4c935741, faulting module PGNP64.dll, version 1.3.0.2251, time stamp 0x4e6e25ae, exception code 0xc0000409, fault offset 0x0000000000230ec6, process id 0xf08, application start time 0x01ccf63034b3db42.


I've been using PGNP successfully for a few months now without any issues, however today running a simple query is crashing SQL Server as noted above. The query in question is very simple:

Code: Select all
USE *database*

SET XACT_ABORT ON
BEGIN TRAN

DECLARE @rate_centers TABLE (
   cache_id            INT NOT NULL PRIMARY KEY IDENTITY(1,1),
   lineclasscode1         VARCHAR(255),
   lcg_rate_center_id      INT
)
INSERT INTO @rate_centers (
   lineclasscode1,
   lcg_rate_center_id
)
SELECT
   d.switch_value,
   d.lcg_rate_center_id
FROM
   OPENQUERY(BBI, '
      SELECT
         ms.switch_value,
         lc.lcg_rate_center_id
      FROM
         metaswitch_rate_centers                     AS ms,
         lcg_rate_centers                        AS lc,
         lcg_rate_center_metaswitch_rate_center_mapping    AS ma
      WHERE
            lc.lcg_rate_center_id   = ma.lcg_rate_center_id
         AND ms.rate_center_id      = ma.ms_rate_center_id
   ') AS d

ROLLBACK TRAN


The 'BBI' Linked Server is configured with the following options:

Code: Select all
EXEC master.dbo.sp_addlinkedserver @server = N'BBI', @srvproduct=N'PGNP', @provider=N'PGNP', @datasrc=N'*server*', @catalog=N'*database*'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BBI',@useself=N'False',@locallogin=NULL,@rmtuser=N'*username*',@rmtpassword='*password*'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'BBI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


And the PGNP provider is configured with:
    Dynamic Parameter
    Nested Queries
    Allow inprocess
    Supports 'Like' operator

And is connecting to a Postgres 8.4.2 server.

Any ideas? I'm planning a full reboot of the server later and some more testing to try and narrow things down, however I can't do any testing during the day.
sray
 
Posts: 5
Joined: Tue Feb 28, 2012 10:59 am

Re: PGNP Query crashes SQL Server 2008

Postby sray » Tue Feb 28, 2012 6:48 pm

Figured out my own problem.

The table name, 'lcg_rate_center_metaswitch_rate_center_mapping' was too long for PGNP. I created a view that selected everything from that table but had a much shorter name and everything works fine. I'm really surprised that PGNP would crash SQL Server so badly over something so insignificant though. Is this a known bug and/or fixed in 1.4?

Thanks,

Seoras.
sray
 
Posts: 5
Joined: Tue Feb 28, 2012 10:59 am

Re: PGNP Query crashes SQL Server 2008

Postby Moderator » Wed Feb 29, 2012 12:20 am

The issue had been fixed both in v1.3 and v1.4. We have sent you the product update that contains the fix for the issue.

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

Re: PGNP Query crashes SQL Server 2008

Postby sray » Wed Feb 29, 2012 4:10 am

Much appreciated, many thanks!

Seoras.
sray
 
Posts: 5
Joined: Tue Feb 28, 2012 10:59 am


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

Who is online

Users browsing this forum: Google [Bot] and 1 guest

cron