Vo2Ado Sql Server Adding Constraint
in [Visual Objects]
|
From: Ian on 8 Sep 2008 07:40 Hi All, I have produced a script from sql server 2008 to create the database and tables required by my app. I have then translated this into vo2ado commands to create the database and tables wjen the user first runs the app using the following eaxmple. cNewSql := " IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = ; OBJECT_ID(N'[dbo].[CLNTVIEWHEADER]') AND type in (N'U'))" cNewSql += " BEGIN" cNewSql += " CREATE TABLE [dbo].[CLNTVIEWHEADER](" cNewSql += " [RECID] [int] IDENTITY(1,1) NOT null," cNewSql += " [TRANSNUM] [char](9) null," cNewSql += " [LASTSAVE] [char](16) null," cNewSql += " [VIEWNAME] [char](50) NOT null," cNewSql += " [INUSEBY] [char](6) null" cNewSql += " ) ON [PRIMARY]" cNewSql += " end" AAdd(aSqlArray,cNewSql) cNewSql := " SET ANSI_NULLS ON" AAdd(aSqlArray,cNewSql) cNewSql := " SET QUOTED_IDENTIFIER ON" AAdd(aSqlArray,cNewSql) CreateSqlTables(aSqlArray,"",oDataConn) This works ok for all of the tables I need to create. The problem is thaat some tables require this from the sql script " CONSTRAINT [PK_CLNTVIEWHEADER] PRIMARY KEY CLUSTERED ( [VIEWNAME] ASC)with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" I have tried to add this in before and after the end statement but it always returns an error. I have tried to process it as an ALTER TABLE instruction after the create sqltables command but that returns an error also. Any ideas how I can add the CONSTRAINT instruction to the above code so it processes without error. Kind Regards, Ian
From: Ginny Caughey on 8 Sep 2008 07:49 Ian, Have you tested your script in SQL Server Management Studio? It don't look right to me. The primary key constraint goes right before ) ON [Primary]. -- Ginny Caughey www.wasteworks.com "Ian" <ithomson(a)mikroaid.co.uk> wrote in message news:889d8901-508e-46a3-95a4-a7f735cf4ead(a)l43g2000hsh.googlegroups.com... > Hi All, > > I have produced a script from sql server 2008 to create the database > and tables required by my app. I have then translated this into vo2ado > commands to create the database and tables wjen the user first runs > the app using the following eaxmple. > > cNewSql := " IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id > = ; > OBJECT_ID(N'[dbo].[CLNTVIEWHEADER]') AND type in (N'U'))" > > cNewSql += " BEGIN" > cNewSql += " CREATE TABLE [dbo].[CLNTVIEWHEADER](" > cNewSql += " [RECID] [int] IDENTITY(1,1) NOT null," > cNewSql += " [TRANSNUM] [char](9) null," > cNewSql += " [LASTSAVE] [char](16) null," > cNewSql += " [VIEWNAME] [char](50) NOT null," > cNewSql += " [INUSEBY] [char](6) null" > cNewSql += " ) ON [PRIMARY]" > cNewSql += " end" > AAdd(aSqlArray,cNewSql) > cNewSql := " SET ANSI_NULLS ON" > AAdd(aSqlArray,cNewSql) > cNewSql := " SET QUOTED_IDENTIFIER ON" > AAdd(aSqlArray,cNewSql) > CreateSqlTables(aSqlArray,"",oDataConn) > > This works ok for all of the tables I need to create. The problem is > thaat some tables require this from the sql script > > " CONSTRAINT [PK_CLNTVIEWHEADER] PRIMARY KEY CLUSTERED ( [VIEWNAME] > ASC)with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, > IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) > ON [PRIMARY]" > > I have tried to add this in before and after the end statement but it > always returns an error. > > I have tried to process it as an ALTER TABLE instruction after the > create sqltables command but that returns an error also. > > Any ideas how I can add the CONSTRAINT instruction to the above code > so it processes without error. > > Kind Regards, > Ian > >
From: Ian on 8 Sep 2008 08:26 On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...(a)wasteworks.com> wrote: > Ian, > > Have you tested your script in SQL Server Management Studio? It don't look > right to me. The primary key constraint goes right before ) ON [Primary]. > > -- > > Ginny Caugheywww.wasteworks.com > > "Ian" <ithom...(a)mikroaid.co.uk> wrote in message > > news:889d8901-508e-46a3-95a4-a7f735cf4ead(a)l43g2000hsh.googlegroups.com... > > > > > Hi All, > > > I have produced a script from sql server 2008 to create the database > > and tables required by my app. I have then translated this into vo2ado > > commands to create the database and tables wjen the user first runs > > the app using the following eaxmple. > > > cNewSql := " IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id > > = ; > > OBJECT_ID(N'[dbo].[CLNTVIEWHEADER]') AND type in (N'U'))" > > > cNewSql += " BEGIN" > > cNewSql += " CREATE TABLE [dbo].[CLNTVIEWHEADER](" > > cNewSql += " [RECID] [int] IDENTITY(1,1) NOT null," > > cNewSql += " [TRANSNUM] [char](9) null," > > cNewSql += " [LASTSAVE] [char](16) null," > > cNewSql += " [VIEWNAME] [char](50) NOT null," > > cNewSql += " [INUSEBY] [char](6) null" > > cNewSql += " ) ON [PRIMARY]" > > cNewSql += " end" > > AAdd(aSqlArray,cNewSql) > > cNewSql := " SET ANSI_NULLS ON" > > AAdd(aSqlArray,cNewSql) > > cNewSql := " SET QUOTED_IDENTIFIER ON" > > AAdd(aSqlArray,cNewSql) > > CreateSqlTables(aSqlArray,"",oDataConn) > > > This works ok for all of the tables I need to create. The problem is > > thaat some tables require this from the sql script > > > " CONSTRAINT [PK_CLNTVIEWHEADER] PRIMARY KEY CLUSTERED ( [VIEWNAME] > > ASC)with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, > > IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) > > ON [PRIMARY]" > > > I have tried to add this in before and after the end statement but it > > always returns an error. > > > I have tried to process it as an ALTER TABLE instruction after the > > create sqltables command but that returns an error also. > > > Any ideas how I can add the CONSTRAINT instruction to the above code > > so it processes without error. > > > Kind Regards, > > Ian- Hide quoted text - > > - Show quoted text - Hi Ginny, I have tried putting the Constraint line where you suggested but I get an error message saying; Incorrect Syntax Near The Keyword 'End' Regards, Ian
From: E®!k /!sser on 8 Sep 2008 08:52 Ian, Could it be you try to execute the script on a Sql2000 or 2005 server? Erik "Ian" <ithomson(a)mikroaid.co.uk> wrote in message news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c(a)z66g2000hsc.googlegroups.com... On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...(a)wasteworks.com> wrote: > Ian, > > Have you tested your script in SQL Server Management Studio? It don't look > right to me. The primary key constraint goes right before ) ON [Primary]. > > -- > > Ginny Caugheywww.wasteworks.com > > "Ian" <ithom...(a)mikroaid.co.uk> wrote in message > > news:889d8901-508e-46a3-95a4-a7f735cf4ead(a)l43g2000hsh.googlegroups.com... > > > > > Hi All, > > > I have produced a script from sql server 2008 to create the database > > and tables required by my app. I have then translated this into vo2ado > > commands to create the database and tables wjen the user first runs > > the app using the following eaxmple. > > > cNewSql := " IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id > > = ; > > OBJECT_ID(N'[dbo].[CLNTVIEWHEADER]') AND type in (N'U'))" > > > cNewSql += " BEGIN" > > cNewSql += " CREATE TABLE [dbo].[CLNTVIEWHEADER](" > > cNewSql += " [RECID] [int] IDENTITY(1,1) NOT null," > > cNewSql += " [TRANSNUM] [char](9) null," > > cNewSql += " [LASTSAVE] [char](16) null," > > cNewSql += " [VIEWNAME] [char](50) NOT null," > > cNewSql += " [INUSEBY] [char](6) null" > > cNewSql += " ) ON [PRIMARY]" > > cNewSql += " end" > > AAdd(aSqlArray,cNewSql) > > cNewSql := " SET ANSI_NULLS ON" > > AAdd(aSqlArray,cNewSql) > > cNewSql := " SET QUOTED_IDENTIFIER ON" > > AAdd(aSqlArray,cNewSql) > > CreateSqlTables(aSqlArray,"",oDataConn) > > > This works ok for all of the tables I need to create. The problem is > > thaat some tables require this from the sql script > > > " CONSTRAINT [PK_CLNTVIEWHEADER] PRIMARY KEY CLUSTERED ( [VIEWNAME] > > ASC)with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, > > IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) > > ON [PRIMARY]" > > > I have tried to add this in before and after the end statement but it > > always returns an error. > > > I have tried to process it as an ALTER TABLE instruction after the > > create sqltables command but that returns an error also. > > > Any ideas how I can add the CONSTRAINT instruction to the above code > > so it processes without error. > > > Kind Regards, > > Ian- Hide quoted text - > > - Show quoted text - Hi Ginny, I have tried putting the Constraint line where you suggested but I get an error message saying; Incorrect Syntax Near The Keyword 'End' Regards, Ian
From: Ginny Caughey on 8 Sep 2008 09:04
Ian, The next thing I'd try is removing the SET statements. I doubt you need them since those options are usually the default. -- Ginny Caughey www.wasteworks.com "Ian" <ithomson(a)mikroaid.co.uk> wrote in message news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c(a)z66g2000hsc.googlegroups.com... On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...(a)wasteworks.com> wrote: > Ian, > > Have you tested your script in SQL Server Management Studio? It don't look > right to me. The primary key constraint goes right before ) ON [Primary]. > > -- > > Ginny Caugheywww.wasteworks.com > > "Ian" <ithom...(a)mikroaid.co.uk> wrote in message > > news:889d8901-508e-46a3-95a4-a7f735cf4ead(a)l43g2000hsh.googlegroups.com... > > > > > Hi All, > > > I have produced a script from sql server 2008 to create the database > > and tables required by my app. I have then translated this into vo2ado > > commands to create the database and tables wjen the user first runs > > the app using the following eaxmple. > > > cNewSql := " IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id > > = ; > > OBJECT_ID(N'[dbo].[CLNTVIEWHEADER]') AND type in (N'U'))" > > > cNewSql += " BEGIN" > > cNewSql += " CREATE TABLE [dbo].[CLNTVIEWHEADER](" > > cNewSql += " [RECID] [int] IDENTITY(1,1) NOT null," > > cNewSql += " [TRANSNUM] [char](9) null," > > cNewSql += " [LASTSAVE] [char](16) null," > > cNewSql += " [VIEWNAME] [char](50) NOT null," > > cNewSql += " [INUSEBY] [char](6) null" > > cNewSql += " ) ON [PRIMARY]" > > cNewSql += " end" > > AAdd(aSqlArray,cNewSql) > > cNewSql := " SET ANSI_NULLS ON" > > AAdd(aSqlArray,cNewSql) > > cNewSql := " SET QUOTED_IDENTIFIER ON" > > AAdd(aSqlArray,cNewSql) > > CreateSqlTables(aSqlArray,"",oDataConn) > > > This works ok for all of the tables I need to create. The problem is > > thaat some tables require this from the sql script > > > " CONSTRAINT [PK_CLNTVIEWHEADER] PRIMARY KEY CLUSTERED ( [VIEWNAME] > > ASC)with (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, > > IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) > > ON [PRIMARY]" > > > I have tried to add this in before and after the end statement but it > > always returns an error. > > > I have tried to process it as an ALTER TABLE instruction after the > > create sqltables command but that returns an error also. > > > Any ideas how I can add the CONSTRAINT instruction to the above code > > so it processes without error. > > > Kind Regards, > > Ian- Hide quoted text - > > - Show quoted text - Hi Ginny, I have tried putting the Constraint line where you suggested but I get an error message saying; Incorrect Syntax Near The Keyword 'End' Regards, Ian |