February 14, 2020 at 12:00 am
Comments posted to this topic are about the item Using SQLCMD Variables
March 20, 2020 at 2:40 pm
That does not match with this example:
and the example is:
:setvar SQLCMDLOGINTIMEOUT 60
:setvar server "testserver"
:connect $(server) -l $(SQLCMDLOGINTIMEOUT)
USE AdventureWorks2012;
SELECT FirstName, LastName
FROM Person.Person;
...there aren't any quotes. Is there a different behavior depending on the SQL version?
March 20, 2020 at 3:29 pm
Good catch. Added the quotes to the SELECT.
That example doesn't need quotes for the connect. However, in selecting a value, it needs to be valid. In this case, the original question has this code executing:
select log.txt, myid, myname, mychar from dbo.MyTable;
Unless there's a column named log.txt, this is invalid. I've added quotes tot the SELECT. In the example you show, there isn't a need for quotes in the first variable, and neither part of the :connect line needs quotes.
March 20, 2020 at 6:25 pm
The question was what should replace xxx in " SELECT 'xxx', ... ".
There are already quotes around the xxx. Why would I include another set of quotes around the SQLCMD variable reference?
You can go ahead and try SELECT ''$(DirectoryR00tPath)'', but I don't think you'll be happy with the result.
March 22, 2020 at 6:58 am
The question was what should replace xxx in " SELECT 'xxx', ... ".
There are already quotes around the xxx. Why would I include another set of quotes around the SQLCMD variable reference?
this.
Having just spent a day writing code using sqlcmd variables (including variables for file/folder names), I answered with confidence, only to be told I was wrong ?!?!?!
This won't even parse:
:setvar DirectoryRootPath "Log.txt"
SELECT ''$(DirectoryRootPath)''
March 22, 2020 at 10:27 pm
I had more time on Sunday, but I didn't believe I'd spend nearly half a day with it. I will try to explain this using the examples below, which I compiled on SQL Server 2014 (SP2-GDR) (KB4505217) - 12.0.5223.6 (X64). I included notes in the script code that helped me understand the correct answer. And here is a link to DOCS "sqlcmd - Use with Scripting Variables".
--========================================
-- Set Query in SQLCMD Mode First --
--========================================
-- Examples according to QotD 2020-03-20:
------------------------------------------
:setvar DirectoryRootPath "C:\Log.txt"
--SELECT $(DirectoryRootPath);-- Error because it's like SELECT C:\Log.txt
SELECT '$(DirectoryRootPath)'; -- O.K. as SELECT 'C:\Log.txt'
GO
:setvar DirectoryRootPath 'C:\Log.txt'
SELECT $(DirectoryRootPath);-- O.K. as SELECT 'C:\Log.txt'
--SELECT '$(DirectoryRootPath)';-- Error because it's like SELECT ''C:\Log.txt''
GO
:setvar DirectoryRootPath C:\Log.txt
--SELECT $(DirectoryRootPath);-- Error because it's like SELECT C:\Log.txt
SELECT '$(DirectoryRootPath)'; -- O.K. as SELECT 'C:\Log.txt'
GO
:setvar DirectoryRootPath """C:\Log.txt"""
--SELECT $(DirectoryRootPath);-- Error Invalid column name 'C:\Log.txt'.
SELECT '$(DirectoryRootPath)'; -- O.K. as SELECT '"C:\Log.txt"'
GO
--====================================================================================
-- Examples according to DOCS:
------------------------------
:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title Ms.
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = '$(title)'-- O.K. as SELECT 'Ms.'
GO
--=================================================================
/*
:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title Ms.
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = $(title)-- Error because it's like SELECT Ms.
GO
*/
--=================================================================
:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title "Ms."
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = '$(title)'-- O.K. as SELECT 'Ms.'
GO
:setvar tablename Person.Person
:setvar col1 FirstName
:setvar col2 LastName
:setvar title 'Ms.'
USE AdventureWorks2014
SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name
FROM $(tablename)
WHERE Title = $(title)-- O.K. as SELECT 'Ms.'
GO
March 25, 2020 at 10:26 am
I totally agree with Scott and Ryan.
The question was not written properly.
I also work a lot with SQLCMD variables and the given answer from this QotD is not correct.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy