Incorrect syntax near the keyword 'ON'.

Posts: 4 · Posted: Sun Mar 30, 2008 2:00 pm

I have a CF8 site and my database is MS SQL

I am running a query that is creating database tables.

I receive this error:
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'ON'.

My code has sever scripts similar to this

Code:

ALTER TABLE TBLpage ADD
CONSTRAINT FK_TBLpage_entry FOREIGN KEY
(
id
) REFERENCES TBLentry (
id
) ON DELETE CASCADE ON UPDATE CASCADE

why does MS SQL not like ON DELETE CASCADE ON UPDATE CASCADE??

thanks.

Posts: 65 · Posted: Mon Mar 31, 2008 1:21 pm

I'm not a SQL expert so I am not sure how the syntax should look, but I would try running the query in SQL Management Studio directly to see if you get a better error.

-Dave

Posts: 547 · Posted: Mon Mar 31, 2008 1:29 pm

Try this. It's not much different, but give it a shot and run it in Management Studio:

Code:

ALTER TABLE TBLpage
ADD CONSTRAINT FK_TBLpage_entry FOREIGN KEY (id)
REFERENCES TBLentry (id)
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Posts: 4 · Posted: Mon Mar 31, 2008 2:39 pm

At work, I had our DB administrator run the Script to generate the database tables on a MS SQL 2000 server as well as MS SQL 2005 server. It ran successfully on both.

Seems as though there is some setting issue?? at HostMySite.com??

Any thoughts??

Thanks for the help.
Kelly

Posts: 65 · Posted: Mon Mar 31, 2008 3:18 pm

Well I would guess that your DBA ran it as a server administrator on your local database, so since you don't have server admin access in the shared environment that may be an issue. That being said, you should have DBO access to your database which should give you the ability to do whatever you need.

If you run the query in management studio directly and the error isn't any better, give us a ring and open a support ticket about it and we will be able to look at the settings on the database in depth.

-Dave

Posts: 4 · Posted: Tue Apr 01, 2008 2:08 pm

I put in a support ticket and turns out my database was on a SQL7 server.
No wonder I am having issues.... geez not what I signed up for.

Hopefully the database will be upgraded to SQL2005 without issue or charge.

Posts: 547 · Posted: Tue Apr 01, 2008 2:13 pm

kramm wrote:

I put in a support ticket and turns out my database was on a SQL7 server.
No wonder I am having issues.... geez not what I signed up for.

Hopefully the database will be upgraded to SQL2005 without issue or charge.

Oh geez.. Didn't even know SQL7 was an option anymore. YIKES! Shocked

Posts: 65 · Posted: Tue Apr 01, 2008 2:13 pm

Wow that is really pretty odd, because that hasn't been an option for over 4 years. Can you PM me the ticket number so I can take a look at exactly what happened on our end?

Sorry for the trouble with this!

-Dave