10

I have a large script file (nearly 300MB, and feasibly bigger in the future) that I am trying to run. It has been suggested in the comments of Gulzar's answer to my question about it that I should change the script timeout to 0 (no timeout).

What is the best way to set this timeout from within the script? At the moment I have all of this at the top of the script file in the hopes that one of them does something:

sp_configure 'remote login timeout', 600
go
sp_configure 'remote query timeout', 0
go
sp_configure 'query wait', 0
go
reconfigure with override
go

However, I'm still getting the same result and I can't tell if I'm succeeding in setting the timeout because the response from sqlcmd.exe is the world's least helpful error message:

Sqlcmd: Error: Scripting error.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
tags2k
  • 82,117
  • 31
  • 79
  • 106

3 Answers3

7

Your solution - Add GO every 100 or 150 lines

http://www.red-gate.com/MessageBoard/viewtopic.php?t=8109

5
sqlcmd -t {n}

Where {n} must be a number between 0 and 65535.

Note that your question is a bit misleading since the server has no concept of a timeout and therefore you cannot set the timeout within your script.

In your context the timeout is enforced by sqlcmd

Ed Guiness
  • 34,602
  • 16
  • 110
  • 145
  • @edg: I've never heard of the -t option of sqlcmd and didn't find it in the documentation. But I checked it sqlcmd -? - and there it is! Bravo! – splattne Oct 22 '08 at 09:25
  • I guess this won't work. sqlcmd.exe without setting -t explicitly means "no timeout" – Tomalak Oct 22 '08 at 09:44
  • 1
    Yup. Technically the correct answer to my question, but didn't help the core problem. Thanks anyway! – tags2k Oct 22 '08 at 10:48
  • 4
    You should unaccept it then so others see your question as not-yet-solved. – Tomalak Oct 22 '08 at 11:39
4

I think there is no concept of timeout within a SQL script on SQL Server. You have to set the timeout in the calling layer / client.

According to this MSDN article you could try to increase the timeout this way:

exec sp_configure 'remote query timeout', 0 
go 
reconfigure with override 
go

"Use the remote query timeout option to specify how long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine."

P.S.: By 300 MB you mean the resulting file is 300 MB? I don't hope that the script file itself is 300 MB. That would be a world record. ;-)

splattne
  • 102,760
  • 52
  • 202
  • 249
  • 1
    Yes, the SQL script itself is ~300MB. I doubt it's a world record as it's only 1.4 million rows of a very simple table! – tags2k Oct 22 '08 at 09:15
  • @tags2k oh, it's used to insert data inside "INSERT" statements... I'm sure there's a reason to do it that way, but couldn't you get the data in a textfile and import it via a DTS-package or somthing similar? – splattne Oct 22 '08 at 09:24
  • Each line needs to be checked via my API, and while I'm sure I could replicate all the checks via a DTS package, for the time and stress required I might as well just divide the SQL script I already have. – tags2k Oct 22 '08 at 10:55