Vo2Ado Sql Server Adding Constraint
in [Visual Objects]
|
From: Ian on 8 Sep 2008 09:45 On 8 Sep, 13:52, E®!k \\/!sser <nos...(a)nospam.com> wrote: > Ian, > > Could it be you try to execute the script on a Sql2000 or 2005 server? > > Erik > > "Ian" <ithom...(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- Hide quoted text - > > - Show quoted text - Hi Erik, I created the script this morning on sql server 2008. I then cut the code out of the sql server 2008 script and added it to my VO app as per the original example shown. As long as I exclude all of the CONSTRAINT instructions all of the tables (53 in total) are created without error. As soon as I add in the CONSTRAINT instructions I get errors. I need to add the CONSTRAINT instructions back in as they are part of the Primary Key and Foreign Key settings. I have taken out the SET instructions as suggested by Ginny but this did not clear the errors. Hope someone can help. Regards, Ian
From: Ginny Caughey on 8 Sep 2008 10:03 Ian, Does it run with just the primary key constraint? If so you can add the others in separate alter table statements: alter table Foo add constraint foo_bah foreign key (otherTable) references otherTable (columnName) You could also try posting on a SQL Server newsgroup. This is not really a VO issue. -- Ginny Caughey www.wasteworks.com "Ian" <ithomson(a)mikroaid.co.uk> wrote in message news:56d7334d-49fe-44d3-a488-76826d256323(a)f36g2000hsa.googlegroups.com... On 8 Sep, 13:52, E�!k \\/!sser <nos...(a)nospam.com> wrote: > Ian, > > Could it be you try to execute the script on a Sql2000 or 2005 server? > > Erik > > "Ian" <ithom...(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- Hide quoted text - > > - Show quoted text - Hi Erik, I created the script this morning on sql server 2008. I then cut the code out of the sql server 2008 script and added it to my VO app as per the original example shown. As long as I exclude all of the CONSTRAINT instructions all of the tables (53 in total) are created without error. As soon as I add in the CONSTRAINT instructions I get errors. I need to add the CONSTRAINT instructions back in as they are part of the Primary Key and Foreign Key settings. I have taken out the SET instructions as suggested by Ginny but this did not clear the errors. Hope someone can help. Regards, Ian
From: E®!k /!sser on 8 Sep 2008 10:32 Just tried, the query below can be executed without errors from within a VO-Vo2ADO application: CREATE TABLE [dbo].[CLNTVIEWHEADER]( [RECID] [int] IDENTITY(1,1) NOT null, [TRANSNUM] [char](9) null, [LASTSAVE] [char](16) null, [VIEWNAME] [char](50) NOT null, [INUSEBY] [char](6) null, 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] ) HTH Erik "Ian" <ithomson(a)mikroaid.co.uk> wrote in message news:56d7334d-49fe-44d3-a488-76826d256323(a)f36g2000hsa.googlegroups.com... On 8 Sep, 13:52, E�!k \\/!sser <nos...(a)nospam.com> wrote: > Ian, > > Could it be you try to execute the script on a Sql2000 or 2005 server? > > Erik > > "Ian" <ithom...(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- Hide quoted text - > > - Show quoted text - Hi Erik, I created the script this morning on sql server 2008. I then cut the code out of the sql server 2008 script and added it to my VO app as per the original example shown. As long as I exclude all of the CONSTRAINT instructions all of the tables (53 in total) are created without error. As soon as I add in the CONSTRAINT instructions I get errors. I need to add the CONSTRAINT instructions back in as they are part of the Primary Key and Foreign Key settings. I have taken out the SET instructions as suggested by Ginny but this did not clear the errors. Hope someone can help. Regards, Ian
From: Ian on 8 Sep 2008 11:17 On 8 Sep, 15:32, E®!k \\/!sser <nos...(a)nospam.com> wrote: > Just tried, the query below can be executed without errors from within a > VO-Vo2ADO application: > > CREATE TABLE [dbo].[CLNTVIEWHEADER]( > [RECID] [int] IDENTITY(1,1) NOT null, > [TRANSNUM] [char](9) null, > [LASTSAVE] [char](16) null, > [VIEWNAME] [char](50) NOT null, > [INUSEBY] [char](6) null, > 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] ) > > HTH > > Erik > > "Ian" <ithom...(a)mikroaid.co.uk> wrote in message > > news:56d7334d-49fe-44d3-a488-76826d256323(a)f36g2000hsa.googlegroups.com... > On 8 Sep, 13:52, E®!k \\/!sser <nos...(a)nospam.com> wrote: > > > > > > > Ian, > > > Could it be you try to execute the script on a Sql2000 or 2005 server? > > > Erik > > > "Ian" <ithom...(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- Hide quoted text - > > > - Show quoted text - > > Hi Erik, > > I created the script this morning on sql server 2008. I then cut the > code out of the sql server 2008 script and added it to my VO app as > per the original example shown. As long as I exclude all of the > CONSTRAINT instructions all of the tables (53 in total) are created > without error. As soon as I add in the CONSTRAINT instructions I get > errors. > > I need to add the CONSTRAINT instructions back in as they are part of > the Primary Key and Foreign Key settings. > > I have taken out the SET instructions as suggested by Ginny but this > did not clear the errors. > > Hope someone can help. > > Regards, > Ian- Hide quoted text - > > - Show quoted text - Hi Erik I have just tried to run the following from within my VO app using VO2ADO cNewsql :={} 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 += " 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" cNewSql += " ) ON [PRIMARY]" cNewSql += " end" AAdd(aSqlArray,cNewSql) CreateSqlTables(aSqlArray,"",oDataConn) This gets an error coming back saying [Incorrect Syntax Near The Keyword 'End']. Is it because I am using IF NOT EXISTS then putting the instructions inside a begin/end loop. Ian
From: Ian on 8 Sep 2008 12:11 On 8 Sep, 16:17, Ian <ithom...(a)mikroaid.co.uk> wrote: > On 8 Sep, 15:32, E®!k \\/!sser <nos...(a)nospam.com> wrote: > > > > > > > Just tried, the query below can be executed without errors from within a > > VO-Vo2ADO application: > > > CREATE TABLE [dbo].[CLNTVIEWHEADER]( > > [RECID] [int] IDENTITY(1,1) NOT null, > > [TRANSNUM] [char](9) null, > > [LASTSAVE] [char](16) null, > > [VIEWNAME] [char](50) NOT null, > > [INUSEBY] [char](6) null, > > 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] ) > > > HTH > > > Erik > > > "Ian" <ithom...(a)mikroaid.co.uk> wrote in message > > >news:56d7334d-49fe-44d3-a488-76826d256323(a)f36g2000hsa.googlegroups.com.... > > On 8 Sep, 13:52, E®!k \\/!sser <nos...(a)nospam.com> wrote: > > > > Ian, > > > > Could it be you try to execute the script on a Sql2000 or 2005 server? > > > > Erik > > > > "Ian" <ithom...(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- Hide quoted text - > > > > - Show quoted text - > > > Hi Erik, > > > I created the script this morning on sql server 2008. I then cut the > > code out of the sql server 2008 script and added it to my VO app as > > per the original example shown. As long as I exclude all of the > > CONSTRAINT instructions all of the tables (53 in total) are created > > without error. As soon as I add in the CONSTRAINT instructions I get > > errors. > > > I need to add the CONSTRAINT instructions back in as they are part of > > the Primary Key and Foreign Key settings. > > > I have taken out the SET instructions as suggested by Ginny but this > > did not clear the errors. > > > Hope someone can help. > > > Regards, > > Ian- Hide quoted text - > > > - Show quoted text - > > Hi Erik > > I have just tried to run the following from within my VO app using > VO2ADO > > cNewsql :={} > 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 += " 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" > cNewSql += " ) ON [PRIMARY]" > cNewSql += " end" > AAdd(aSqlArray,cNewSql) > CreateSqlTables(aSqlArray,"",oDataConn) > > This gets an error coming back saying [Incorrect Syntax Near The > Keyword 'End']. Is it because I am using IF NOT EXISTS then putting > the instructions inside a begin/end loop. > > Ian- Hide quoted text - > > - Show quoted text - 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
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: VO CDX and Comix CDX Next: GetSystemMetrics and SM_CXPADDEDBORDER |