| Author |
Message
|
| mqjeff |
 Posted: Wed Apr 01, 2015 8:41 am Post subject: |
 |
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
That is more clearly an issue with the dataRow variable than with the inputs to the proc.
Can you verify the contents of the dataRow variable after the second call to the proc returns? |
|
| Back to top |
|
 |
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 9:03 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
It returns empty after the first call.
After a timeout passes, it is populated as expected. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 01, 2015 9:22 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Dave Ziegler wrote: |
It returns empty after the first call.
After a timeout passes, it is populated as expected. |
Take an odbc trace. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 10:01 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
Did that as well and nothing conclusive came of it.
I'll ride out the PMR process and report back. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Apr 01, 2015 10:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Dave Ziegler wrote: |
| Did that as well and nothing conclusive came of it. |
You should be able to see the parameters being passed into the procedure, and the data being returned back, at least at some level.
This would tell you if the data coming back is different in the two calls, or not. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 10:26 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
| The most frustrating part of this is that executing the same SQL via Enterprise Manager works over and over and over. Only through Broker does this occur. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 2:40 pm Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
So... IIB is clearly caching something here. I made a request, it worked, made another one and it failed as per usual. I deleted the ODBC connection in the ODBC applet and fired off more requests. They kept getting processed and kept failing for about a minute, but not because the connection was missing. Then finally the broker reported connection issues.
Still working on getting that trace from my DBA, but we can see the two dates being passed in just fine the first time, then on subsequent calls they get NULL'ed out by something, apparently in the ODBC layer, and then my query fails to return data. Which is expected, because passing NULL date values yields 0 rows.
I still feel like IIB is hanging onto something for about a minute, or something in the SQL Server ODBC driver is. Either way, the input doesn't change but the hand off to SQL Server after the first attempt is mangling my date variables. Which is odd too because they are being passed as NVARCHARs just like the other inputs. I don't know why these two are being singled out. |
|
| Back to top |
|
|
| Dave Ziegler |
| Posted: Wed Apr 01, 2015 3:39 pm Post subject: |
|
|
Centurion
Joined: 15 Apr 2014
Posts: 118
|
I changed the driver to SQL Server Native Client 10.0 and now it works (was just using SQL Server before).
I thought it had to be a driver issue, but my sysadmin didn't think it would matter. Turns out it does  |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Apr 02, 2015 2:24 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi Dave,
Good news! We would recommend using:
SQL Native Client 10.0 for SQL Server 2008.
SQL Native Client 11.0 for SQL Server 2012.
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
