CreateTables.sql sintax error problem

Post here for help with installing or upgrading mojoPortal pre-compiled release packages. When posting in this forum, please provide all relevant details. You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

Post here for help with installation of mojoPortal pre-compiled release packages

When posting in this forum, please try to provide as many relevant details as possible. Particularly the following:

What operating system were you running when the bug appeared?
What database platform is your site using?
What version of mojoPortal are you running?
What version of .NET do you use?
What steps are necessary to reproduce the issue? Compare expected results vs actual results.

You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

This thread is closed to new posts. You must sign in to post in the forums.

7/10/2006 7:21:49 AM

Ariel B. Vargas

Total Posts 4

View Posts

CreateTables.sql sintax error problem

Hi, when trying to execute 2CreateTables.sql on MSSQL I receive the following error:
Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '('.

I assume that the error is near 'WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]'

Thanks for your help,
Regards

7/10/2006 7:33:48 AM

Joe Audette

Total Posts 18439

View Posts

Re: CreateTables.sql sintax error problem

You don't mention which version of mojoportal you are using. I just tested the create table procs for both 2.1 and 1.03 and they both ran without errors for me on MS SQL 2005.

What version of MS SQL are you using?

7/10/2006 7:39:42 AM

Ariel B. Vargas

Total Posts 4

View Posts

Re: CreateTables.sql sintax error problem

Hi Joe: Thanks for your quick reply!

I'm using mojoportal 2.1 on MS SQL 2000.

Regards

7/10/2006 9:39:24 AM

Ariel B. Vargas

Total Posts 4

View Posts

Re: CreateTables.sql sintax error problem

Joe: The problem was that my MSSQL 2000 didn't have the SP4 applied. I installed it, and everything worked smoothly.

Thanks for your help!
Ariel

7/10/2006 10:18:08 AM

Joe Audette

Total Posts 18439

View Posts

Re: CreateTables.sql sintax error problem

Excellent! Glad you are up and running.

Cheers,

Joe

7/16/2006 11:04:29 AM

Jan Hussaarts

Total Posts 73

View Posts

Re: CreateTables.sql sintax error problem

Hi all,

I tried to install 2.1 today. I am using SQLServer 2000 and encountered the same problems. I upgraded with SP4, but the problems stay.

When I remove the IGNORE_DUP_KEY = OFF, I get the message that sys.objects is an invalid objectname.

Joe, is the script only usable under SQLServer 2005? Ariel, are you sure you are running 2000 SP4?

7/16/2006 11:23:19 AM

Ariel B. Vargas

Total Posts 4

View Posts

Re: CreateTables.sql sintax error problem

Jan: Joe sent me an updated database creation script created for sql 2000. Besides having installed the SP4, you need a different script.

Kind Regards,
Ariel

7/16/2006 11:46:26 AM

Jan Hussaarts

Total Posts 73

View Posts

Re: CreateTables.sql sintax error problem

OK Ariel,

Can you send me the script for SQLServer 2000. Please send it to hussaarts@planet.nl

Thanks.

Jan

7/16/2006 12:16:29 PM

Joe Audette

Total Posts 18439

View Posts

Re: CreateTables.sql sintax error problem

My bad, I thought after you installed sp4 that the original script worked.

I must have forgot to select the SQL 2000 compatibility option when generating the table script for the release.

I've updated svn branches/2.1.1
and the Novell Forge downloads for 2.1 release and src

with a script that is compatible in bothe MS SQL 2000 and 2005

Joe

7/16/2006 12:45:31 PM

Jan Hussaarts

Total Posts 73

View Posts

Re: CreateTables.sql sintax error problem

Hi Joe,

I downloaded both the new src-zip and the MSSQL release version on Novell Forge.

The scripts are the same; they still won't run under 2000.

Jan

7/17/2006 3:47:04 AM

Joe Audette

Total Posts 18439

View Posts

Re: CreateTables.sql sintax error problem

Hi Jan,

The scripts on Novell forge should be the same as the one I sent to Ariel. I sent it also to you, yesterday, did it work for you?

Thanks,

Joe

7/19/2006 11:23:29 AM

Jan Hussaarts

Total Posts 73

View Posts

Re: CreateTables.sql sintax error problem

Hi Joe,

I did not receive an email from you. Only two noreply messages from Mojo.

The code in Novell Forge contains the following:

<<<<<<<<<<<<<<<<<<<<<<<<

IF NOT EXISTS

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mp_BlogItemCategories]') AND type in (N'U'))

BEGIN

CREATE TABLE

[ID] [int]

[ItemID] [int]

[CategoryID] [int]

[dbo].[mp_BlogItemCategories](IDENTITY(1,1) NOT NULL,NOT NULL,NOT NULL,CONSTRAINT [PK_mp_BlogItemCategories] PRIMARY KEY CLUSTERED

(

[ID]

ASC

)

WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]ON [PRIMARY]

END

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

In my SQLServer 2000 database WITH (IGNORE_DUP_KEY = OFF) gives a problem. You either specifiy IGNORE_DUP_KEY in which case it is ON or you do not specify is (default is OFF). Furthermore SQL2K does not understand sys.objects. It understands sysobjects, but in that situation it gives another error (incorrect columns....????). Than I gave up the modifications.

Regards,

Jan

7/19/2006 2:22:21 PM

Joe Audette

Total Posts 18439

View Posts

Re: CreateTables.sql sintax error problem

Hi Jan,

Can you try and re-apply SP4? The script I sent you is the same one I sent Ariel and it worked for him so it makes me think something went wrong when you applied SP4.

In any case I will probably release version 2.1.1 this weekend. I will make certain I choose SQL 2000 compatibility when I generate the scripts, that is what I did on the script I sent to you and to Ariel.

Joe

7/22/2006 9:42:34 AM

Jan Hussaarts

Total Posts 73

View Posts

Re: CreateTables.sql sintax error problem

Hi Joe,

I think it is good to mention that the scripts did not run under SQLServer 2000. It only went ok after you sent me SQLserver 2000 compliant scripts.

I suggest you add these scripts to the new release.

Best regards,

Jan

7/22/2006 9:47:37 AM

Joe Audette

Total Posts 18439

View Posts

Re: CreateTables.sql sintax error problem

Yes, I will be making the new release later today and it will have compatible scripts.

The problem with the last release was that there was a known bug (though unknown to me at the time) in SQL 2005 RTM that event though I selected SQL 2000 Compatible when generating the scripts, it did not generate scripts that were compatible with SQL 2000. This is fixed in SQL 2005 SP1, it does generate compatible scripts when you select the SQL 2000 compatible option.

So when I generate the scripts for the new release they will be compatible with both SQL 2005 and SQL 2000

Thanks,

Joe

7/25/2006 7:24:00 AM

Tom Opgenorth

Total Posts 26

View Posts

Microsoft MVP in C#

Re: CreateTables.sql sintax error problem

Just curious: have these new scripts been generated and put into SVN yet?

thx.

7/25/2006 7:26:48 AM

Joe Audette

Total Posts 18439

View Posts

Re: CreateTables.sql sintax error problem

yes the scripts in svn branches/2.1.2 (which is the latest dev branch) is compatible with both SQL 2000 and SQL 2005, as is branches/2.1.1

Joe

You must sign in to post in the forums. This thread is closed to new posts.