Vo2Ado Sql Server Adding Constraint
in [Visual Objects]
|
From: E®!k /!sser on 8 Sep 2008 12:35 Ian, Perhaps you should generate a script for the whole database using Management Studio. Not to use it in your app, but it shows how to work with the constraint insude the begin/end sequence. Here is a sample from one of my databases: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[META_XTYPE]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[META_XTYPE]( [ID] [int] IDENTITY(1,1) NOT NULL, [XTYPE] [int] NOT NULL, [DESCRIPTION] [nvarchar](25) NOT NULL, CONSTRAINT [PK_META_XTYPE] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO Another approach could be to create a stored procedure (that can be done on the fly) where you put your code in and execute that stored procedure. hth Erik Hi Erik, I have now found out something that may be a possible cause of the problem. If I put the constraint instruction inside the IF NOT EXISTS etc etc begin instructions end, I get the error [Incorrect Syntax Near The Keyword 'End']. If I put the CONSTRAINT instruction outside of the IF NOT EXIST begin instructions end and then change the CONSTRAINT instruction by adding this to the start of it ALTER TABLE [dbo].[CLNTVIEWHEADER] with NOCHECK ADD CONSTRAINT ETC ETC ETC. Then the table gets created and the CONSTRAINT instruction is actioned. However, next time I run the app I get a new error saying that the 'primary key is already defined'. This is because I have put it outside of the IF NOT EXISTS- BEGIN-END. So although not solved does this give you any clues as to a solution. Ian
From: Ian on 8 Sep 2008 12:54 On 8 Sep, 17:35, E®!k \\/!sser <nos...(a)nospam.com> wrote: > Ian, > > Perhaps you should generate a script for the whole database using Management > Studio. > Not to use it in your app, but it shows how to work with the constraint > insude the begin/end sequence. > Here is a sample from one of my databases: > > IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = > OBJECT_ID(N'[dbo].[META_XTYPE]') AND type in (N'U')) > BEGIN > CREATE TABLE [dbo].[META_XTYPE]( > [ID] [int] IDENTITY(1,1) NOT NULL, > [XTYPE] [int] NOT NULL, > [DESCRIPTION] [nvarchar](25) NOT NULL, > CONSTRAINT [PK_META_XTYPE] PRIMARY KEY CLUSTERED > ( > [ID] ASC > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] > ) ON [PRIMARY] > END > GO > > Another approach could be to create a stored procedure (that can be done on > the fly) where you put your code in and execute that stored procedure. > > hth > > Erik > > Hi Erik, > > I have now found out something that may be a possible cause of the > problem. If I put the constraint instruction inside the > IF NOT EXISTS etc etc > begin > instructions > end, > I get the error [Incorrect Syntax Near The Keyword 'End']. > > If I put the CONSTRAINT instruction outside of the > IF NOT EXIST > begin > instructions > end > and then change the CONSTRAINT instruction by adding this to the start > of it ALTER TABLE [dbo].[CLNTVIEWHEADER] with NOCHECK ADD CONSTRAINT > ETC ETC ETC. Then the table gets created and the CONSTRAINT > instruction is actioned. However, next time I run the app I get a new > error saying that the 'primary key is already defined'. This is > because I have put it outside of the IF NOT EXISTS- BEGIN-END. So > although not solved does this give you any clues as to a solution. > > Ian Hi Erik, That has sorted it. I followed your example and the code seems to be working as expected. Thank you very much for your help. Kind Regards, Ian
From: E®!k /!sser on 8 Sep 2008 14:01 > Thank you very much for your help. You are welcome! Erik Hi Erik, That has sorted it. I followed your example and the code seems to be working as expected. Thank you very much for your help. Kind Regards, Ian
From: Geoff Schaller on 8 Sep 2008 19:26
Ian, Can I suggest a different approach. We have create scripts like this in VO methods as strings but they do become difficult to manage. I now find the better approach is to build such things in SSMS then turn them into a stored proc. The VO code now only executes an SP like Exec CreateCustomerTable. This makes code management a lot easier and you can pass in params like drop if exists etc. The trouble with string management the way you're doing it is that you always run into simple typo issues along with T-SQL issues. Geoff |