SQL 2005 - problem to remotely execute stored procedures via JDBC

Question

Hi all,

I'm currently experiencing some weird behaviour while trying to execute a stored procedure on SQL Server 2005. The execution should be triggered by SAP PI system, which is using JDBC driver to access SQL Server. If executing stored procedure directly via SQL Server Management Studio or some other database tool like DBVisualizer everything works fine.

Via SAP PI I always get an error (synchronous timeout after 3 minutes, which is default timeout for synchronous calls on SAP PI).

A colleague and I had a look at the trace files and there are several errors, which start with error 156 (incorrect syntax near ...) and later error 3621 (A statement has been terminated). But there is no syntax problem, the procedures works if executed directly.

The other funny thing is, if I reroute the execution via TCPGateway over my laptop, the execution works fine. The call is still coming from SAP PI, but SAP PI sends the request to my laptop, which just routes it to SQL Server and the execution works fine.

Does anybody have an idea, what could possibly be the reason for this behaviour?

By the way, the stored procedure itself isn't very complicated. It truncates a table, then makes an insert on this table with the contents of a view, which has the exact same structure.

 

Hope for help

Ricrahl

Answer 1

hi,

Use the SQL Profiler to check what SQL statements are used and whether the error is generated by the server or the driver:

http://msdn.microsoft.com/en-us/library/ms181091.aspx

Or use a server-side trace to get the same information:

http://msdn.microsoft.com/en-us/library/ms191443%28v=SQL.105%29.aspx

btw, this is a German speaking forum.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

Hallo,

sorry für en englischen Post, ich bin von den SAP Foren englisch gewohnt und hatte hier auch einige gesehen.

Ich habe leider keinen Benutzer mit dem ich selbst traces ausführen kann, habe aber gestern mit einem Kollegen zusammen folgende Traces erzeugt:

Hier hat der Aufruf funktioniert:

  <Event id="11" name="RPC:Starting">
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11542590</Column>
   <Column id="2" name="BinaryData">00000000070000002200730070005F0063007500720073006F007200700072006500700065007800650063001400000003000600380169006E007400000000001400000003000600380169006E007400000000002600000082001C00E7406E0076006100720063006800610072002800340030003000300029007A00000082001C00E7206E007600610072006300680061007200280034003000300030002900500000004500580045004300200045006E006500720067007900430072006500640069007400530074006100670069006E0067002E00640062006F002E00520075006E00500044005600690065007700430052001400000003000600380069006E007400100000001400000003000600380069006E007400012000001400000003000600380169006E00740000000000</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">60</Column>
   <Column id="14" name="StartTime">2011-03-01T14:15:56.26+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
   <Column id="1" name="TextData">declare @p1 int
set @p1=0
declare @p2 int
set @p2=0
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'EXEC EnergyCreditStaging.dbo.RunPDViewCR',16,8193,@p7 output
select @p1, @p2, @p7</Column>
  </Event>
  <Event id="70" name="CursorPrepare">
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11544870</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">60</Column>
   <Column id="14" name="StartTime">2011-03-01T14:17:36.787+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
  </Event>
  <Event id="10" name="RPC:Completed">
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="15" name="EndTime">2011-03-01T14:17:36.787+01:00</Column>
   <Column id="31" name="Error">0</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11544871</Column>
   <Column id="2" name="BinaryData">00000000080000002200730070005F0063007500720073006F007200700072006500700065007800650063001400000003000600380169006E007400010000001400000003000600380169006E007400000000002600000082001C00E7406E0076006100720063006800610072002800340030003000300029007A00000082001C00E7206E007600610072006300680061007200280034003000300030002900500000004500580045004300200045006E006500720067007900430072006500640069007400530074006100670069006E0067002E00640062006F002E00520075006E00500044005600690065007700430052001400000003000600380069006E007400100000001400000003000600380069006E007400012000001400000003000600380169006E007400000000001400000003000600380469006E00740000000000</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">60</Column>
   <Column id="14" name="StartTime">2011-03-01T14:15:56.26+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
   <Column id="1" name="TextData">declare @p1 int
set @p1=1
declare @p2 int
set @p2=0
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'EXEC EnergyCreditStaging.dbo.RunPDViewCR',16,8193,@p7 output
select @p1, @p2, @p7</Column>
  </Event>

Und hier nicht:

  <Event id="11" name="RPC:Starting">
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11577052</Column>
   <Column id="2" name="BinaryData">00000000070000002200730070005F0063007500720073006F007200700072006500700065007800650063001400000003000600380169006E007400000000001400000003000600380169006E007400000000002600000082001C00E7406E0076006100720063006800610072002800340030003000300029007A00000082001C00E7206E007600610072006300680061007200280034003000300030002900500000004500580045004300200045006E006500720067007900430072006500640069007400530074006100670069006E0067002E00640062006F002E00520075006E00500044005600690065007700430052001400000003000600380069006E007400100000001400000003000600380069006E007400012000001400000003000600380169006E00740000000000</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">59</Column>
   <Column id="14" name="StartTime">2011-03-01T14:44:51.087+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
   <Column id="1" name="TextData">declare @p1 int
set @p1=0
declare @p2 int
set @p2=0
declare @p7 int
set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'EXEC EnergyCreditStaging.dbo.RunPDViewCR',16,8193,@p7 output
select @p1, @p2, @p7</Column>
  </Event>
  <Event id="33" name="Exception">
   <Column id="31" name="Error">156</Column>
   <Column id="4" name="TransactionID">414062997</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">59</Column>
   <Column id="20" name="Severity">15</Column>
   <Column id="64" name="SessionLoginName">SRVPIRGC</Column>
   <Column id="1" name="TextData">Error: 156, Severity: 15, State: 1</Column>
   <Column id="41" name="LoginSid">80426FB8006DAD49BB898BBEA62A990F</Column>
   <Column id="49" name="RequestID">0</Column>
   <Column id="14" name="StartTime">2011-03-01T14:44:51.087+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
   <Column id="30" name="State">1</Column>
   <Column id="50" name="XactSequence">0</Column>
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11577053</Column>
  </Event>
  <Event id="162" name="User Error Message">
   <Column id="31" name="Error">156</Column>
   <Column id="4" name="TransactionID">414062997</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">59</Column>
   <Column id="20" name="Severity">15</Column>
   <Column id="64" name="SessionLoginName">SRVPIRGC</Column>
   <Column id="1" name="TextData">Incorrect syntax near the keyword 'Delete'.</Column>
   <Column id="41" name="LoginSid">80426FB8006DAD49BB898BBEA62A990F</Column>
   <Column id="49" name="RequestID">0</Column>
   <Column id="14" name="StartTime">2011-03-01T14:44:51.087+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
   <Column id="30" name="State">1</Column>
   <Column id="50" name="XactSequence">0</Column>
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11577054</Column>
  </Event>
  <Event id="33" name="Exception">
   <Column id="31" name="Error">16954</Column>
   <Column id="4" name="TransactionID">414062997</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">59</Column>
   <Column id="20" name="Severity">10</Column>
   <Column id="64" name="SessionLoginName">SRVPIRGC</Column>
   <Column id="1" name="TextData">Error: 16954, Severity: 10, State: 1</Column>
   <Column id="41" name="LoginSid">80426FB8006DAD49BB898BBEA62A990F</Column>
   <Column id="49" name="RequestID">0</Column>
   <Column id="14" name="StartTime">2011-03-01T14:44:51.087+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
   <Column id="30" name="State">1</Column>
   <Column id="50" name="XactSequence">0</Column>
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11577055</Column>
  </Event>
  <Event id="162" name="User Error Message">
   <Column id="31" name="Error">16954</Column>
   <Column id="4" name="TransactionID">414062997</Column>
   <Column id="8" name="HostName">r0445</Column>
   <Column id="12" name="SPID">59</Column>
   <Column id="20" name="Severity">10</Column>
   <Column id="64" name="SessionLoginName">SRVPIRGC</Column>
   <Column id="1" name="TextData">Executing SQL directly; no cursor.</Column>
   <Column id="41" name="LoginSid">80426FB8006DAD49BB898BBEA62A990F</Column>
   <Column id="49" name="RequestID">0</Column>
   <Column id="14" name="StartTime">2011-03-01T14:44:51.087+01:00</Column>
   <Column id="26" name="ServerName">s060a7137</Column>
   <Column id="30" name="State">1</Column>
   <Column id="50" name="XactSequence">0</Column>
   <Column id="3" name="DatabaseID">9</Column>
   <Column id="11" name="LoginName">SRVPIRGC</Column>
   <Column id="35" name="DatabaseName">EnergyCreditStaging</Column>
   <Column id="51" name="EventSequence">11577056</Column>
  </Event>

Vielleicht wird ja aus diesem Trace jemand schlauer als ich. Der Unterschied zwischen diesen beiden Aufrufen ist nur, dass der obere über ein TCPGateway weitergeleitet wurde, als der gleiche JDBC Treiber und auch der gleiche User benutzt wurden. Meine Vermutung ist, dass aus irgendeinem Grund vielleicht das Timing ein Problem bereitet.

Beste Grüße

Ricrahl

Answer 3

Aufgrund folgender Zeile:

 <Column id="1" name="TextData">Incorrect syntax near the keyword 'Delete'.</Column>

<Glasskugel>

Der funktionierende Fall unterscheided sich vom Fehlerfall durch den Wert der Variablen @p1 0 statt 1.

</Glasskugel>

Teste im SSMS:

 

DECLARE
 @p1 INT
 ;
DECLARE
 @p2 INT
 ;
DECLARE
 @p7 INT
 ;

SET
 @p1 = 0 ;
SET
 @p2 = 0 ;
SET
 @p7 = 0 ;

EXEC
 sp_cursorprepexec 

 @p1 OUTPUT
, 

 @p2 OUTPUT
, 

 NULL
, 

 N'EXEC EnergyCreditStaging.dbo.RunPDViewCR'
, 

 16, 

 8193, 

 @p7 OUTPUT
 ;

SELECT
 @p1 , @p2 , @p7 ;

Wobei ich mich frage ob es bei sp_cursorprepexec eine Rolle spielt:

http://technet.microsoft.com/de-de/library/ff946033%28SQL.100%29.aspx

 

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 4

Hi,

der Unterschied ist mir auch aufgefallen, aber ich rufe noch weitere SPs auf und dort wird p1 auch auf 0 gesetzt.

Beste Grüße

Ricrahl

Answer 5

Hallo Ricrahl,
könnte es evtl. an den Session-Einstellungen liegen?
Die Clients setzen in der Regel vorab schon mal einige Statements ab, um die ANSI-Einstellungen sicherzustellen.
Du findest diese z. B. im Management Studio unter Optionen->Abfrageausführung->SQL Server->ANSI

Nur eine Idee...

Einen schönen Tag noch,
Christoph

---

Microsoft SQL Server MVP
http://www.insidesql.org/blogs/cmu