Reputation: 13068

incorrect syntax near ':' in SQL

I am using SQL Server Management Studio to run a script on SQL Server 2008. Following simple statement throws error "Incorrect syntax near ':'".

:setvar DatabaseName "USHR1.2"

Curious, what's going on here. Appreciate your help!

Upvotes: 3

Answers (5)

Reputation: 31353

The SQL being executed could be meant for an Oracle database. The colon(:) in an Oracle statement indicates a "bind variable".

Below is example Dapper query with a parameter. Notice the SQL has different syntax depending on if the database is SQL Server or Oracle.

SQL Server: select * from Thing where Name = @Name

Oracle: select * from Thing where Name = :Name

Upvotes: 0

Reputation: 83

Upvotes: 3

Reputation: 19479

use TSQL....

DECLARE @databaseName VARCHAR(7)
SET @databaseName = 'USHR1.2'

Upvotes: 5

Reputation: 13068

OK.. it was simple. Enabling the "SQLCMD Mode" from the SSMS menu solved the error. Thanks everyone for your replies.

I guess, there are tons of such "Incorrect syntax near 'x'" errors reported on stackoverflow. Guess, those will go away with this fix.

Upvotes: 11

Reputation: 100308

Or use ' instead of "

Upvotes: 1