sqlcmd mode and r command
Dan Holmes
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?
Erland Sommarskog
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.
--
Erland Sommarskog, SQL Server MVP, esq...@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
Bob
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
> Dan Holmes (dan.h...@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.
>
>
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
Erland Sommarskog
> 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)'
Dan Holmes
> Dan Holmes (dan.h...@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)'
>
>
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
Erland Sommarskog
> 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.