Creating Primary key with ODBC

Hi all,

I've spent hours trying to find the error in the following SQL 2000
command:

ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEY
CLUSTERED ([PhoneNo]) On [PRIMARY]

Every time I try to "Execute" this from my (VB5) ODBC connection I get:

Runtime error 3289;
Syntax error in CONSTRAINT clause

For the life of me I can see nothing wrong. I used Enterprise manager
to create this statement, and I can create the primary key fine from
there. The PhoneNo field does not allow NULLs.

Everything is service-packed up to date. I have tried using
[databasename].[dbo].[ClientList] and suchlike.

I've tried to find a relevant manual, but my SQL Server developers
guide suggests this should be ok and I can't see anything wrong in the
books online.

Can anybody please help?

TIA

re: Creating Primary key with ODBC

RS200Phil (philsowden@dataservicesltd.co.uk) writes:[color=blue]
> I've spent hours trying to find the error in the following SQL 2000
> command:
>
> ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEY
> CLUSTERED ([PhoneNo]) On [PRIMARY]
>
> Every time I try to "Execute" this from my (VB5) ODBC connection I get:
>
> Runtime error 3289;
> Syntax error in CONSTRAINT clause
>
> For the life of me I can see nothing wrong. I used Enterprise manager
> to create this statement, and I can create the primary key fine from
> there. The PhoneNo field does not allow NULLs.[/color]

That appears to be an error from the client layer. There certainly is
no syntax error in that statement as far as SQL Server is concerned, as
I can see. (Run in Query Analyzer to verify.)

Could you post the actual VB code you are using? Please also include
the part where you connect.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Creating Primary key with ODBC

Thanks for the prompt reply Erland.

The database is opened as follows:
..
..
..
lsConnectionString = "ODBC;Description=SQL Server 2k;DRIVER=SQL
Server;SERVER=SERVER;UID=Administrator;" & _

"APP=??????;WSID=PII333;DATABASE=Sparc;Network=DBN MPNTW;QueryLog_On=Yes;
Trusted_Connection=Yes"

Set gdbSparc = DBEngine.Workspaces(0).OpenDatabase("", dbDriverNoPrompt,
False, lsConnectionString)
..
..
..


(Sorry for line confusion, hope this makes sense!).

The function that would build the indexes contains the following code.

lsSQL = "ALTER TABLE " & lsTable & " " & _
"ADD CONSTRAINT PK_" & lsTable & " PRIMARY KEY CLUSTERED " & _
"([PhoneNo]) On [PRIMARY];"

gdbSparc.Execute lsSQL


This translates to the code I posted previously when the "lsTable"
parameter is provided (e.g. [ClientTable]).

I pasted the code by printing the value of <lsSQL> in the debug window
and copying it from there.

HTH

Phil



*** Sent via Developersdex http://www.developersdex.com ***

re: Creating Primary key with ODBC

RS200Phil (RS200Phil@dataservicesltdnospam.co.uk) writes:[color=blue]
> The database is opened as follows:
> .
> .
> .
> lsConnectionString = "ODBC;Description=SQL Server 2k;DRIVER=SQL
> Server;SERVER=SERVER;UID=Administrator;" & _
>
> "APP=??????;WSID=PII333;DATABASE=Sparc;Network=DBN MPNTW;QueryLog_On=Yes;
> Trusted_Connection=Yes"
>
> Set gdbSparc = DBEngine.Workspaces(0).OpenDatabase("", dbDriverNoPrompt,
> False, lsConnectionString)
> .
> .
> (Sorry for line confusion, hope this makes sense!).
>
> The function that would build the indexes contains the following code.
>
> lsSQL = "ALTER TABLE " & lsTable & " " & _
> "ADD CONSTRAINT PK_" & lsTable & " PRIMARY KEY CLUSTERED " & _
> "([PhoneNo]) On [PRIMARY];"
>
> gdbSparc.Execute lsSQL[/color]

Hm, doesn't look like modern technology to me. :-)

Have you checked which version of the ODBC SQL Server driver you have?
Which operating system is this (incl Service Pack)? Do you know which
version of the MDAC you have?

All I can really recommend is to try to get a newer version of the ODBC
driver, as this appeears to be culprit. (You could try to remove the
"On [PRIMARY]" part.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Creating Primary key with ODBC

RS200Phil wrote:[color=blue]
> gdbSparc.Execute lsSQL[/color]

Try:
gdbSparc.Execute lsSQL, dbSQLPassthrough

Otherwise Jet sticks its nose in.

re: Creating Primary key with ODBC

Thanks for that, Trevor.

The passthrough was originally in place, but it failed with error 3416.
I thought I was getting more info when I went through Jet.

However, you've hit the nail on the thumb! I found the following in the
Jet manual:

"Note The Microsoft Jet database engine doesn't support the use of
CONSTRAINT, or any of the data definition language (DDL) statements,
with non-Microsoft Jet databases. Use the DAO Create methods instead."

So it's back to the drawing board, then!

BTW - I am converting about 100 quite large tables from a legacy Access
97 back end to SQL 2k. DTS doesn't do a brilliant job of the
conversion, but it did create the initial vb5 code for me. I just had
to bodge it for the index creation (no good there, then!) and to change
some of the field types (rather than do it manually for about 1000
fields!).

Thanks for your help, I'm optimistic that I can resolve it now.

Cheers

Phil


*** Sent via Developersdex http://www.developersdex.com ***

re: Creating Primary key with ODBC

RS200Phil wrote:[color=blue]
> Thanks for that, Trevor.
>
> The passthrough was originally in place, but it failed with error 3416.
> I thought I was getting more info when I went through Jet.[/color]

You mean 3146? You can loop the errors collection of the dbengine
object, e.g.

dim e as Error
....
For each e in DbEngine.Errors
debug.print e.number, e.description
Next
[color=blue]
> BTW - I am converting about 100 quite large tables from a legacy Access
> 97 back end to SQL 2k. DTS doesn't do a brilliant job of the
> conversion, but it did create the initial vb5 code for me. I just had
> to bodge it for the index creation (no good there, then!) and to change
> some of the field types (rather than do it manually for about 1000
> fields!).[/color]

Have you tried the upsizing wizard? http://support.microsoft.com/kb/q176614/

re: Creating Primary key with ODBC

Thanks so much for the ideas. Of course - I should've thought of the
dbengine error collection.

Also, I like the idea of the upsizing wizard. I've used it before, some
time ago, but opted for the SQL Server DTS tool. I'll give this a go,
too.

I appreciate all your help, Trevor. I'm sure I'll be ok now.

Cheers

Phil




*** Sent via Developersdex http://www.developersdex.com ***