We help IT Professionals succeed at work.

Cannot stop columns output from showing in ms sql result grid

Hi Guys

I'm trying to understand why the function "set nocount on" does not work in my MS SQL stored procedure script called [GetScore], see below

	declare @JSON		varchar	(max )
	declare @cmd		varchar	(100 )
	declare @cmd1		varchar	(8000)
	declare @deletefile varchar	(20	 )

	set @deletefile='LastScore.txt' 
	set @cmd= 'xp_cmdshell ''del "d:\Freak\' + @deletefile + '"''';
	set @cmd1 = ' ren d:\Freak\*.txt LastScore.txt'
        set nocount on
	exec master..xp_cmdshell @cmd1

	waitfor delay '00:00:01';

	select @JSON = BulkColumn from openrowset (bulk 'D:\Freak\LastScore.txt', single_clob) as j

	select * from openjson (@JSON) with 

		LeaderboardName					varchar		(20)
	   ,LeaderboardNameFull				varchar		(20)
	   ,PlayerCount						varchar		(20)
	   ,PlayerName						varchar		(20)
	   ,Score							nvarchar	(20)
	   ,ZombiesKilled					varchar		(15)		'$.ScoreDetails.ZombiesKilled'
	   ,Accuracy						varchar		(50)		'$.ScoreDetails.Accuracy'
	   ,DamageTaken						varchar		(20)		'$.ScoreDetails.DamageTaken'
	   ,SecondsInLevel					varchar		(30)		'$.ScoreDetails.SecondsInLevel'
	   ,Deaths							varchar		(50)		'$.ScoreDetails.Deaths'

	waitfor delay '00:00:01';

        set nocount on
	exec (@cmd)


Open in new window

When I run the stored procedure [GetScore] I get 2 columns called Output, I don't know how to stop these columns from showing up in the result. I have attached a snip of the result after running the stored procedure, see below

Any help is greatly appreciated
Watch Question

Senior Developer
They are the result of the xp_cmdshell call. Use the no_output switch:

exec master..xp_cmdshell @cmd1, no_output

Open in new window


Thanks St5an, that worked!