sqlcmd mode and r command

You can't really mix sqlcmd variables and T-SQL variables in this way. What
you could do, is script the script out to another file and run it, eg

-- Option1: sqlcmd variables only
:setvar filename "c:\temp\temp.sql"
PRINT '$(filename)'
:r $(filename)


-- Option 2: script the script
SET NOCOUNT ON
:out c:\temp\sqlcmd.sql
DECLARE @v VARCHAR (MAX)
SET @v = 'c:\temp\temp.sql'
SELECT ':setvar filename ' + @v + '
PRINT ''$' + '(filename)''
:r $' + '(filename)'
GO
:out STDOUT
GO
:r c:\temp\sqlcmd.sql



-- Option 3: like option 2, but from a table
SET NOCOUNT ON
:out c:\temp\sqlcmd.sql
DECLARE @files TABLE ( filename VARCHAR(MAX) )
INSERT INTO @files SELECT 'c:\temp\temp.sql' UNION SELECT 'c:\temp\temp2.sql'

SELECT ':setvar filename ' + filename + '
PRINT ''$' + '(filename)''
:r $' + '(filename)
GO'
FROM @files
GO
:out STDOUT
GO
:r c:\temp\sqlcmd.sql


SQLCMD mode is wicked, I use it all the time.
HTH
wBob
Rate the post.
"Dan Holmes" wrote:

> I have this small piece of SQL running in sqlcmd mode.
>
> DECLARE @v VARCHAR (MAX)
> SET @v = 'c:\asdf.sql'
> :setvar filename @v
> PRINT $(filename)
> :r $(filename)
>
> I get this error:
> A fatal scripting error occurred.
> The file specified for :r command was not found.
>
> The filename does print in the print command and the file exists. I have seen examples of this syntax here
> http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/ but can't make it work for me.
>
> Anyone know what i am doing wrong?
>
 >> Stay informed about: sqlcmd mode and r command 

Erland Sommarskog wrote:
> Dan Holmes (dan.holmes@routematch.com) writes:
>> I have this small piece of SQL running in sqlcmd mode.
>>
>> DECLARE @v VARCHAR (MAX)
>> SET @v = 'c:\asdf.sql'
>> :setvar filename @v
>> PRINT $(filename)
>> :r $(filename)
>>
>> I get this error:
>> A fatal scripting error occurred.
>> The file specified for :r command was not found.
>>
>> The filename does print in the print command and the file exists. I
>> have seen examples of this syntax here
>> http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/ but can't
>> make it work for me.
>>
>> Anyone know what i am doing wrong?
>
> The value of $(filename) is "@v", not whatever value the T-SQL variable
> that @v might have. Keep in mind that SQLCMD is a client tool, and SQL
> Server is a server application. Furthermore, T-SQL variables are local
> to a batch, so there is no way that SQLCMD can retrieve the value of @v
> to assign it to one of its own variables.
>
>
I very much want to agree with you but running this:

DECLARE @v VARCHAR (MAX)
SET @v = 'c:\asdf.sql'
:setvar filename @v
PRINT $(filename)

on my system will print 'c:\asdf.sql'

would you try that on yours? I have tried with SSMS 2005 and 2008. Both print the value in @v from the sqlcmd variable
$(filename)

danny
 >> Stay informed about: sqlcmd mode and r command 

Dan Holmes (dan.holmes@routematch.com) writes:
> I very much want to agree with you but running this:
>
> DECLARE @v VARCHAR (MAX)
> SET @v = 'c:\asdf.sql'
>:setvar filename @v
> PRINT $(filename)
>
> on my system will print 'c:\asdf.sql'

Of course it does. That's the value of @v. But try this:

DECLARE @v VARCHAR (MAX)
SET @v = 'c:\asdf.sql'
:setvar filename @v
PRINT '$(filename)'

or

DECLARE @v VARCHAR (MAX)
SET @v = 'c:\asdf.sql'
:setvar filename @v
go
PRINT '$(filename)'


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: sqlcmd mode and r command 

Erland Sommarskog wrote:
> Dan Holmes (dan.holmes@routematch.com) writes:
>> I very much want to agree with you but running this:
>>
>> DECLARE @v VARCHAR (MAX)
>> SET @v = 'c:\asdf.sql'
>> :setvar filename @v
>> PRINT $(filename)
>>
>> on my system will print 'c:\asdf.sql'
>
> Of course it does. That's the value of @v. But try this:
>
> DECLARE @v VARCHAR (MAX)
> SET @v = 'c:\asdf.sql'
> :setvar filename @v
> PRINT '$(filename)'
>
> or
>
> DECLARE @v VARCHAR (MAX)
> SET @v = 'c:\asdf.sql'
> :setvar filename @v
> go
> PRINT '$(filename)'
>
>
Now i am even more confused. Why does PRINT '$(filename)' and PRINT $(filename) produce different outputs? I get the
value of @v in one and '@v' in the other? And if i can get $(filename) to print the value of @v why won't :r use that
value when running?

Are there better resources than BOL for this?

danny
 >> Stay informed about: sqlcmd mode and r command 

Dan Holmes (dan.holmes@routematch.com) writes:
> Now i am even more confused. Why does PRINT '$(filename)' and PRINT
> $(filename) produce different outputs? I get the value of @v in one and
> '@v' in the other? And if i can get $(filename) to print the value of
> @v why won't :r use that value when running?

It's nothing strange at all.

PRINT @v

prints the value of @v

PRINT '@v'

prints the string '@v'.

In SQLCMD you have defined the variable filename to have the value '@v'.
Then you say:

PRINT $(filename)

SQLCMD reads this line, and recognizes that $(filename) is an SQLCMD
variable, and replace it with its value before it sends the string to
SQL Server. SQL Server sees this:

DECLARE @v VARCHAR (MAX)
SET @v = 'c:\asdf.sql'
PRINT @v

It's important to understand that the order of execution here does not
agree with the text you see. You had:

DECLARE @v VARCHAR (MAX)
SET @v = 'c:\asdf.sql'
:setvar filename @v
PRINT $(filename)

But the actual execution order is:

:setvar filename @v

DECLARE @v VARCHAR (MAX)
SET @v = 'c:\asdf.sql'
PRINT $(filename)

And more in detail this happens:

1) SQLCMD parses the command text, looking for
a) go
b) it's own commands, those starting with :
c) Variables in $() to expand.
2) SQL performs commands and expansions as it finds them. When it reaches
go it sends the text to SQL Server.
3) SQL Server parses the batch, to check syntax and that.
4) SQL Server executes the batch.
5) SQLCMD gets the result back and prints the result.

--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: sqlcmd mode and r command