NotePage, Inc. Forum Index

PageGate 7 with SQL Server - slow, ODBC timeouts

 
Post new topic   Reply to topic    NotePage, Inc. Forum Index -> Integration Related Questions
View previous topic :: View next topic  
Author Message
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Mon Feb 15, 2016 4:32 pm    Post subject: PageGate 7 with SQL Server - slow, ODBC timeouts Reply with quote

BACKGROUND:
- We're running PageGate 7.0.13 on Windows 2008 R2 Standard against a SQL Server 2008 database.
- All users are being messaged via SMTP (using various carrier gateways)
- Messages are sent through an IIS SMTP gateway.
- Messages are being created & scheduled by inserting rows into the database via a custom SQL Server stored procedure

Generally speaking, the integration works well. But when sending large numbers of messages (15-20 or so), the system takes 5-10 minutes to send these messages. Looking at the logs, there seems to be no SMTP activity during that time period, then at the end of the time period there's a flurry of SMTP activity and all the messages go out.

Every so often, I also see "3146 ODBC Call Failed" errors in the Scheduler monitor window.

Also during this time period, there seems to be some sort of record lock happening, and other applications that attempt to insert more messages get an ODBC timeout.

There seems to be no correlation between a client getting an ODBC timeout and seeing the ODBC call failed error in the logs. In other words, if I schedule 20 messages, then just let the system sit there, the logs will show ODBC failures.

I'm using this to integrate with some fairly mission-critical systems and would like to know what we may be doing wrong.

P.S.: I found that another service was using UDP port 10101 on this server and have stopped that service. I have not had opportunity to test after making this change. I have heard that UDP is used for monitoring things, but I'm confused about why this other service didn't totally prevent monitoring from working.


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4370

PostPosted: Mon Feb 15, 2016 4:35 pm    Post subject: Reply with quote

While I'm investigating the error code you're receiving, the PageGate Monitor does use UDP port 10101 to receive monitoring information from the PageGate Server, even on the local server where the system broadcasts the information to the local loopback address.



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Mon Feb 15, 2016 4:39 pm    Post subject: Reply with quote

One other thing to note: I suspect that PageGate may be holding a row or table lock open for a very long time for some reason. Here's why I suspect this: I have a stored procedure that joins to PageGate tables to create a read-only recordset to display message status in another application.

When PageGate was working to send messages, that stored procedure would time out. Until I put the following code at the top:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This basically ignores locks and allows "dirty" reads. Since adding this code, I can execute the stored procedure while waiting for PageGate to send the messages.


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4370

PostPosted: Mon Feb 15, 2016 5:01 pm    Post subject: Reply with quote

Let's start with updating PageGate to the latest version. There have been a few patches between what you're running and now that may help.

1) Make a backup copy of the PageGate Database (pagegate.mdb) typically located in c:\PageGateData\Database\

1a) If you want to avoid rebooting the server, stop the PageGate service, close all PageGate related applications, close any active connections to the database, then run the Task Manager in Windows and terminate the pgstray.exe process.

2) Use these steps to download and install the latest build of PageGate overtop of what you have now:
http://www.notepage.net/install-pagegate-7-on-vista-7-8-2008-or-2012.htm

3) After the installation has finished, switch the Run Style for PageGate back to a Windows Service: http://www.notepage.net/videos/pagegate-run-styles.htm



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
OzCom



Joined: 22 Oct 2003
Posts: 1122
Location: US

PostPosted: Mon Feb 15, 2016 5:26 pm    Post subject: Reply with quote

Please email support with your Serial Number as well - this will help us understand what modules are licensed and running



_________________
Aaron D. Osgood
Streamline Communications, LLC
* Authorized Notepage, Inc. Development Partner and Re-seller
TEL: 207-518-8455
AOsgood@streamline-solutions.net
Creators of AutoPage Spillman CAD interface for PageGate
Back to top
View user's profile Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Mon Feb 15, 2016 8:08 pm    Post subject: Reply with quote

I can't take the system offline to perform an upgrade at the moment, but should be able to do so sometime in the next 24 hours. I will post results.


Back to top
View user's profile
OzCom



Joined: 22 Oct 2003
Posts: 1122
Location: US

PostPosted: Tue Feb 16, 2016 5:49 pm    Post subject: Reply with quote

Any Update?



_________________
Aaron D. Osgood
Streamline Communications, LLC
* Authorized Notepage, Inc. Development Partner and Re-seller
TEL: 207-518-8455
AOsgood@streamline-solutions.net
Creators of AutoPage Spillman CAD interface for PageGate
Back to top
View user's profile Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Wed Feb 17, 2016 9:21 am    Post subject: Reply with quote

We were able to complete the upgrade yesterday, but have not had a chance to run more tests to see if performance has improved with the upgrade.


Back to top
View user's profile
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Wed Feb 17, 2016 10:04 am    Post subject: Reply with quote

Behavior seems about the same after the update. While PageGate is sending a bunch of messages, I cannot use our stored procedure to add more messages...there appears to be some kind of lock (based on behavior, not definitive investigation).


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4370

PostPosted: Wed Feb 17, 2016 10:09 am    Post subject: Reply with quote

Thank you for updating. Typically speaking, the 3164 error code spawns when a null value, rather than a blank string value, is inserted in to a field or a field is left blank when it shouldn't be. Any time the program runs across a null value in a field where it shouldn't be, it causes the program to encounter an error that knocks the Scheduler service in to a repair and cleanup routine. The repair and cleanup routine then find the value, correct it and the message(s) can flow through the system normally.

So, I know this is going to be a bit lengthy but, if you would, please let me know what data type you're writing to each field in the messages table when you spawn a line.



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Wed Feb 17, 2016 10:10 am    Post subject: Reply with quote

Ha, I was about to point out some differences between the messages I'm creating with our custom stored procedure and messages created by PageGate Admin. I have them in a spreadsheet--is there a way to attach that here? I don't see one.


Back to top
View user's profile
Tech Support



Joined: 25 Aug 2003
Posts: 4370

PostPosted: Wed Feb 17, 2016 10:11 am    Post subject: Reply with quote

Not to the forum, no, but you can certainly email the file to support at notepage dot com



_________________
Tech Support
support@notepage.com
http://www.notepage.net
Back to top
View user's profile
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Wed Feb 17, 2016 10:17 am    Post subject: Reply with quote

I have sent a CSV of some of our messages table data.


Back to top
View user's profile
jburgess



Joined: 15 Feb 2016
Posts: 9
Location: Maine, USA

PostPosted: Sun Jul 10, 2016 5:35 pm    Post subject: Reply with quote

A follow-up to this issue: we worked with PageGate support via email for a while, and they ultimately determined that we need a patch to fix our issue. This has fixed our problem.

Kudos to the support team for sticking with us until the problem was resolved!


Back to top
View user's profile
Display posts from previous:   
Post new topic   Reply to topic    NotePage, Inc. Forum Index -> Integration Related Questions All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2002 phpBB Group
Theme created by Vjacheslav Trushkin