I recently worked on a customer system and installed SQL Server 2008 R2 Express using the Microsoft Web Platform Installer. I then went to run a script we always run to create the database tables and populate them but received a weird error.
Incorrect syntax near ','
Hmmm…this is interesting. We run this script all the time in our environment and we also run it in customer environments. After some testing, I narrowed the issue to an insert statement that inserted multiple rows of data. A sample follows.[sourcecode language=”sql”] INSERT INTO countries (name, code) VALUES
This is a pretty simple statement that normally works fine in SQL Server 2008. Come to find out, the great comment from Rob Nicholson in this forum response pinpointed the source of the issue: My customer wasn’t running the 2008 SQL Server engine even though I installed 2008 R2 from the Web Platform Installer. In fact, the database compatibility level was 90 and only SQL Server 9 was available. Thus, the insert statements will not work.
Looking more closely at the installed SQL Server Express products, I noticed that 2005, 2008 and 2008 R2 products were all installed. I chose only to install 2008 R2 so I’m not sure what happened. In the end, I uninstalled all versions of SQL Server Express and then installed 2008 R2 by itself from http://www.microsoft.com/sqlserver/en/us/editions/express.aspx. This works much better and I have the correct engine so now everything runs fine.
Be careful when using the Web Platform Installer (WPI) as I’ve seen this happen a couple of times. It might be best to install SQL Server Express from the SQL Server Express website instead of using the WPI to avoid this issue.