Search code examples
sql-servervariablessqlcmd

sqlcmd scripting variables understanding problem

try to run sqlcmd from powershell with some variables. But get error.

Simple test. I know that this select is really simple but it is sufficient to show the problem.

this is the content of my sql file:

USE [master]
select name from sys.databases  where NAME = $(db)
GO

now i run this: sqlcmd -S testserver -v db="Test" -i \mssql_duplicate\testvariable.sql

i get following message:

Meldung "207", Ebene "16", Status "1", Server "testserver", Zeile 2 "Ungültiger Spaltenname "Test"."

So my question: Why the sqlcmd do this conversion? When i put he name in the sql script it runs fine:

USE [master]
select name from sys.databases  where NAME = "TEST"
GO

sqlcmd -S testserver -i \mssql_duplicate\testvariable.sql

Thanks for your help!

Solution

  • SQLCMD variables can substitute anything, in particular databases' and objects' names. In your example, however, it is used as an ordinary parameter, which is supposed to be of nvarchar(128) data type. So your code should look like this:

    USE [master];
select name from sys.databases  where NAME = N'$(db)';
GO

    Without single quotes, SQL Server interprets your variable as an object name, hence the error.