SQL Server timeout

[hellilyntax]

hi guys,
before i come to my problem, ill let you know my app architecture.
i have a vb program to update my sql server database which is located at hosting company.
the program will transfer every rows in Excel on local pc to MSSQL table on server.

the problem is, it takes too long to complete the transaction because it involves thousands of rows (up to 10000). when i try, it only can execute 800 rows and the connection will break.

do you have any idea to overcome this...
is there anything to do with
1. connection speed
2. sql statement
3. architecture itself
4. is it possible to execute 10000 rows over the net?
5. do i have to have the SQL Server on local so that the journey can be faster?

please give me some idea....

[RobDog888]

Easiest and most stable way to do an import of Excel to SQL Server is to use DTS Import. You can designate the Excel file and destination table and save the package. Then execute it manually or via a Job or code etc.

[hellilyntax]

thanks dude,
ill look at DTS first and come back later...

[techgnome]

DTS is only going to be effective if it's run on the server (as it's designed to).....

Apart from that.... when you create your command object, you can also set the CommandTimeout property. By default it is about 30 seconds (If I remember right). You can extend it by setting it to a new value (in number of seconds)... we use 300 (which is 5 minutes) which seems to work for us.

-tg

OG Tay sez: OW! Not the face! Not the face!

[RobDog888]

Good suggestion TG as I see his db is located at hosting company so increasing the timeout property is the better way to go.

[RobDog888]

Post #4.

[hellilyntax]

i now try with new value...3000 is that enough for 10000 rows?
command timeout means attemp to execute one sql right?
am i too greedy?

[RobDog888]

The unit is seconds and there is no formula for a row per second value. It varies as according to connection speed, traffic, server loads, etc.

[hellilyntax]

when i put 3000sec does it means server will wait for any command for 3000sec. if no command wthin that period, it will break the connection.
-or-
the server only provide 3000sec to complete all command.after that it will terminate the connection.

which one is right? correct me if im wrong....

fyi, currently the process took about 1sec per row and reach 1000 rows with constant speed...

damn, it breaks at 1133 rows....i want to sleep right now...
if you have othe idea...help me please...