I have a stored procedure that is called from a SQL Server job.

The following parameters are passed from the job to the stored procedure, to look in a share with multiple subfolders with files within them. in a 2008 server vs a 2016 server.

This one fails and returned the Type as the file name type\filename

EXECUTE StoredProcedure '\\Server2016\Public\"File Uploads"', uploadfilelist, '%.%', NULL, 1

This one works fine.

EXECUTE StoredProcedure '\\Server2008\Public\"File Uploads"', uploadfilelist, '%.%', NULL, 1
  • When the procedure is run against Windows Server 2008, it works fine
  • When the procedure is run again Windows Server 2016, it fails to return the file type, it's blank. The file type is then merged into the file name.

A sample of the output from both looks like this, results from the Windows Server 2008 table:

Filelist - Type - filename - file_ext\server2008\Public\File Uploads\subfolder1\filename.pdf - subfolder1 - filename - pdf

Results from the Windows Server 2016 table:

Filelist - Type - filename - file_ext\server2008\Public\File Uploads\subfolder1\filename.pdf - no data - subfolder1\filename - pdf

I know what your saying, leave the shared folder on the 2008 box and call it a weekend. Yes that will work but the 2016 server is FAST! and has more space.

Also note that nothing changes with regards to SQL Server - it's SQL Server 2017 on another box.

What the procedure is doing is taking the file path, the type, the file name and the file ext and placing that data in a SQL Server database table, as a reference.

This is what the stored procedure looks like.

SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[StoredProcedure]@PCWrite VARCHAR(2000),@DBTable VARCHAR(100)=NULL,@PCIntra VARCHAR(100)=NULL,@PCExtra VARCHAR(100)=NULL,@DBUltra BIT=0ASSET NOCOUNT ONDECLARE @Return INT DECLARE @Retain INT DECLARE @Status INT SET @Status=0DECLARE @Task VARCHAR(2000)DECLARE @Work VARCHAR(2000)DECLARE @Wish VARCHAR(2000)--SET @Work='"' + 'DIR /s ' + @PCWrite + '"'SET @Work='"' + 'DIR /b /s ' + @PCWrite + '"'--PRINT @WorkEXEC sp_xp_cmdshell_proxy_account 'Administrator\admin' , ‘password' DROP TABLE IF EXISTS #DBAZCREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))INSERT #DBAZ EXECUTE @Return=master.dbo.xp_cmdshell @WorkSELECT *FROM #DBAZSET @Retain=@@ERROR--IF @Status=0 SET @Status=@RetainIF @Status=0 SET @Status=@ReturnIF (SELECT COUNT(*) FROM #DBAZ) < 4BEGINSELECT @Wish=Name FROM #DBAZ WHERE Work=1IF @Wish IS NULLBEGINRAISERROR ('General error [%d]',16,1,@Status)ENDELSEBEGINRAISERROR (@Wish,16,1)ENDENDELSEBEGIN--DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10))=0 OR SUBSTRING--(Name,40,1)='.' OR Name LIKE '%.lnk'IF @DBTable IS NULLBEGINSELECT SUBSTRING(Name, 40, 100) AS FilesFROM #DBAZWHERE 0=0AND (@DBUltra=0 OR Name LIKE '% %')AND (@DBUltra !=0 OR Name NOT LIKE '% %')AND (@PCIntra IS NULL OR SUBSTRING(Name, 40, 100) LIKE @PCIntra)AND (@PCExtra IS NULL OR SUBSTRING(Name, 40, 100) NOT LIKE @PCExtra)ORDER BY 1ENDELSE-- this is where the db table gets updated. Need to delete all data first.BEGIN-- delete the data in the tableSET @Task=' DELETE FROM ' + REPLACE(@DBTable,CHAR(32),CHAR(95))IF @Status=0 EXECUTE (@Task) SET @Return=@@ERRORIF @Status=0 SET @Status=@Return-- insert the new valuesSET @Task=' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))+ ' SELECT Name AS filelist, '+ 'SUBSTRING(Name,34, CHARINDEX(' + CHAR(39) + '\' + CHAR(39) + ', Name, 34) - 34 ) as type, '--+ '400 as type, '+ 'SUBSTRING(Name, CHARINDEX(' + CHAR(39) + '\' + CHAR(39) + ', Name, 34) + 1, CHARINDEX(' + CHAR(39) + '.' + CHAR(39) + ', Name) - CHARINDEX(' + CHAR(39) + '\' + CHAR(39) + ', Name, 34) -1) as filename, '--+ '300 as filename, '+ 'RIGHT(Name, 3) as file_ext'--+ ' SELECT SUBSTRING(Name,40,100) AS Files' – ORIGINAL+ ' FROM #DBAZ'+ ' WHERE 0=0'+ CASE WHEN @DBUltra=0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '% %' + CHAR(39) END + CASE WHEN @DBUltra !=0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '% %' + CHAR(39) END+ CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name, 40, 100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END+ CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING (Name, 40, 100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END+ ' ORDER BY 1'IF @Status=0 EXECUTE (@Task) SET @Return=@@ERRORIF @Status=0 SET @Status=@ReturnENDENDDROP TABLE #DBAZSET NOCOUNT OFFRETURN (@Status)

There may be a much easier way of passing the file locations to a database.

Open to suggestions.

Thanks for your time!

    Your Answer

     

    By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

    Browse other questions tagged or ask your own question.