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