Search code examples
sqlsql-serversql-server-2005sqlcmd

Syntax error in SQLCMD SQL script?

I'm currently trying to get a SQL script working for SQL Server 2005. I'm using SQLCMD to execute the script and it fails giving me an "Error: Syntax error at line 7 near command ':r'" error.

The script:

--Main Script--
SET NOCOUNT ON
GO

:on error exit

:r C:\Documents and Settings\ZSmith\My Documents\Scripts\CreateDatabase
:r C:\Documents and Settings\ZSmith\My Documents\Scripts\CreateTables

I can provide the other SQL scripts if needed but they function fine on their own. I'm just trying to automate the entire process.

Solution

  • From what I know, you need to

    • put the SQL file name into double quotes - especially if the path contains spaces!
    • include the .sql extension on the file name

    Try this:

    :r "C:\Documents and Settings\ZSmith\My Documents\Scripts\CreateDatabase.sql"
:r "C:\Documents and Settings\ZSmith\My Documents\Scripts\CreateTables.sql"