Re: Timeout Expired

So the timeout occurs during the bulk load operations?

If so, did you try to perform this operation using small blocks of data instead of the whole package? So that you could find the problem easier. For instance you may have some constraints (such as FK or other) on your tables that prevents some records from your text file to be inserted into the tables...

Also, did you check the SQL Error Logs to see more clues about the timeout problem if there is any?

--
Ekrem Önsoy - SQL Server MVP



"Muhammad Bilal" <MuhammadBilal@xxxxxxxxxxxxxxxxxxxxxxxxx>, iletisinde şunu yazdı, news:1154FF3C-9E3B-4EC8-9DAE-8613EBF01245@xxxxxxxxxxxxxxxx
i have tried the nolock option but the problem remain the same, i also have
tried reindexing and rebuilding of indexes but the problem remain the same.

I am working on a standalone server without a domain controller and no other
user other than me and I have tried to load the batch with the sa rights.


Regards,
Muhammad Bilal



"Ekrem Önsoy" wrote:

To change data in tables and in terms of ACID, tables or rows or pages are
locked for DML operations by SQL Server. So if your bulk operating takes
long time to finish, then this kind of time out problems are expected.

If you accept your users to be able to run SELECT queries you can use READ
UNCOMMITTED Isolation Level so that your users would be able to read data
even if those rows would be locked by some DML operations but that data to
be read would be uncommitted so it would be a Dirty reading. This is
absoluetely not recommended for sensetive data such as finance because the
results could be different after the data is committed. However if this data
which must be read would be some sort of reporting data which does not have
to be so sensetive for your situation, then you may want to use this level
of transaction isolation.

Take a look at BOL:
http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx

Or you may want to perform this job at off-work hours, then your users would
not be affected by locks. If this is a live system for 24/7, then perform it
during off-peak hours.

--
Ekrem Önsoy - SQL Server MVP



"Muhammad Bilal" <MuhammadBilal@xxxxxxxxxxxxxxxxxxxxxxxxx>, iletisinde şunu
yazdı, news:F056DFB2-6D38-4C73-AFC8-6FDCEB0ADD2E@xxxxxxxxxxxxxxxx
> Hi.
>
> SQL Server 2000 EE gives timeout expired error continously when data is
> loaded in bulk from text files through a application developed in VB 6. > As
> the application works fines when used with a blank DB or DB with > lesser
> records.
>
> Regards,
> Muhammad Bilal


.

Relevant Pages

  • Re: Generating one table with a terabyte of data
    ... Pretty much disabled the transaction log completely. ... We are using BULK INSERT ... for all those who don't know, SQL server prefers smaller BULK ... ORACLE seems to handle that 1 big file really well but with small files SQL ...
    (microsoft.public.sqlserver.dts)
  • Re: Check Linked Table Connection
    ... That will only test to make sure the connection is available. ... Run the query with a 20 second timeout. ... but I don't have any direct access to their SQL Server nor their ... I am an outside programmer that end-users contract, ...
    (comp.databases.ms-access)
  • Re: Recordset.AddNew and the recordset objects data retaining
    ... I am in an environment where bulk insert isn't an option--even though the ... underlying database is SQL server, I am using a third party OLE DB provider-- ... > insert(assuming you are using SQL server) or DTS. ...
    (microsoft.public.data.ado)
  • Re: Can Indexes effect Bulk Insert/Update Operations
    ... Columnist, SQL Server Professional ... becuase some operations need to be performed before insert/update the ... "Tom Moreau" wrote in message ... > unique/pk constraints before bulk loading the tables. ...
    (microsoft.public.sqlserver.programming)
  • Re: Increase Connection Command timeout globally
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... in Stored procs which is causing it to timeout. ... Connection and Command timeout settings are done on the client. ... I expect you'll have to visit each Command object declaration and set the ...
    (microsoft.public.dotnet.framework.adonet)
Loading