Primary key not dected in MS Query(Excel 2013) after a table structure modification(SQL Server 2008R2)

• jeanyvesR

  Say Hey Kid

  Points: 696

  More actions

  November 2, 2016 at 6:57 am

  #77441

  Hi all,

  I modified many table structure as following :

  - adding new integer(indentity) field --> New primary key

  - deleting old primary key

  After this modification , when I tried to make query with MS Query(Excel 2013) I noticed that table didn't show the new primary key in bold style ??

  After some investigation using SQL Profiler , it seems that Ms Query use the system procedure sp_special_columns_100 to get primary key column and that's the problem :

  if I run manually the following line no record are returned ??

  exec [TEST].[sys].sp_special_columns_100 N'T_TEST_TST',N'dbo',N'TEST',N'R',N'C',N'U'

  If someone know if it's a known issue of SQL Server 2K8R2 ?

  Thanks in advance.

  Jean-Yves

  P.S : Here is an example of modification of structure which cause the issue(DATABASE NAME : TEST).

  CREATE TABLE T_TEST_TST

  (

  ID_TST VARCHAR(8) not null CONSTRAINT PK_ID_TST PRIMARY KEY CLUSTERED,

  TST_LIBELLE VARCHAR(20) NOT NULL,

  TST_QTY INTEGER NOT NULL CONSTRAINT CK_TST_QTY CHECK (TST_QTY>0)

  )

  GO

  INSERT INTO T_TEST_TST

  SELECT 'T1','TEST N°1',10

  UNION ALL

  SELECT 'T2','TEST N°2',20

  UNION ALL

  SELECT 'T3','TEST N°3',30

  UNION ALL

  SELECT 'T4','TEST N°4',40

  GO

  -----------------------------------

  --- BEGIN STRUCTURE MODIFICATION --

  -----------------------------------

  ALTER TABLE T_TEST_TST DROP CONSTRAINT PK_ID_TST

  GO

  -----------------------------

  --- NEW PRIMARY KEY ---

  -----------------------------

  ALTER TABLE T_TEST_TST ADD TST_ID INTEGER NOT NULL IDENTITY(1,1) CONSTRAINT PK_TST_ID PRIMARY KEY CLUSTERED

  GO

  ----------------------------------------

  --- DELETING OLD PRIMARY KEY ---

  ----------------------------------------

  ALTER TABLE T_TEST_TST DROP COLUMN ID_TST

  GO