2

I have a complex stored procedure that queries specific network folders, collects the names of SQL Server backup files it finds there, and then processes each file for details. If I run the procedure from SSMS version 18.4, it gives the wrong results. But if I run it in SSMS version 17.9, the procedure gives me the data I expect to see.

I can recreate the problem with a simpler example:

CREATE OR ALTER PROCEDURE TestOutput (
    @HostName nvarchar(255),
    @DbName nvarchar(255)
    )
AS
BEGIN
    Declare @cmd nvarchar(500);
    Declare @basePath nvarchar(500) = '\\Server1\DB_Backups\SQL\' + @HostName + '\' + @DbName + '\';

    Create table #FullFileList (backupPath nvarchar(255), backupFile NVARCHAR(255)) ;

    SET @cmd = 'DIR /b "' + @basePath + 'Full\"';
    INSERT INTO  #FullFileList (backupFile) EXEC master.sys.xp_cmdshell @cmd;
    update #FullFileList set backupPath = @basePath + 'Full\';

    SET @cmd = 'DIR /b "' + @basePath + 'Diff\"';
    INSERT INTO  #FullFileList (backupFile) EXEC master.sys.xp_cmdshell @cmd 
    update #FullFileList set backupPath = @basePath + 'Diff\' where backupPath is null;     

    SET @cmd = 'DIR /b "' + @basePath + 'Log\"'
    INSERT INTO  #FullFileList (backupFile) EXEC master.sys.xp_cmdshell @cmd;
    update #FullFileList set backupPath = @basePath + 'Log\' where backupPath is null;

    select * from #FullFileList;
    select count(*) from #FullFileList;
END
GO

If I execute this proedure from SSMS version 17.9 against a parent folder (basepath) that contains 1,950 files in the three subfolders, the output is 1,953 rows (three rows with null filenames are in the list, one for each folder). And the count output is 1,953.

\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Full\    Host_Name_DB_Name_FULL_20191230_040407.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Full\    Host_Name_DB_Name_FULL_20200106_040407.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Full\    Host_Name_DB_Name_FULL_20200113_040407.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Full\    NULL
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200103_050507.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200104_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200105_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200107_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200108_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200109_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200110_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200111_050507.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200112_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200114_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200115_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    Host_Name_DB_Name_DIFF_20200116_050506.bak
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Diff\    NULL
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Log\ Host_Name_DB_Name_LOG_20200112_104703.trn
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Log\ Host_Name_DB_Name_LOG_20200112_105003.trn
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Log\ Host_Name_DB_Name_LOG_20200112_105302.trn
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Log\ Host_Name_DB_Name_LOG_20200112_105602.trn
[... Snip ...]
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Log\ Host_Name_DB_Name_LOG_20200116_144402.trn
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Log\ Host_Name_DB_Name_LOG_20200116_144703.trn
\\Server1\DB_Backups\SQL\Host_Name\DB_Name\Log\ NULL

 1953

However, if I execute that same code in SSMS version 18.4, the procedure produces 9 rows of output. It also tells me the count is 22 rows, which is also false: the output was just given; there were only nine rows returned.

\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Full\ WTR320MSDNCM_DBMonitoring_FULL_20191230_040407.bak
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Full\ WTR320MSDNCM_DBMonitoring_FULL_20200106_040407.bak
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Full\ WTR320MSDNCM_DBMonitoring_FULL_20200113_040407.bak
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Full\ NULL
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Log\  WTR320MSDNCM_DBMonitoring_LOG_20200112_104703.trn
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Log\  WTR320MSDNCM_DBMonitoring_LOG_20200112_105003.trn
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Log\  WTR320MSDNCM_DBMonitoring_LOG_20200112_105302.trn
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Log\  WTR320MSDNCM_DBMonitoring_LOG_20200112_105602.trn
\\wtrnas\DB_Backups\SQL\wtr320msdncm\DBMonitoring\Log\  WTR320MSDNCM_DBMonitoring_LOG_20200112_105902.trn

22

(that's all of the rows returned when run from 18.4)

Again, exact same code, same database engine on the back end (Microsoft SQL Server 2017 CU18 Enterprise Edition on Windows Server 2012 R2 Standard), pointed at the same folder structure on Server1. The only difference is the version of SSMS that executes the create procedure command.

Note that SQLCMD gives the correct output as well.

I have tried uninstalling and reinstalling SSMS 18.4. I have also tried switching this to a non-temporary table. The SSMS 18.4 version creates 9 rows of data, not 1953 rows, which I can then view from outside the procedure. I have tried switching what order the folders are processed; this does not change the outcome.

SQL Server has all necessary permissions to browse the three folders in question; it actually wrote those files.

But why is the version of SSMS a factor at all? This should all be executing from the server, which isn't changing here. How can I make this procedure work properly in 18.4, or why is it NOT working correctly?

2
  • 1
    At a guess, some configuration difference between the two SSMS, or on the session (SET ROWCOUNT)? – Michael Green Jan 17 '20 at 2:53
  • Yep. Explicitly doing a "SET ROWCOUNT 0;" in the procedure resolved it. I can't find any setting in SSMS that looks to be at fault here, though. Weird. – CaM Jan 17 '20 at 13:24
4

I see your comment that

Explicitly doing a "SET ROWCOUNT 0;" in the procedure resolved it

You can achieve the same by configuring the menu options in SSMS:

Tools > Options > Query Execution > SET ROWCOUNT: 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.