| Author |
Topic |
|
BAz
Starting Member
7 Posts |
 Posted - 2002-01-24 : 04:45:05
|
Hi All,I'm trying to copy data from one db table to another (in the same db). It's a total of about 50,000 records. I tried the following line:INSERT INTO STUDENTS_CMR (STUDNR,PCN,NAAM,INSTITUUT,LESPLAATS) SELECT DISTINCT STUDNR, MIN(PCN), MIN(NAAM), MIN(INSTITUUT), MIN(LESPLAATS) FROM IMPORTTABEL GROUP BY STUDNRThis ALLWAYS gives me a timeout from SQL. Allways. Without fail. Sure thing.Does ANYONE have any ideas? I'm kind of at a loss here... thanxBAz |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-24 : 07:41:49
|
| i think the problem is because of your Connection Query-Time Setting. you need to increase it. Go to EnterPrise Manager click on Server Properties . you get Tabs like General,Memory ....and Connection.chose the Connection Tab in the Query Time-Out and increase the value there. if you put 0 it is unlimited.this should help you out.--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God is |
 |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2002-01-24 : 09:41:49
|
| Or perhaps you could do the INSERT in batches of a 1000 !? |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-01-25 : 02:23:03
|
| I'm with nazim that it's a client-side thing, not a SQL server side problem. SQL server *will not* timeout queries itself (they may deadlock, but that's it). I've had quries run for more than two weeks, no problem (big tables!)If you're going to be doing this on a regular basis, look at creating a DTS package.Cheers-b |
|
|
|
BAz
Starting Member
7 Posts |
Posted - 2002-01-25 : 04:56:23
|
| erhmm, Nazim,I'd love to, but I'm not an administrator, so I can't change server settins. But i'm taking it up with one of the admin guys this afternoon, and we'll see what happens.This couldn't by any chance be a log file problem, could it?cheerzBAz |
|
|
|
hande
Starting Member
8 Posts |
Posted - 2002-01-25 : 05:45:47
|
| Yeahh, about two years I wandered between that guestion a lot and there other: are there extra theresas in the world space and what the heck the little asper do in the firm like this. Plays with toys? |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-01-25 : 20:03:45
|
| Baz, it looks like a *client* side issue. What tool/app/language are you issuing the command from?In ASP pages, you want to set Script.Timeout=3600 (1 hour). SQL Query Analyzer shouldn't be timing out on you.I wouldn't expect a log file issue; if the log file fills up you get a message like "log file full"; believe me, I've seen that more than enough!And hande, you are just strange.Cheers-b |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-26 : 10:50:53
|
| if you are using ADO's increase your timeout setting using either of this . Connection.CommandTimeout=60 orCommand.CommandTimeout=60HTH--------------------------------------------------------------Dont Tell God how big your Problem is , Tell the Problem how Big your God isEdited by - Nazim on 01/26/2002 10:51:42 |
|
|
|
BAz
Starting Member
7 Posts |
Posted - 2002-01-30 : 10:13:02
|
| Let see:At first I was using MS Interdev 6.0, running the stored procedure.(T-SQL)Then I moved to Enterprise Manager, using the Query Tool. Now my problem has become a little worse. I don't get timeouts, I just get nothing... But I can't believe that it's taking so long to import the file. There's about 75.000 records in the ascii file, but I've left my pc on for upto an hour, with no result. Also, I *think* I'm using bcp fast mode (Select into, no indexes on import table). I spoke to some other developers here at my company and they all brag about how fast SQL server copies 100's of 1000's of records in record times...It seems I'm the only one having the problem...I've tried a lot of variations, like building the bcp into a seperate transaction, but it's still taking so long. yeah well, what else can I say?theoretically it should work, literally it has worked for others, but for me it doesn't...BAz |
|
|
|
andre
Constraint Violating Yak Guru
259 Posts |
Posted - 2002-01-30 : 10:34:19
|
quote:
Then I moved to Enterprise Manager, using the Query Tool. Now my problem has become a little worse. I don't get timeouts, I just get nothing
Query Analyzer should at least be telling you whether the query is running and how long it's been running.quote:
Also, I *think* I'm using bcp fast mode (Select into, no indexes on import table).
SELECT INTO is not the same as BCP. What is the query you are using? |
|
|
|
|
Arrggh! Timeout!