SQL ALTER COLUMN causes a Possible schema corruption.

SQL ALTER COLUMN causes a Possible schema corruption.

• chaser7016

  The communication failure simply indicates that the connection from the client to the server was terminated by the server due to some fatal error. It can happen due to network outage also. But the main thing is that you should first check the SQL Server error log on the server for more details. If you see any stack dumps in the error log then you may want to actually contact CSS. Additionally, you may also want to look at the NT event log for any hardware failures or system errors that can affect SQL Server. And please run a DBCC CHECKDB to start with. Lastly, there is a new disaster and recovery forum. So if you cannot find the cause of the problem then please start a thread there with more details on the problem (error messages from SQL error log, NT event log etc).
  
  

• CHTHONIC

  I have a Understanding of TSQL, I’m a developer not database specialist… and there is no hardware or corruption issues, I have ran the same statement on multiple machines but they all present the same error.

  And scripting out the action with Express this is the output.

  /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
  BEGIN TRANSACTION
  SET QUOTED_IDENTIFIER ON
  SET ARITHABORT ON
  SET NUMERIC_ROUNDABORT OFF
  SET CONCAT_NULL_YIELDS_NULL ON
  SET ANSI_NULLS ON
  SET ANSI_PADDING ON
  SET ANSI_WARNINGS ON
  COMMIT
  BEGIN TRANSACTION
  GO
  ALTER TABLE dbo.connect
  DROP CONSTRAINT DF__connect__records__15E52B55
  GO
  CREATE TABLE dbo.Tmp_connect
  (
  connectid bigint NOT NULL,
  code nvarchar(20) NULL,
  name nvarchar(60) NULL,
  address1 nvarchar(40) NULL,
  address2 nvarchar(40) NULL,
  address3 nvarchar(40) NULL,
  address4 nvarchar(40) NULL,
  address5 nvarchar(40) NULL,
  postcode nvarchar(10) NULL,
  telephone nvarchar(50) NULL,
  fax nvarchar(50) NULL,
  mobile nvarchar(50) NULL,
  email nvarchar(50) NULL,
  weburl nvarchar(50) NULL,
  areaid int NULL,
  regionid int NULL,
  supplierid bigint NULL,
  associateid bigint NULL,
  versionid int NULL,
  batchid int NULL,
  recordstatusid int NULL,
  notes varchar(2600) NULL,
  titleid int NULL,
  firstname nvarchar(60) NULL,
  surname nvarchar(60) NULL,
  middlename nvarchar(60) NULL,
  nametypeid int NULL,
  photofilename nvarchar(255) NULL,
  customerid bigint NULL
  ) ON [PRIMARY]
  GO
  ALTER TABLE dbo.Tmp_connect ADD CONSTRAINT
  DF__connect__records__15E52B55 DEFAULT (0) FOR recordstatusid
  GO
  IF EXISTS(SELECT * FROM dbo.connect)
  EXEC('INSERT INTO dbo.Tmp_connect (connectid, code, name, address1, address2, address3, address4, address5, postcode, telephone, fax, mobile, email, weburl, areaid, regionid, supplierid, associateid, versionid, batchid, recordstatusid, notes, titleid, firstname, surname, middlename, nametypeid, photofilename, customerid)
  SELECT connectid, code, name, address1, address2, address3, address4, address5, postcode, telephone, fax, mobile, email, weburl, areaid, regionid, supplierid, associateid, versionid, batchid, recordstatusid, CONVERT(varchar(2600), notes), titleid, firstname, surname, middlename, nametypeid, photofilename, customerid FROM dbo.connect WITH (HOLDLOCK TABLOCKX)')
  GO
  DROP TABLE dbo.connect
  GO
  EXECUTE sp_rename N'dbo.Tmp_connect', N'connect', 'OBJECT'
  GO
  ALTER TABLE dbo.connect ADD CONSTRAINT
  PK_connect PRIMARY KEY CLUSTERED
  (
  connectid
  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  GO
  COMMIT

  A little excessive for 1 change don’t you think But thanks for you help.

  
  

• Chandra B

  Could you run the commands

  DBCC CHECKCATALOG on the database and

  DBCC CHECKTABLE on the table

  and report the results

  
  
  

• mehmet bey

  If the same statement errors out on multiple machines then the problem might be in the database itself. In any case, could you post the schema of the table with the indexes and the ALTER TABLE you are trying to run So we can run it on our servers and see if it is indeed a database engine issue. The error message you are seeing usually happens due to a fatal error as evidenced by the severe error message. Hence my suggestion that it can be a corruption as indicated by the first message. Alternatively, there is a new forum here for SQL Server disaster recovery and availability so you can post the error message there and see what the storage engine team has to say about it.

  And as I said before, the tools doesn't handle all the column changes using ALTER TABLE. If you want to give feedback to the Tools team then you can do so in the SQL Server Tools Forum or create a bug using http://connect.microsoft.com. The 2nd mechanism will ensure that your feedback gets to the Tools team promptly. I didn't mean to imply that you were not familiar with TSQL. My comment is that the tools will issue different statements behind the scenes and they may not be the most optimal way to make schema changes. So it is best to use a TSQL command if present rather than recreating the table which can be problematic if the table has schema bound objects, other references, constraints, indexes etc. It will just require more work as you observed recreating the table just to alter a column.

  
  
  

• Discoman

  Also to mention this SQL statement is being run using ADO in VB6 when catching the error the error object description is ‘Communication Link Failure”

  If that throw’s any light on the problem for anyone.

  Thanks

  Tristan

  
  

• kenc2005

  SQL Server Express Edition supports the same ALTER statement as the other SKUs. There is no difference in terms of the SQL language functionality. There are certain enterprise capabilities that are disabled or restricted but the language features are the same. The tools however has lot of limitations on the type of schema changes it can handle. And often it may recreate the table so it is best to not use the tools to make schema changes but instead use the DDL statements which are more powerful and straight forward to use with few documented restrictions. Not all of the functionality that the database engine supports are exposed directly / easily in the tools so try to learn the TSQL language features to make your life easier. Your original error indicates some kind of corruption or hardware issue that is causing the ALTER TABLE to fail. So you need to first find out the cause of that problem and then try the ALTER statement again.
  
  

• GianniAb

  *BUMP
  

• THHNO

  USE test;

  GO

  DBCC CHECKCATALOG

  GO

  'Command(s) completed successfully.'

  USE test;

  GO

  DBCC CHECKTABLE ('connect')

  GO

  'DBCC results for 'connect'.

  There are 4661 rows in 209 pages for object "connect".

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.'

  Tristan

  
  

• InvertedAerials

  Thanks for your response; all of your points are valid … with further investigation, I decided to script out an alter column in express guess what; Express creates a TMP table then copies all the data back into the new structure… all that server load for a change column statement.

  Bottom line Sql Express 2005 does not support a true SQL alter column statement.

  Thanks for all the help from everyone.

  
  
• SQL ALTER COLUMN causes a Possible schema corruption.