Vo2Ado Sql Server Adding Constraint

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

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@mikroaid.co.uk> wrote in message
news:889d8901-508e-46a3-95a4-a7f735cf4ead@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
>
>

On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> news:889d8901-508e-46a3-95a4-a7f735cf4ead@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

Ian,

Could it be you try to execute the script on a Sql2000 or 2005 server?

Erik


"Ian" <ithomson@mikroaid.co.uk> wrote in message
news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c@z66g2000hsc.googlegroups.com...
On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> news:889d8901-508e-46a3-95a4-a7f735cf4ead@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

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@mikroaid.co.uk> wrote in message
news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c@z66g2000hsc.googlegroups.com...
On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> news:889d8901-508e-46a3-95a4-a7f735cf4ead@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

On 8 Sep, 13:52, E®!k \\/!sser <nos...@nospam.com> wrote:
> Ian,
>
> Could it be you try to execute the script on *a Sql2000 or 2005 server?
>
> Erik
>
> "Ian" <ithom...@mikroaid.co.uk> wrote in message
>
> news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c@z66g2000hsc.googlegroups.com...
> On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> >news:889d8901-508e-46a3-95a4-a7f735cf4ead@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

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@mikroaid.co.uk> wrote in message
news:56d7334d-49fe-44d3-a488-76826d256323@f36g2000hsa.googlegroups.com...
On 8 Sep, 13:52, E®!k \\/!sser <nos...@nospam.com> wrote:
> Ian,
>
> Could it be you try to execute the script on a Sql2000 or 2005 server?
>
> Erik
>
> "Ian" <ithom...@mikroaid.co.uk> wrote in message
>
> news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c@z66g2000hsc.googlegroups.com...
> On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> >news:889d8901-508e-46a3-95a4-a7f735cf4ead@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

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@mikroaid.co.uk> wrote in message
news:56d7334d-49fe-44d3-a488-76826d256323@f36g2000hsa.googlegroups.com...
On 8 Sep, 13:52, E®!k \\/!sser <nos...@nospam.com> wrote:
> Ian,
>
> Could it be you try to execute the script on a Sql2000 or 2005 server?
>
> Erik
>
> "Ian" <ithom...@mikroaid.co.uk> wrote in message
>
> news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c@z66g2000hsc.googlegroups.com...
> On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> >news:889d8901-508e-46a3-95a4-a7f735cf4ead@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

On 8 Sep, 15:32, E®!k \\/!sser <nos...@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...@mikroaid.co.uk> wrote in message
>
> news:56d7334d-49fe-44d3-a488-76826d256323@f36g2000hsa.googlegroups.com...
> On 8 Sep, 13:52, E®!k \\/!sser <nos...@nospam.com> wrote:
>
>
>
>
>
> > Ian,
>
> > Could it be you try to execute the script on a Sql2000 or 2005 server?
>
> > Erik
>
> > "Ian" <ithom...@mikroaid.co.uk> wrote in message
>
> >news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c@z66g2000hsc.googlegroups.com....
> > On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> > >news:889d8901-508e-46a3-95a4-a7f735cf4ead@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

On 8 Sep, 16:17, Ian <ithom...@mikroaid.co.uk> wrote:
> On 8 Sep, 15:32, E®!k \\/!sser <nos...@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...@mikroaid.co.uk> wrote in message
>
> >news:56d7334d-49fe-44d3-a488-76826d256323@f36g2000hsa.googlegroups.com....
> > On 8 Sep, 13:52, E®!k \\/!sser <nos...@nospam.com> wrote:
>
> > > Ian,
>
> > > Could it be you try to execute the script on a Sql2000 or 2005 server?
>
> > > Erik
>
> > > "Ian" <ithom...@mikroaid.co.uk> wrote in message
>
> > >news:4306bcd2-ca2f-4ad3-aa41-af59674ae15c@z66g2000hsc.googlegroups.com....
> > > On 8 Sep, 12:49, "Ginny Caughey" <ginny.caughey.onl...@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...@mikroaid.co.uk> wrote in message
>
> > > >news:889d8901-508e-46a3-95a4-a7f735cf4ead@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 problemis
> > > > > 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