Field data type change - query timeout

Last Post 10 Sep 2010 05:16 AM by rm. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
river1
New Member
New Member

--
03 Sep 2010 06:56 AM

Hi,

 

    I need to change a data type of a field in SQL Server 2005.

The fiels most be changed from varchar(13) to varchar (20), but because of the amout of data it is taking to much time so i receive a timeout message from SQL Server on Query Window.

 

Can i change the timeout, so that this query (alter table ….) can run?

 

Thank you    

 

rm
New Member
New Member

--
03 Sep 2010 10:47 AM
There's no local query timeout by default, did you see blocking? Tried run it as sql job?
gunneyk
New Member
New Member

--
03 Sep 2010 05:52 PM
This is a meta data change and does not actually change the data. So the timeout must be due to the exclusive lock it needs to take on the table. Once it gets the lock it should only take les than a second regardless of the amount of data.
river1
New Member
New Member

--
09 Sep 2010 08:49 AM
When i try to execute this command:

ALTER TABLE CONTRIBUINTES_IRREGULARES ALTER COLUMN BICTB NVARCHAR(25)

I receive this error:

Msg 5074, Level 16, State 1, Line 1
The index 'IX_irreg_1' is dependent on column 'BICTB'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN BICTB failed because one or more objects access this column.


This column as one index as you masters can see throught the error.

I can not change the data of a column if it as a index?

What can i do? delete de index and then create it again? or can i just desable the index? what is better in my situaction?

Thank you
rm
New Member
New Member

--
10 Sep 2010 05:16 AM
Drop the index, change data type for the column, recreated index.
You are not authorized to post a reply.

Acceptable Use Policy