%COMPUTERNAME% does not work
-
I was wondering if anyone might know why SQL Agent job with a cmdexec steps fails when using the %COMPUTERNAME% environment variable, without a leading echo statement for the environment variable? Read below for more information.
WHen I run the following command as a cmdexec step in a SQL Agent Job:
osql -E -S%COMPUTERNAME% -Q"select * from sysobjects where name like 'sysa%'"
This step failes with the following error:
SQL Server does not exist or access denied.
Now when I run this same command under MSSSQLSERVER service via QA by issue the following command it works fine:
master.dbo.xp_cmdshell 'osql -E -S %COMPUTERNAME% -Q"select * from sysobjects where name like ''sysa%''"'
Also if I change the SQL Agent cmdexec step to read, it also works fine:
echo %COMPUTERNAME% && osql -E -S %COMPUTERNAME% -Q"select * from sysobjects where name like 'sysa%'"
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Greg,
Sorry, I don't know why. (helpful aren't I
)However, after much grief over the years I've got in the habit of preceding most of my cmdexec steps with "cmd /c".
It works with your example, so I guess it has sort of the same effect as your "echo %computername% &&".
Cheers,
- Mark
Cheers,
- Mark -
Wonder why starting a new shell makes a difference in a SQL Agent job?
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
I hope you are not trying to run this on a cluster ?
Why don't you generate the osql-line in your job and then xp_cmdshell it ?
(select @sqlcmd = 'osql -E -S' + @@servername + ' -Q"select * from sysobjects where name like ''sysa%''"'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No, not running this on a cluster.
Thanks for the solution, that would work, as well as the one provided by mccork.
Really I was wondering if anyone know why the code does not work via SQL Agent, like it does when running it through xp_cmdshell.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
What is it returning when you schedule a job (owned by sa) which runs TSQL:
exec xp_cmdshell 'echo %computername%'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
It returns the computer name on which the command runs. Why are you asking?
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
I'm guessing that the osql isn't evaluating the variable properly. Does it invoke other items? Like osql -smyserver -u%username%?
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
The %username% produces the following error if run through sql agent:
Login failed for user '%username%'.
But works if run through xp_cmdshell.
So basically I'm guessing no environment variables get replaced with there values prior to submitting the command to the query engine via sql agent.
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
That would be my guess as well. As to why, I'm not sure.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
"It returns the computer name on which the command runs. Why are you asking?"
to start on the quest for "why isn't this working".
"So basically I'm guessing no environment variables get replaced with there values prior to submitting the command to the query engine via sql agent."
I can join that conclusion.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Seems to me that the implementation of supporting environment variable replacement with its value should be consistant under both MSSQLSERVER and SQLSERVERAGENT. Once again Microsoft only provide a solution in MSSQLSERVER, and not SQLSERVERAGENT. Think it is worth making a wish on this one. Does anyone see any value here?
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http://www.sqlservercentral.com/bestof/
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
)Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply