November 2, 2016 at 6:57 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply