cancel
Showing results for 
Search instead for 
Did you mean: 

com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set

Former Member
0 Kudos

Hi,

  I have a JDBC to Proxy scenario where PI is executing a stored procedure to fetch data from a SQL system. Even though there is data to be fetched, the PI sender JDBC Channel is still throwing the error "The statement did not return a result set".

Does anyone faced this issue and resolved it successfully.

Thanks,

Sarat

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

This reply is too late for you, but may help someone else who is now facing asimilar issue.

I am using Spring, Java and SQL Server 2012.

The task was to migrate from Weblogic 9.2 + Sybase ASE to Tomcat 7.0 and SQL Server 2012.

The stored procedures returned results in SQL server Mgmt Studio but not while using Java.

Solution :

Write   SET NOCOUNT ON just after the BEGIN of the stored procedure

zameerf
Contributor
0 Kudos

Hi Sarat,

Did you check with DB team for any solutions at Database side?

Refer the below threads if you haven't read earlier.

http://scn.sap.com/thread/1827939

http://scn.sap.com/thread/1097990

former_member181985
Active Contributor
0 Kudos

have you tested stored procedure directly and checked the result using SQL editor

Former Member
0 Kudos

Yes I have tested using SQL Server Management Studio and it is working fine. The stored proc statement in PI Sender channel was also executed as it is in the tool and it is working absolutely fine.

Thanks,

Sarat

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Please check the following.

a) Check the stored procedure statement has any typo  in the communication channel

b)  Please check this thread  http://scn.sap.com/thread/1791705

The jdbc drivers some of them  for MS SQL Server might not deployed properly in the PI and so you get this error.

Former Member
0 Kudos

Baskar,

  Is there any possibility that we can insert data into database but not retrieve data from database due to some missing JDBC drivers ? That is what is exactly happening in my case.

Any thoughts on this ?

Thanks in advance.

Regards,

Sarat

baskar_gopalakrishnan2
Active Contributor
0 Kudos

What is your PI version?  I have installed oracle jdbc drivers in PI 7.1 before and not with SQL Server. The thread I refered shows the similar situation.  I would first recommend you to see the deployment of jdbc drivers for SQL Server. Take help from BASIS.

Please see the  SAP note 639702.

Also this link

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/8000cffc-6b92-2d10-3493-f2ac13992...

Former Member
0 Kudos

Hi Baskar,

Thanks for your inputs. I checked with Basis Team and everything looks fine.

Let me throw some more light on the scenario.

Here is our requirement -

1. PI writes data into Database with status as "A".

2. There will be a job running in Database that updates the status to "B".

3. PI sender channel will execute the stored procedure. This is in the "Query Execute Statement" inside the channel.

4. The stored procedure will update the status to "C" from "B".

5. Then this stored procedure will select the required fields and passes it to PI.

6. Now PI will update the status to "D" from "C". This is in the "Update SQL Statement" inside the channel.

This is the entire process.

Please let me know yur thoughts on this.

Thanks,

Sarat

baskar_gopalakrishnan2
Active Contributor
0 Kudos

There are many interdependent interfaces/jobs around this interface. So if one step fails, it will be slightly hard to debug the cause of the error.

1) This step is carrried out through another PI interface. Is that right?

2) What kind of job it is?  How frequent you run this job. This job is primarily input for the stored procedure to execute. Assume that your sender channel is always trying to execute (before the database job and after the job).. If your database job is not executed then the status would be A. If your stored procedure is looking for the status B to update to C. Since there are no records of B, you get no resultset message from the sender channel. This answers your question

Suggestion: You might create a script where you execute database job and then start the jdbc channels to execute your SP. This might be helpful.

You can control the communication channels externally. You can see Williams blog for this..

http://scn.sap.com/community/pi-and-soa-middleware/blog/2007/05/04/control-communication-channels-ex...

former_member181985
Active Contributor
0 Kudos

Hi Sarat,

Are you using the same Database user (what is used in PI) when checking stored procedure call from native SQL editor? The problem could be some transaction still open from SQL editor by end user and hence the data is not reflecting when executed externally using PI JDBC sender channel with Stored procedure call. Remember, in such situations the stored procedure will work from SQL editor and data will be reflected. I faced this issue in my previous projects. Ask your DB adminstrator if such instances still open.

Praveen

Former Member
0 Kudos

Hi Praveen,

  I am not using the same user to check through SQL editor. Furthermore the data is being written into database successfully with the user used by PI. This same user is being used by PI even to read the data from database.

Thanks,

Sarat

Former Member
0 Kudos

Hi Baskar,

  1. Yes you are correct. The first piece i.e. insertion into the database happens by another PI Interface.

  2. PI communication channel executes the stored procedure every 5 min and gets the required data. The stored proc will be modified to return empty resultset when the select query doesn't have anything to return. That ensures our PI communication channel not to be errored out. But in my case there is data that has to be pulled out through the select statement in the stored proc but still PI communication channel is erroring out with the issue "com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set" . 

Any thoughts on this ???

Thanks,

Sarat

former_member181985
Active Contributor
0 Kudos

I understand Sarat, inserting data into database from PI will work. The only issue is happening in your case is while reading db tables/stored procedure from external client using API wrappers (in this case PI JDBC sender channel - Java API). So some transaction is not commited.

Can you perform these

  1. check SQL db stored procedure result with the same user ID what you are using in PI channel?
  2. use some programming API (e.g., java) to execute the SP externally and see the result with two user IDS separately? check this link: http://www.javadb.com/connect-to-database-and-call-stored-procedure

- Praveen