SQL Server 2000 just bit me

Post #239,709 by lincoln 12/28/05 3:45:32 PM Reply	SQL Server 2000 just bit me I used the following text within a stored procedure to create a table: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE PROCEDURE CreateTableNewTable AS SET NOCOUNT ON CREATE TABLE NewTable ( UID\t\tbigint\t\tIDENTITY (1,1) PRIMARY KEY, RecordType\t\t\tchar(2)\t\tNOT NULL, RecSequenceNumber\t\tint\t\tNOT NULL, TicketNumber\t\t\tvarchar(15)\tNOT NULL, TicketNumberCheckDigit\t\tchar(1)\t\tNOT NULL, RecordDate\t\t\tdatetime\tNOT NULL, FileDate\t\t\tdatetime\tNOT NULL, TransmissionControlNumber\tvarchar(15)\tNULL, TCNCheckDigit\t\t\tchar(1)\t\tNULL, OriginalRecord\t\t\tvarchar(450)\tNULL \t) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO I've used it multiple times as a template to make dozens of tables. Well, guess what? SQL Server 2000 decided that, on the average, 1 out of every 2 times I used it -- the primary key WAS NOT CREATED. No error messages, no warnings, no nothing. SQL Server made me think that every worked as expected. And yet it didn't. Any suggestions on how to modify the syntax so that the primary key gets created every time that I use this stored procedure format? lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. [link\|mailto:bconnors@ev1.net\|contact me]
Post #239,792 by JayMehaffey 12/29/05 11:47:25 AM Reply	Re: SQL Server 2000 just bit me The only thing I can suggest is using a seperate alter table to add the primary key. It would look something like this. CREATE TABLE NewTable ( UID bigint IDENTITY (1,1), RecordType char(2) NOT NULL, RecSequenceNumber int NOT NULL, TicketNumber varchar(15) NOT NULL, TicketNumberCheckDigit char(1) NOT NULL, RecordDate datetime NOT NULL, FileDate datetime NOT NULL, TransmissionControlNumber varchar(15) NULL, TCNCheckDigit char(1) NULL, OriginalRecord varchar(450) NULL ) ALTER TABLE NewTable WITH NOCHECK ADD CONSTRAINT [PK_NewTable] PRIMARY KEY CLUSTERED ( [UID] ) WITH FILLFACTOR = 90 ON [PRIMARY] GO Jay
Post #239,836 by lincoln 12/30/05 10:18:23 AM Reply	Thanks for the answer Since I have the same tables in multiple databases, what I did was put the ALTER TABLE statements to create the primary key constraint into a stored procedure. Your way is better because it keeps the primary key creation with the table creation. I did an online chat with a "help person" at Microsoft last night, explaining the situation, and asking if they could tell me why the primary key constraint creation was hit or miss with my stored procedure. They didn't know why, especially after I showed them how I copied the text verbatim from the Books Online to use as a template. lincoln "Chicago to my mind was the only place to be. ... I above all liked the city because it was filled with people all a-bustle, and the clatter of hooves and carriages, and with delivery wagons and drays and peddlers and the boom and clank of freight trains. And when those black clouds came sailing in from the west, pouring thunderstorms upon us so that you couldn't hear the cries or curses of humankind, I liked that best of all. Chicago could stand up to the worst God had to offer. I understood why it was built--a place for trade, of course, with railroads and ships and so on, but mostly to give all of us a magnitude of defiance that is not provided by one house on the plains. And the plains is where those storms come from." -- E.L. Doctorow Never apply a Star Trek solution to a Babylon 5 problem. [link\|mailto:bconnors@ev1.net\|contact me]
Post #239,920 by ChrisR 12/31/05 2:56:28 AM Reply	Can't replicate it here Just ran the (slightly modified) script below couple dozen times, and the primary key seems to come out fine. You could use the sysindexes query to detect when it happens - at least you could automate the detection of the error. (Also a suggestion to name all indexes (including primary keys) as it helps in maintenance). SET QUOTED_IDENTIFIER ON\nGO\nSET ANSI_NULLS ON\nGO\n\nIF EXISTS(SELECT * FROM dbo.sysobjects WHERE ((name = 'CreateTableNewTable') AND (type ='P')))\n DROP PROCEDURE dbo.CreateTableNewTable\nGO\n\nCREATE PROCEDURE CreateTableNewTable\n\nAS\n SET NOCOUNT ON \n\n CREATE TABLE NewTable (\n UID BIGINT IDENTITY (1,1),\n RecordType CHAR(2) NOT NULL,\n RecSequenceNumber INT NOT NULL,\n TicketNumber VARCHAR(15) NOT NULL,\n TicketNumberCheckDigit CHAR(1) NOT NULL,\n RecordDate DATETIME NOT NULL,\n FileDate DATETIME NOT NULL,\n TransmissionControlNumber VARCHAR(15) NULL,\n TCNCheckDigit CHAR(1) NULL,\n OriginalRecord VARCHAR(450) NULL\n CONSTRAINT PK_NewTable PRIMARY KEY CLUSTERED(UID))\nGO\n\nSET QUOTED_IDENTIFIER OFF\nGO\nSET ANSI_NULLS ON\nGO\n\nIF EXISTS(SELECT * FROM sysobjects WHERE ((type = 'U') AND (name = 'NewTable' ))) \n DROP TABLE dbo.NewTable\nGO\n\nEXECUTE CreateTableNewTable\nGO\n\nSELECT\n IndexType = (CASE\n WHEN (ind.indid = 1) THEN ' PRIMARY KEY CLUSTERED'\n ELSE ' PRIMARY KEY NONCLUSTERED'\n END),\n IndexName = RTRIM(ind.name),\n TableName = RTRIM(tab.name),\n ColumnName = RTRIM(col.name),\n KeyNo = RTRIM(sik.keyno)\nFROM\n sysindexes ind\n INNER JOIN sysindexkeys sik ON(\n (sik.indid = ind.indid))\n INNER JOIN syscolumns col ON(\n (col.colid = sik.colid))\n INNER JOIN sysobjects tab ON(\n (tab.id = ind.id) AND\n (tab.id = sik.id) AND\n (tab.id = col.id))\n LEFT JOIN sysobjects cns ON(\n (cns.name = ind.name) AND\n (cns.parent_obj = tab.id))\nWHERE\n (ind.name LIKE 'PK_%') AND\n (ind.name != 'pk_dtproperties') AND\n (tab.name IN('NewTable'))\nGO\n

Welcome to IWETHEY!