MSSQL Stored procedure : only one command executed

[Former Member]

For a MSSQL Stored Procedure, We have used this

Solution1:

Just put SET NOCOUNT OFF in the end of the stored procedure. After that run the stored procedure in the Desktop Intelligence report.

Or

If the above doesnu2019t works then please try the following solution.

Solution2:

You have to add the given parameter in .sbo file -> save the file and run the stored Procedure.

This file should be located under <drive>\Business Objects\BusinessObjects Enterprise 11.5\dataAccess\RDBMS\connectionServer\odbc\odbc.sbo

<Defaults>

<Parameter Name="Force SQLExecute">Always</Parameter>

but it still does not work

What others?

[former_member212749]

Hi Mariana SCHEER,

Declare @ASSIGNDATE1 DATETIME

Declare @ASSIGNDATE2 DATETIME

set @ASSIGNDATE1=@variable('1. (Assigned Date) From MM/DD/YYYY')

set @ASSIGNDATE2=@variable('2. (Assigned Date) To MM/DD/YYYY')

exec ModspaceRpt_VIC @ASSIGNDATE1,@ASSIGNDATE2

Above will work if you are talking abt giving a value to the prompt from the SP.

Please let me know if this works for you..

Regards

Prashant

Edited by: Prashant Sathyapalan on Sep 23, 2008 6:23 AM

[Former Member]

Hello

thank you for your answer but indicated procedure to test does not work too

the message is the same : u201CNo column and no data to fetchu201D

I return with more details :

to create a new report DesKi, I use this stored procedure

CREATE PROCEDURE dbo.test1 @matricule integer
AS
BEGIN
SET NOCOUNT OFF
delete from Temp_matr_boucle
execute absences_matricules_per_matr @matricule /* it is ok */
select MATRICULE ,NOM ,PRENOM ,CDDEP, NBR_PERIODE, NBR_JOURS , UNITE ,
       DATE_MIN,  DATE_MAX ,TYPE_ABSENCE, MATRICULE_CAR, NBR_JOURS_SERVICES 
from Temp_matr_boucle as result  /* not execute */
return
END

Launched directly from the database it work verry well.

Launched in Deski rapport, we get the same message

u201CNo column and no data to fatchu201D

The exec command works because the table "Temp_matr_boucle" is filled but is no longer running the select commande.

You have advisor to use one a two solutions (see replay from DWinkel)

1) SET NOCOUNT OFF

or

2) add the parameter:

<Defaults>

<Parameter Name="Force SQLExecute">Always</Parameter> in odbc.sbo file

Note that we use the OLEDB driver and not the odbc

We all tried both but the result is the same

thank you for your help

regards

Mariana

[Former Member]

Hi Mariana,

Could you please test the issue on FP2.4 because in the SQL query you are using Temp tables and the error message you mentioned is related to the bug Id ADAPT00704906 which is fixed in FP2.4.

when a stored procedure is run then error message no column no data to fecth appears.

Regards,

Sarbhjeet Kaur

[former_member212749]

Hi Mariana SCHEER,

Could you verify the SP.Does it require a Retun at the End ???

Regards

Prashant

[Former Member]

Hi Mariana,

Could you please test the issue using ODBC connection and also try to create a new OLEDB connection and test the issue because it will rule out the possibility of connection corruption.

Regards,

Sarbhjeet Kaur

[Former Member]

Hello

using ODBC connection it is ok now but a new OLEDB connection is failed again.

in this case I'll use an ODBC connection ,waiting to apply the patch fix.

I think this is the only solution for OLEDB

Regards,

Mariana Scheer