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.

Is having a trigger that inserts a row in Table A, when a row in same table is inserted by

I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.

Thanks

Hey,

I would prefer stored procedure; the reason is I have no control over triggers, so it makes it easier from a maintenance perspective on me, which is what I think you may also be under that restraint? I don't know how much slowing down it would do; I would think a lot of that depends on volume of data as well.

If you are inserting the main data using a stored procedure, then that stored procedure can do two inserts. You can also group them in a transaction so if one fails, both statements are rolled back for safety purposes.

Microsoft includes a lot of things: they include xp_cmdshell which most DBA's disable because of the power of it. So they will include things even though they may not be the best to implement...

|||

Actually, your DBA is correct. I always recommend, if possible, opting for stored procedures as opposed to triggers. The trigger will save you time if you are inserting rows into the table from many different sources in many different ways. But, if the only point of entry is a stored procedure, there's no reason not to place it in the sp. The farther back in SQL Server version you go, the less reliable triggers seem to get - and, yes, they can place locks on tables. Microsoft included them because they have their place - but it is a mistake to use them as substitutes for logical flow.

This shouldn't be a lot of extra work on you. If you aren't using a stored procedure already, they are much faster and far more secure than "on page" SQL. If you have insert statements in various parts of your application inserting to the same table, then you should be encapsulating them into a stored procedure anyway! Your DBA's job is to protect the efficiency and cleanliness of your database. Adding triggers unnecessarily affects both.