SQL Server Performance Forum – Threads Archive

Proc with Linked Server does not work

Hi, 1. I have created a linked server in MS SQL Server that talks with Oracle. I have written a SQL Server proc. that reads data from SQL Server database and loads data into an Oracle database. If I execute the proc. for MS SQL Server it works/loads all the data into Oracle tables. If the developers call the proc. from Java (DAO), the procs. do get executed, but do not load all the data. They behave wierd. At times load only 1 recods, at times 15, at times 40 recods…..looks like they time out for some reasons. The code returned from the procs. is always 0 (Successful Execution). 2. I edited the proc. to load data from MS SQL Server base tables into MS SQL Server Staging tables. If the developers calls this proc. from Java (DAO), the procs. do get executed, and loads all the data. After doing 2nd step we are convinced there is some setting that the linkedserver procedure does not like resulting in a timeout. To Summarize: Executing from JAVA (using DAO, BO), a SQL Server stored proc. WITHOUT linked server WORKS.
Executing from JAVA (using DAO, BO), a SQL Server stored proc. WITH linked server DOES NOT WORK. Can you please let me know if I need to set any setting in MS SQL Server (espl. for Linked Server) to offset this problem. Thanks in advance.
Navin

Post the code used. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>