Error calling sp on db2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error calling sp on db2

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-05 : 14:13:11

I have defined a linked server and a sp residing on db2 machine.

When I call the sp, I get error at line 3:

declare @bcmcu char(12)
Set @bcmcu ='     1100281'  
Exec ('Call QGPL.get_jobs(?)', @bcmcu) AT AS400SRV_MSDASQL

Incorrect syntax near ','.

Now, when I call the same procedure and use the same code on Sequel Server 2005 it works fine.

Any ideas?

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-06 : 04:07:53

The EXEC AT is a new feature in SQL 2005 right? Are you using 2005?

http://www.mcpressonline.com/index2.php?option=com_content&do_pdf=1&id=1541

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-06 : 08:29:40

We are using both SQL Server 2005 and 2000. What I need is the right syntax for 2000. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 09:26:24

http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-06 : 15:57:03

I've been trying different approaches and even tested that my linked server AS400SRV_MSDASQL)works on SQL Server 2000. Here is my test

SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,'SELECT * from rjadevlib.f5514ar')

The result is that all records are being returned, so I assume that linked server works.

Now back to using linked server to call sp.

SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,'Call QGPL.get_allcmp')

Running this code I get error:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Call QGPL.get_allcmp'. The OLE DB provider 'MSDASQL' indicates that the object has no columns.

Again when I run call the same sp on a SQL Server 2005 I do not have any problems and records are being returned.

Exec ('Call QGPL.get_allcmp') AT AS400SRV_MSDASQL

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-06 : 16:50:38

Stored procedure calls from an SQL Server linked server (or OpenRowSet) to UDB are not supported, regardless if using the IBM UDB driver or the Microsoft OLE DB driver.

One cheesy way to get around it is to make stored procedure wrapped inside an UDF that returns a table

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-06 : 23:45:00

It seems calling a db2 parameterised sp from sql 2000 is not supported. Please refer to this thread. The last comment refers to an IBM article which provides the reason for all these errors.

http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&enterthread=y

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-07 : 08:12:30

Thank you guys for the update. At least I know not to spend time trying to get it to work.

I am able to issue a "select" using a linked server (on SQL Server 2000) but the response time is not good at all. The table I am accessing on db2 have 17 mill records. In my sp on db2 I use a logical file (access path) keyed on what I need and response time is fast. When I use the same logical file (access path) in my "select" it seems like the keyed access path is ignored and it is reading the table sequentially. Not sure if this is the case or how to get around this. Since we have SQL Server 2005, I may want to consider using it to call my sp on db2 (but this is my last resort).

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 11:02:54

Did you try to wrap your SP to a Function and call it as Linked server?

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-07 : 11:07:43

No, I have not tried that. Not sure if Function is supported either.

I have never created a function but here is my sp code:

 create procedure get_jobqty                                                      
                (in @gbmcu varchar(12))                                           
                result set 1                                                      
                language sql                                                      
                reads sql data                                                    
     
   begin                                                                          
         declare c1 scroll cursor with return for  
         select  gbmcu, gbsub, DECIMAL(SUM(gban01 + gban02 + gban03 +    
         gban04 + gban05 + gban06 + gban07 + gban08 +                    
         gban09 + gban10 + gban11 + gban12 + gban13 +                    
         gban14)/FLOAT(100.00),38,2) as sum_qty                          
         from vgiprddta/f0902lc where gbmcu = @gbmcu and                 
         gblt = 'AU'                                                     
         GROUP BY gbmcu, gbsub;                                          
         open c1;                                                        
         set result sets cursor c1;                                      
  end;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 11:11:51

quote:

Originally posted by snufse

No, I have not tried that. Not sure if Function is supported either.

I have never created a function but here is my sp code:

 create procedure get_jobqty                                                      
                (in @gbmcu varchar(12))                                           
                result set 1                                                      
                language sql                                                      
                reads sql data                                                    
     
   begin                                                                          
         declare c1 scroll cursor with return for  
         select  gbmcu, gbsub, DECIMAL(SUM(gban01 + gban02 + gban03 +    
         gban04 + gban05 + gban06 + gban07 + gban08 +                    
         gban09 + gban10 + gban11 + gban12 + gban13 +                    
         gban14)/FLOAT(100.00),38,2) as sum_qty                          
         from [vgiprddta/f0902lc] where gbmcu = @gbmcu and                 
         gblt = 'AU'                                                     
         GROUP BY gbmcu, gbsub;                                          
         open c1;                                                        
         set result sets cursor c1;                                      
  end;

whats the purpose of decimal here? no need of that

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 12:03:35

You can convert this Stored Procedure to UDF which returns results as Table. Much faster than cursor based SP.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-07 : 12:18:48

DB2 FUNCTION:

CREATE FUNCTION GET_JOBQTY (gbmcu varchar(12))
RETURNS TABLE (gbmcu varchar(12),bgsub varchar(50),
sum_qty decimal(38,2)
)
LANGUAGE SQL
READ SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
         select  gbmcu, gbsub, DECIMAL(SUM(gban01 + gban02 + gban03 +    
         gban04 + gban05 + gban06 + gban07 + gban08 +                    
         gban09 + gban10 + gban11 + gban12 + gban13 +                    
         gban14)/FLOAT(100.00),38,2) as sum_qty                          
         from vgiprddta/f0902lc where gbmcu = GET_JOBQTY.gbmcu and                 
         gblt = 'AU'                                                     
         GROUP BY gbmcu, gbsub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources