I am trying to update a MS SQL Server database with a script that contains a lot of GO statements. On my development machine this works fine and the script completes in about 2,5 minutes and does what it is supposed to do, but on my laptop I get a timeout. It happens during a part of the script (a batch?) that takes 29 seconds on my development machine, so my guess is that this takes more than 60 seconds on my laptop. The table I am working with in that part of the script has about 600 000 rows.
But I have set 1800 seconds timeout in code, and 1800 in the connection string (Connection Timeout=1800). The number 1800 is not important, I have just raised it to a huge number to see if that helped the situation. It looks to me like the timeout for a single batch is still 60 seconds somehow? Here is the code that executes the script:
SqlConnection connection = new SqlConnection(_login.ConnectionString);
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.StatementTimeout = 1800;
server.ConnectionContext.ExecuteNonQuery(script);
Here is the error I get:
EXECUTE_ERROR:An exception occurred while executing a Transact-SQL statement or batch.
EXECUTE_ERROR:Timeout uppstod innan åtgärden slutfördes eller servern svarar inte.
The second part is Swedish, and roughly translates to : Timeout occurred before the action was completed or the server is not responding.
Any ideas?
Edit :
Tried the following code, but the result is the same...
Server server = new Server();
server.ConnectionContext.ConnectionString = _login.ConnectionString;
server.ConnectionContext.Connect();
server.ConnectionContext.StatementTimeout = 1800;
server.ConnectionContext.ExecuteNonQuery(script);
Edit 2:
Ok, now I at least know where Sql Server has problems. I don't know why, because this SQL works fine on my development machine, and executes in about 5 seconds. On my laptop, with a FASTER hard disk, it pretty much hangs. I have not had the patience to see how long it took to complete it for the laptop. I know that the original question was about timeouts, and I still need to solve that, but this is more important. How can this batch take so much more time on my laptop? Any ideas?
DECLARE @TaskId Int
DECLARE @CreatedById Int
DECLARE @CreatedAt DateTime2
DECLARE @AssignedToId int
DECLARE @AssignedById int
DECLARE @AssignedAt DateTime2
DECLARE @Count Int
SELECT @Count=1
DECLARE TaskLog_Cursor CURSOR FOR
SELECT Id, CreatedById, CreatedAt, AssignedToId, AssignedById, AssignedAt FROM Task
OPEN TaskLog_Cursor;
FETCH NEXT FROM TaskLog_Cursor INTO @TaskId, @CreatedById, @CreatedAt, @AssignedToId, @AssignedById, @AssignedAt
WHILE @@FETCH_STATUS = 0
BEGIN
-- Created by logs
INSERT INTO TaskLog (Id, TaskId, CreatedById, CreatedAt) VALUES(@Count, @TaskId, @CreatedById, @CreatedAt)
INSERT INTO TaskLogDetail (Id, TaskLogId, TaskLogActionId) VALUES (@Count, @Count, 0)
SELECT @Count=@Count + 1
-- Assigned to logs
if (not @AssignedToId is null AND not @AssignedById is null AND not @AssignedAt is null)
begin
INSERT INTO TaskLog (Id, TaskId, CreatedById, CreatedAt) VALUES(@Count, @TaskId, @AssignedById, @AssignedAt)
INSERT INTO TaskLogDetail (Id, TaskLogId, TaskLogActionId, FromId, ToId) VALUES (@Count, @Count, 8, null, @AssignedToId)
SELECT @Count=@Count + 1
end
FETCH NEXT FROM TaskLog_Cursor INTO @TaskId, @CreatedById, @CreatedAt, @AssignedToId, @AssignedById, @AssignedAt
END;
CLOSE TaskLog_Cursor;
DEALLOCATE TaskLog_Cursor;
GO