Is it a bug?

Hi,

I'm running SQL 2K5 with SP1.

Whenever I try to run an import from Oracle linked server to my sql server (of course, there are no problems with the import), and if I try to expand any of the folders like Tables, Programmability -> Stored procedures, functions...............I'm not able to see any tables and SPs etc.

It times out and displays error message "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222).

The linked server (Oracle) import takes 30 to 40 mins and each time it only touches a single table and I don't think this should cause that kind of error but it is.

I'm running this import from Management Studio.

Anybody has any thoughts?

Thanks,

Siva.

SQL Server Management Studio needs some locks to provide the listings. Certain types of data import processes can block other access to the database.

If you can use another method to import data that doesn't lock so much, you can eliminate this problem. I don't know any specific solutions, unfortunately.

-Ryan / Kardax

|||

Hi Ryan,

Thanks for the info.

I did couple of tests on this. It looks like this problem is happening only when importing data but not when querying the linked server inspite of querying also takes lot of time.

Then I tried this import using SSIS and I'm not getting those problems mentioned earlier.

But to be frank, getting data from linked server is very straight forward (when you don't have to do any manipulations with data) and easy compared to SSIS (in my case).

Hopefully, Microsoft will look into this.

Thanks,
Siva.

|||Yes, but linked servers were primarily designed to allow you to offload a query to a remote destination, have it execute it, and then get the results back. It wasn't designed as a data import mechanism. SSIS was designed as a data import mechanism so it is optimized for that type of activity with as low of an overhead as possible.