What is wrong with this script? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What is wrong with this script?

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-03-30 : 15:17:45

I run this script on SQL 9.0 and it works fine but then when I execute the same script on SQL 8.0 it gives me error:

Here is the script:

CREATE TABLE [dbo].[Nop_CustomerSession](
[CustomerSessionGUID] [uniqueidentifier] NOT NULL,
[CustomerID] [int] NOT NULL,
[LastAccessed] [datetime] NOT NULL,
[IsExpired] [bit] NOT NULL,
CONSTRAINT [PK_Nop_CustomerSession] PRIMARY KEY CLUSTERED
(
[CustomerSessionGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

And here is the error:

Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '('.

How can I make it work on SQL 8.0 without any problems?

Mohammad Azam
www.azamsharp.net

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-30 : 15:22:26

[code]CREATE TABLE [dbo].[Nop_CustomerSession](
[CustomerSessionGUID] [uniqueidentifier] NOT NULL,
[CustomerID] [int] NOT NULL,
[LastAccessed] [datetime] NOT NULL,
[IsExpired] [bit] NOT NULL,
CONSTRAINT [PK_Nop_CustomerSession] PRIMARY KEY CLUSTERED
(
[CustomerSessionGUID] ASC
)
) ON [PRIMARY]
GO[/code]

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-03-30 : 15:26:10

@russell,

So that means the WITH keyword is a new one which is not supported in the older version! Am I correct?

Mohammad Azam
www.azamsharp.net

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-30 : 16:15:04

close. the () aren't supported. nor are some of the options. you could still keep the fillfactor

CREATE TABLE [dbo].[Nop_CustomerSession](
	[CustomerSessionGUID] [uniqueidentifier] NOT NULL,
	[CustomerID] [int] NOT NULL,
	[LastAccessed] [datetime] NOT NULL,
	[IsExpired] [bit] NOT NULL,
	CONSTRAINT [PK_Nop_CustomerSession] PRIMARY KEY CLUSTERED 
	(
		[CustomerSessionGUID] ASC
	) WITH FILLFACTOR = 80 ON [PRIMARY]
)ON [PRIMARY]
GO

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-03-30 : 16:17:35

Another issue if someone can help me out: The following script is giving invalid syntax or something:

CREATE TABLE [dbo].[Nop_Setting](
[SettingID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Value] [nvarchar](2000) NOT NULL,
[Description] [ntext] NOT NULL,
CONSTRAINT [PK_Nop_Setting] PRIMARY KEY CLUSTERED
(
[SettingID] ASC
)
CONSTRAINT [IX_Nop_Setting] UNIQUE NONCLUSTERED
(
[Name] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The error is:
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CONSTRAINT'.

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2010-03-30 : 16:19:57

This might be the last one:

CREATE PROCEDURE [dbo].[Nop_LanguagePackExport]
(
@LanguageID int,
@XmlPackage xml output
)
AS
BEGIN
SET NOCOUNT ON
SET @XmlPackage =
(
SELECT l.Name as '@Name',
(
SELECT
lsr.ResourceName AS '@Name',
lsr.ResourceValue AS 'Value'
FROM
Nop_LocaleStringResource lsr
WHERE
lsr.LanguageID = l.LanguageID
ORDER BY
lsr.ResourceName
FOR
XML PATH('LocaleResource'), TYPE
),
(
SELECT
mt.Name AS '@Name',
mtl.Subject AS 'Subject',
mtl.Body AS 'Body'
FROM
Nop_MessageTemplateLocalized mtl
INNER JOIN
Nop_MessageTemplate mt
ON
mt.MessageTemplateID = mtl.MessageTemplateID
WHERE
mtl.LanguageID = l.LanguageID
FOR
XML PATH('MessageTemplate'), TYPE
)
FROM
Nop_Language l
WHERE
LanguageID = @LanguageID
FOR
XML PATH('Language')
)
END
GO

is giving the error:
Msg 156, Level 15, State 1, Procedure Nop_LanguagePackExport, Line 23
Incorrect syntax near the keyword 'FOR'.

Mohammad Azam
www.azamsharp.net

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-30 : 18:11:36

The error is:
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CONSTRAINT'.

You are missing a comma between the two constraint clauses.

=======================================
There are no passengers on spaceship earth. We are all crew. -Marshall McLuhan, educator and philosopher (1911-1980)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-30 : 23:27:07

FOR XML PATH is not available in 80 compatibility mode.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources