September 15, 2009 at 11:45 pm
Hello All,
I am back again with a stupid query. I am running the following script, to add a port in the firewall exceptions. this script is executing successfully but showing result as:
output
1 ok
2 NULL
3 NULL
and message as: Command(s) completed successfully.
I am running the script through sqlcmd.exe and capturing the result into a log which is parsed for further analysis. but the result which is shown above is spoiling the log file. I do not want the result part but need only the message part. Please help me with this.
DECLARE @cmd NVARCHAR(200)
SET NOCOUNT ON
--SET ANSI_NULLS ON
SET @cmd = 'netsh firewall set portopening tcp 1099 SQL_PORT_1099'
EXECUTE xp_cmdshell @cmd
September 16, 2009 at 12:19 am
If I understand your request, add ", no_output" after @cmd..
CEWII
September 16, 2009 at 12:37 am
yes, but the problem with no_output is that even if the @cmd fails for any reason, still the result is shown as 'Command(s) completed successfully'
for e.g. Try the below script [with\with out] no_output option, @cmd is wrong but still executes successfully when no_output option is set.
DECLARE @cmd NVARCHAR(200)
SET NOCOUNT ON
--SET ANSI_NULLS ON
SET @cmd = 'netsh firewall set portopening tcp SQL_PORT_1099'
EXECUTE xp_cmdshell @cmd
September 16, 2009 at 1:40 am
Not sure how you get around that, the other way is to use xp_cmdshell to run a batch file, which contains your commands, and see if that works. you are basically asking for no acknowledgement to come back to sql server
forget I said that, tried that approach and it didnt work
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 16, 2009 at 1:44 am
The only other thing that springs to mind, is run it as a sql job. then you wouldnt have the acknowledgement coming back
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 16, 2009 at 3:07 am
no..actually i need the SQL script to be called from sqlcmd.exe so i have no other option but to use what i said. one solution i found is the capture the integer output of xp_cmdshell into a variable, and then perform based on the output.
DECLARE @cmd NVARCHAR(200),
@res INT
SET NOCOUNT ON
--SET ANSI_NULLS ON
SET @cmd = 'netsh firewall set portopening tcp 1099 SQL_PORT_1099'
EXECUTE @res = xp_cmdshell @cmd,no_output
print @res
@res = 0 if successful
@res = 1 in unsuccessful
Let me know if anyone has tried such scenario and are there any surprises using command like this.
September 17, 2009 at 2:08 pm
why not try using the insert/exec construct to store the results of your xp_cmdshell output in a temp table, delete the nulls and use selects to check your results.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
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