How to increase CommandTimeout for SSMStudio? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

How to increase CommandTimeout for SSMStudio?

Author

Topic

jzurbo77
Starting Member

21 Posts

Posted - 2009-05-12 : 11:58:12

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) Copying tables using Import Data task

Not without issues that woked 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 set 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 up;oad 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...)

Thanks
Naum
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 othe decent forum to post...

jzurbo77
Starting Member

21 Posts

Posted - 2009-05-12 : 16:22:36

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources