1

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
Hultan
  • 119
  • 1
  • 10
  • Are you updating a local SQL Server database (on your laptop) or remote? – Alex Jun 12 '16 at 09:26
  • I am updating a local SQL Server on both occasions... – Hultan Jun 12 '16 at 09:27
  • I have searched around and the only thing that comes up are issues like this e.g. http://stackoverflow.com/questions/2784247/smo-connectioncontext-statementtimeout-setting-is-ignored . Consider adding a more complete example to your question. – Alex Jun 12 '16 at 10:02
  • Thanks, I'll see if that link leads to anything further... – Hultan Jun 12 '16 at 11:03
  • I'm not in a position to test, but I'd try explicitly creating the `SqlConnection` object, setting the `StatementTimeout`, then explicitly creating the `ServerConnection` object using the `SqlConnection` you just created. – Eris Jun 12 '16 at 18:47
  • Thanks for the idea, I'll try that. I have also tried working with the usual SQL connection /sqlcommand but have failed to even get that to work. Starting to get worried about bigger problems with my new laptop... :-( – Hultan Jun 12 '16 at 18:51
  • Have you tried running the script directly from SSMS? – Alex Jun 12 '16 at 23:13
  • Well, the script works perfectly on my development machine.... So the script works... – Hultan Jun 13 '16 at 04:43
  • Thanks Alex. when I tried the script manually in SSMS I found where the problem was. Still no idea why though... – Hultan Jun 13 '16 at 17:59

0 Answers0