none
PowerPivote Call store proc

    Question

  • Hi,

    I try to call a store proc from the AS/400 with OLEDB/ODBC Connection through the Assistant Importation table.

    The SQL Instruction is : CALL STOREPROC1

    Hi have this  error :

    L'instruction SQL n'est pas valide. Aucune colonne n'y a été détectée.

    Can you help me please !

    Thank

    Wednesday, July 17, 2013 7:15 PM

Answers

All replies

  • can you try "EXEC STOREPROC1" instead of "CALL STOREPROC1"?

    - www.pmOne.com -

    Wednesday, July 17, 2013 8:25 PM
  • Check the stored procedure directly. Looks like there might be some invalid columns in the procedure. A column might be misspelled or is missing.
    Wednesday, July 17, 2013 10:37 PM
  • Hi,

    This is my sample procedure :

    When I Execute the procedure from the import wizard table from Button design, all is ok, no error and it return the records. When I press ok to return from first screen, I click Terminate  button, I always have thi error :

    L'instruction SQL n'est pas valide. Aucune colonne n'y a été détectée.

    What it my probleme ?

    /*********************************************************************/

    --  Generate SQL                                     
    --  Version:                    V5R4M0 060210        
    --  Generated on:               10/03/16 21:44:52    
    --  Relational Database:        S06F9024             
    --  Standards Option:           DB2 UDB iSeries      
                                                         
    SET PATH "QSYS","QSYS2","STMXSQL" ;                  
                                                         
    /* DROP   PROCEDURE STMXSQL.WKDBOARDMP; */           

     DBGVIEW = *SOURCE,                              
     ALWCPYDTA = *OPTIMIZE ,                         
     COMMIT = *NONE ,                                
     DECRESULT = (31, 31, 00) ,                      
     DFTRDBCOL = *NONE ,                             
     DYNDFTCOL = *NO ,                               
     DYNUSRPRF = *USER ,                             
     SRTSEQ = *HEX                                   
     BEGIN                                           
                                                     
     /* CURSEUR DE RETOUR  (TOUS) */                 
                                                     
     DECLARE C1 CURSOR WITH RETURN FOR               
     SELECT *                                        
     FROM RNICHOLLS.ARSTAT WHERE STANN = 2012;       
                                                     
     OPEN C1 ;                                       
     RETURN ;                                        
                                                     
     END                                                                                                   
                                                         
    CREATE PROCEDURE STMXSQL.WKDBOARDMP ()               
     DYNAMIC RESULT SETS 5555                            
     LANGUAGE SQL                                        
     SPECIFIC STMXSQL.WKDBOARDMP                         
     NOT DETERMINISTIC                                   
     MODIFIES SQL DATA                                   
     CALLED ON NULL INPUT                                
     SET OPTION  ALWBLK = *ALLREAD ,                     

    Thursday, July 18, 2013 12:44 PM
  • Excuse me,

    This is the good code of my procedure :

    --  Generate SQL                                       
    --  Version:                    V5R4M0 060210          
    --  Generated on:               10/03/16 21:44:52      
    --  Relational Database:        S06F9024               
    --  Standards Option:           DB2 UDB iSeries        
                                                           
    SET PATH "QSYS","QSYS2","STMXSQL" ;                    
                                                           
    /* DROP   PROCEDURE STMXSQL.WKDBOARDMP; */     

    CREATE PROCEDURE STMXSQL.WKDBOARDMP ()                 
     DYNAMIC RESULT SETS 5555                              
     LANGUAGE SQL                                          
     SPECIFIC STMXSQL.WKDBOARDMP                           
     NOT DETERMINISTIC                                     
     MODIFIES SQL DATA                                     
     CALLED ON NULL INPUT                                  
     SET OPTION  ALWBLK = *ALLREAD ,            

     DBGVIEW = *SOURCE,                           
     ALWCPYDTA = *OPTIMIZE ,                      
     COMMIT = *NONE ,                             
     DECRESULT = (31, 31, 00) ,                   
     DFTRDBCOL = *NONE ,                          
     DYNDFTCOL = *NO ,                            
     DYNUSRPRF = *USER ,                          
     SRTSEQ = *HEX                                
     BEGIN                                        
                                                  
     /* CURSEUR DE RETOUR  (TOUS) */              
                                                  
     DECLARE C1 CURSOR WITH RETURN FOR            
     SELECT *                                     
     FROM RNICHOLLS.ARSTAT WHERE STANN = 2012;    
                                                  
     OPEN C1 ;                                    
     RETURN ;                                     
                                                  
     END                                                    
                                                            
                                                            
         

    Thursday, July 18, 2013 12:47 PM
  • Hi TomFaf08,

    Please refer to the following article about how to call a Stored procedure in PowerPivot:
    http://www.powerpivotpro.com/2010/06/calling-a-stored-procedure-in-powerpivot/

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, July 19, 2013 8:04 AM
  • Hi Elvis,

    I Read your link, but in a DB2 Storproc, I can't set this property SET NOCOUNT OFF, I search to find an equivalence and I find nothing. Do you have a suggestion ?

    Think !

    Tommy

    Friday, July 19, 2013 2:12 PM
  • Hi TomFaf08,

    Please refer to the following article about how to call a Stored procedure in PowerPivot:
    http://www.powerpivotpro.com/2010/06/calling-a-stored-procedure-in-powerpivot/

    Best Regards,


    Elvis Long
    TechNet Community Support

    Elvis, do you have an equivalent for Tom?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 22, 2013 6:34 PM
  • Tom,
    Is this still an issue?
    Thanks!

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, September 16, 2013 12:25 AM