I'm trying to get a single value (row count) and write it to a text file.
I'm trying to get the row count value into the variable @spcount and then output it using this code:
set @cmd = 'master..xp_cmdshell "echo Record Count:' + @spcount + '>> d:\file.txt"'
Unfortunately, I can't seem to pass the row count value via the @spcount variable to the file, I always get a value of 0
the code I'm trying to use to assign the value is:
exec @spcount=xdb..sp_executesql N'select count(*) from xtable'
another variation of that line:
exec xdb..sp_executesql N'select count(*) from xtable'
run by themselves, I get the correct output, but when trying to embed the results into the code above, again I get 0