February 9, 2007 at 3:15 pm
Hello,
I created a table creation script in SQL Server 2005 Management Studio by right-clicking on my database's name and selecting Tasks, Generate Scripts... and using the wizard. I selected some options including Script for Server Version SQL Server 2000.
When I ran the script in SQL 2000, however, I got this error:
Server: Msg 170, Level 15, State 1, Line 38
Line 38: Incorrect syntax near '('.
Has anyone else seen this error? I figure I can tinker with the script until it works, but I want to know if this is a known issue with an obvious fix. It seems wrong that SQL Server is generating scripts that trigger syntax errors where no obvious ones seem to exist.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 10, 2007 at 5:13 am
It would help if you could post the script it created.
David
February 11, 2007 at 6:18 pm
February 12, 2007 at 12:18 am
Hi,
When you look to the script generated , i believe you can find this statement :"WITH (IGNORE_DUP_KEY = OFF)".
Just remove it and you will get the script working.
I am not sure of the reason, will be clearing it shortly.
Regards,
Gaurav
February 12, 2007 at 1:37 pm
You're right, it's probably the WITH options. SQL Server 2005 syntax is to use brackets around the list of WITH options, while 2000 simply lists them directly. For example, your primary key definition could define the fillfactor as
SQL Server 2000:
WITH FILLFACTOR = 90
SQL Server 2005:
WITH (FILLFACTOR = 90)
This is one of the known backward incompatibilities.
Rick
townsends.ca
February 12, 2007 at 2:41 pm
I would like to post the whole script, but I'm not sure I'm allowed to, given it is a database for a product we've licensed from a vendor.
The script does have this section near the end though, which sounds consistent with another poster who mentioned the WITH clause -- in my case it was WITH (IGNORE_DUP_KEY = OFF):
CONSTRAINT [PK_COURSES] PRIMARY KEY CLUSTERED
(
[COURSE_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Is this a bug of some kind? I don't know why SQL Server is producing code that throws syntax errors. I can understand that just taking a script and running it doesn't guarantee that the target database will run it cleanly, but I'm surprised that the issue is what appears to be a basic unmatched parenthesis.
What is the current level of service packs or patches that SQL 2005 should be at? I just want to check that a missing patch isn't the issue.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 12, 2007 at 4:17 pm
Rick is correct bit I believe this problem was fixed in SP1 although I am using the CTP of SP2 at the moment on my dev box so it might SP2 that fixes it.
Anyway mine no longer incorrectly creates the script on the test box but does on my un-service packed production box
Also if you are going to apply SP1 make sure and read some of thge posts about the issues it can cause, especially with SSIS hence the reason it never made it onto my production box. I would personally wait for SP2 as the CTP of this have been far better in quality in my opinion than SP1
hth
David
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply