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.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-03-16 : 10:46:35
|
| I have several DTS packages running at the same time. Two of them are shifting a few million records. They both produce the following error: "...the sql server cannot obtain a lock resource..."Does anyone know how I can prevent this from happening? |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 11:23:05
|
| Can you "shift" the records in smaller batches? Or must they go across as a single transaction?You can use DECLARE @intRowsDoneSELECT @intRowsDone = 1 -- A non-zero valueSET ROWCOUNT 10000WHILE @intRowsDone > 0INSERT INTO RemoteTable SELECT * FROM LocalTableSELECT @intRowsDone = @@ROWCOUNTSET ROWCOUNT 0 -- Remove batch size restrictionto do them 10,000 at a timeKristen |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-16 : 11:50:23
|
| Thanks, I've set the Transform insert batch size to 10 000 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 11:52:40
|
| That's a darn sight easier! I learn something every day ...Kristen |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-16 : 13:12:08
|
| Wow! I have taught someone else something! For the first time ever! Cant believe it!Seriously, this is what I mean though. Using what DTS already provides is (to me) so much quicker to develop with than doing everything from scratch - like the BCP purists would have me do. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-16 : 13:15:06
|
| I aint got anything against DTS if a decent amount of effort is put in to error catching. But I don't see that happening in practice ...Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-16 : 13:56:54
|
| The "bcp purists" are telling you that for a good reason. See the other thread for more thoughts on that.bcp's been around a lot longer than DTS, and if DTS were better bcp would've been dropped long ago. That's something to think about. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-16 : 14:02:18
|
| I've been told |
|
|
|
|
|
|
Locks