September 5, 2002 at 10:45 am
I have written a vb program that creates a database, tables, and stored procs using DMO. I wrote it for SQL2K but now it needs to work with MSDE 1.0 -- SQL7.0.
The following error is generated when the DMO code attempts to add the populated Key object to the keys collection:
Microsoft ODBC SQL Server Driver Line 1:Incorrect syntax near dbo
Are there syntax differences between SQL7 and SQL2K. I am not using any objects that end in '2'.
September 5, 2002 at 12:43 pm
Not that I remember anyway! Can you post the code?
Andy
September 5, 2002 at 2:38 pm
The key field is defined: cust_id, char, False (for allow nulls), 8
for length.
Set namesPKtblAdvCustomers = keyPKtblAdvCustomers.KeyColumns
NamesPKtblAdvCustomers.Add "cust_id"
tblAdvCustomers.BeginAlter
THE NEXT LINE CAUSES ERROR
tblAdvCustomers.Keys.Add keyPKtblAdvCustomers
I can post the code that creates the columns in the table, if that helps solve the problem. Let me know if you need it.
Thanks
September 5, 2002 at 3:09 pm
Andy, Here is the code that creates the table columns.
Dim tblAdvCustomers As New SQLDMO.Table
tblAdvCustomers.name = "tblAdvCustomers"
tblAdvCustomers.FileGroup = "PRIMARY"
'Create the column objects using my CreateTableColumn procedure
CreateTableColumn tbllAdvCustomers, "cust_id", "varchar", False, , 8
CreateTableColumn tblAdvCustomers, "source_cust_id", "int", False, , 4
CreateTableColumn tblAdvCustomers, "cust_name", "varchar", True, , 50
CreateTableColumn tblAdvCustomers, "address", "varchar", True, , 100
.....and so on for the other columns
oDatabaseNew.Tables.Add tblAdvCustomers
'Now create a PRIMARY key
Dim keyPKtblAdvCustomers As New SQLDMO.Key
Dim namesPKtblAdvCustomers As SQLDMO.Names
'Create the primary, clustered key on cust_id
keyPKtblAdvCustomers.Clustered = True
keyPKtblAdvCustomers.Type = SQLDMOKey_Primary
' Use the Names collection to define the constraint on the
' cust_id column.
Set namesPKtblAdvCustomers = keyPKtblAdvCustomers.KeyColumns
namesPKtblAdvCustomers.Add "cust_id"
' Mark start of change unit.
tblAdvCustomers.BeginAlter
THE NEXT LINE OF CODE CAUSES THE ERROR ****
' Add the populated Key object to the Keys collection of the Table object.
tblAdvCustomers.Keys.Add keyPKtblAdvCustomers
' Create the PRIMARY KEY constraint by committing the unit of change.
tblAdvCustomers.DoAlter
********************CREATE TABLE COLUMN*******************************
Private Sub CreateTableColumn(oTable As SQLDMO.Table, sName As String, sDatatype As String, bAllowNulls As Boolean, _
Optional varDRIDefault As Variant, Optional bytLength As Byte = 0, Optional bIdentity As Boolean = False, _
Optional bytIncrement As Byte = 0, Optional bytSeed As Byte = 0)
On Error GoTo ErrorHandler_Err
Dim colMyColumn As SQLDMO.Column
Set colMyColumn = New SQLDMO.Column
With colMyColumn
.name = sName
.Datatype = sDatatype
.AllowNulls = bAllowNulls
'IsMissing only works for variants
If Not IsMissing(varDRIDefault) Then
.DRIDefault = varDRIDefault
End If
'for optional parameters that are not variants, set a default and check for it
If Not (bytLength = 0) Then
.Length = bytLength
End If
If Not (bIdentity = False) Then
.Identity = bIdentity
End If
If Not (bytIncrement = 0) Then
.IdentityIncrement = bytIncrement
End If
If Not (bytSeed = 0) Then
.IdentitySeed = bytSeed
End If
End With
oTable.Columns.Add colMyColumn
ErrorHandler_Exit:
Exit Sub
ErrorHandler_Err: and so on
End Sub
September 5, 2002 at 3:30 pm
Does look like a 7.0 problem, code works fine on my machine against a SQL2K instance and a later MSDE instance. Even worked ok if I changed compatibility mode to 70. Profiled it and it really doesnt do much:
create table [tblAdvCustomers3] ([cust_id] varchar (8) NOT NULL , [source_cust_id] int NOT NULL , [cust_name] varchar (50) NULL , [address] varchar (100) NULL ) ON [PRIMARY]
exec sp_MSuniquename N'PK_tblAdvCustomers3_', 1
ALTER TABLE [dbo].[tblAdvCustomers3] WITH CHECK ADD CONSTRAINT [PK_tblAdvCustomers3_1__51] PRIMARY KEY CLUSTERED ([cust_id])
So, the only dbo being in the alter - which corresponds with where you're seeing the error, maybe it's creating the object not as dbo but does the alter always using dbo? I'd try profiling to see what you get with SQL7, maybe that will help figure it out. Does it have the latest SP installed? A workaround would be to just execute the alter directly rather than go through DMO - a hack, but would keep you going!
Andy
September 5, 2002 at 3:48 pm
I'd try profiling to see what you get with SQL7, maybe that will help figure it out.
By profiling, do you mean running on SQL7? If so, yes, I have tried running on MSDE1.0 and MSDE2.0 -- both work fine on my machine. I'm not sure about the SP's, can you give me more info on this. Are there SP's for MSDE 1.0?
Did you get a chance to look at the rest of the code I posted showing how I am creating the columns?
Oh, by the way, I have gotten the error on two different tables. One said Line1: Error near '4' and the other said Line1:Error near dbo.
September 5, 2002 at 4:28 pm
By profiling I mean running SQL Profiler to see exactly what sql is being sent from the client machine. Thats how I got the text I posted earlier showing what DMO generated. Have to say I don't know which service packs are available for MSDE, thinking (but could be wrong) that SQL7 service packs apply to MSDE 1 and SQL2K service packs apply to MSDE2. Check the MS web site for more info on that.
Didnt have any problems with your code, it created a table, added columns, pkey. No errors.
Andy
September 11, 2002 at 10:21 am
Andy,
I am still getting the error: Line 1: Incorrect syntax near dbo. I have profiled it and the code generated is as follows: The error occurs on the last line where it appears that two lines are running together. It is supposed to do alter to table tblAdvEmployees to add the primary key constraint. This is the third table I am trying to add the primary key constraint to. The first two seem to be fine (tblAdvCustomers, tblAdvCustomersExt) but when it gets to tblAdvEmployees, the error occurs. Also, it seems to think it should do the alter to tblAdvCustomers??? Do you have any idea what is going on here? (I had to delete the very beginning because the post was too big)
create table [tblAdvEmployees] ([emp_id] varchar (7) NOT NULL , [source_emp_id] int NOT NULL , [fname] varchar (30) NULL , [lname] varchar (30) NULL , [ssn] varchar (15) NULL , [street1] varchar (50) NULL , [city1] varchar (30) NULL , [prov1] varchar (20) NULL , [zip1] varchar (15) NULL , [country1] varchar (30) NULL , [home_phone] varchar (20) NULL , [hired_date] datetime NULL , [listID] varchar (25) NULL , [time_created] varchar (30) NULL , varchar (99) NULL , [salutation] varchar (15) NULL , [edit_sequence] varchar (16) NULL ) ON [PRIMARY]
go
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (case when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.id, N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id, N'TableHasActiveFulltextIndex'), ObjectProperty(o.id, N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id, N'tableisfake')=1) then 1 else 0 end) from sysobjects o, sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and i.indid < 2 and o.name not like N'#%' and o.id = object_id(N'[tblAdvEmployees]') order by s1, s2
go
exec sp_MShelpcolumns N'[dbo].[tblAdvEmployees]'
go
exec sp_MStablekeys N'[dbo].[tblAdvEmployees]'
go
exec sp_MSuniquename N'PK_ƷÆPxꀀ�Pexec sp_MShelpcolumns N'[dbo].[tblAdvCustomers]'_', 1
go
September 11, 2002 at 10:57 am
Is there a foreign key on the table? Email me the entire trace if you have time (as text), I'll look some more.
Andy
September 11, 2002 at 12:20 pm
Andy,
This field name "source_emp_id" does exist in another table, but it is not defined as a foreign key.
I have emailed you the entire trace as a txt file. Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply