Post reply

How to increase the Timeout setting for the SQL Management Studio?

  • May 12, 2009 at 12:03 pm

    #131333

    Hi,

    Another question about TimeOut but now related to the SQL Management Studio. My goal is to refresh data on the development server from the production server (only 4 tables). Not knowing better I did that in two steps:

    1) Renamed existing tables so I can get back in case I need;

    2) Copied tables using Import Data task

    Not without issues that worked and I got my tables.

    When I started using them I noticed that only data was copyed, not tables properties like primary key, identity settings, foreign keys etc. nothing.

    I decided that if I get my primary key and identity back it would suffice for testing purposes on development server.

    To accomplish that I am going through following steps from my SQL Studio Management tool:

    - open Modify Table window

    - mark primary key field

    - set (Is Identity) to YES for primary key column

    - close Modify Table window

    - confirm changes by clicking YES

    That sequence worked for three tables out of four, I guess it built an index for primary keys.

    The fourth table has 2.5M rows and I am getting the Timeout Error message before update finishes:

    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    Looks like I need to increase the Timeout setting for the SQL Management Studio itself since I am not running any .NET applications...

    I was unable to find steps to do that - any suggestions will be appreciated

    I do not see a way to upload pictures but Remote Query Timeout for the server is set to 600 sec (and I am getting error in about 20 sec) and Execution Timeout for connection to that server is set to 0 (which I hope means that that NO timeout is set and even long commands should NOT timeout...)

    p.s. I also posted this question on MSDN's "Getting Started with SQL Server" Forum - hope cross-posting is OK - I just want more experts to see it and have a chance to respond... when someone does I will make sure it is closed everywhere if I find another decent forum to post...

    Thanks

    Naum

  • May 12, 2009 at 2:24 pm

    #993272

    With help of David Dye, MSDN Moderator the following working solution was built:

    -- 1. Create table with primary key and identity

    CREATE TABLE [dbo].[DataPoint](

    [DataPointID] [int] IDENTITY(1,1) NOT NULL,

    [SubmittedTestID] [int] NOT NULL,

    [DataPoint] [float] NULL,

    [TestNumber] [int] NOT NULL CONSTRAINT [DF_DataPoint_TestNumber_2] DEFAULT (0),

    [IsBottom] [bit] NOT NULL CONSTRAINT [DF_DataPoint_IsBottom_2] DEFAULT (0),

    CONSTRAINT [PK_DataPoint_2] PRIMARY KEY CLUSTERED

    (

    [DataPointID] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    -- 2. Allow old IDs to be copied

    SET IDENTITY_INSERT dbo.DataPoint ON

    -- 3. Perform data copy

    INSERT INTO dbo.datapoint (

    [DataPointID],

    [SubmittedTestID],

    [DataPoint],

    [TestNumber],

    [IsBottom]

    )

    SELECT * FROM sptd508.dbo.a508_datapoint

    It worked 37 sec and created the table exactly the way it should be.

    THANK YOU!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply