IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New 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]
New 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
New 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]
New 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

     SQL Server 2000 just bit me - (lincoln) - (3)
         Re: SQL Server 2000 just bit me - (JayMehaffey) - (1)
             Thanks for the answer - (lincoln)
         Can't replicate it here - (ChrisR)

The natural irony quotient of the Universe asserting itself in everyday life.
37 ms