Ran into a problem today with file system permissions, so I wanted to share with my readers. Long story short, this code worked great when it was targeting a local drive on the server, as it is written here:
-- output new data to report file
DECLARE
@dbColumnHeader VARCHAR(8000),
@dbTable VARCHAR(500)
='GrowthStatistics',
@dbCommand VARCHAR(1000),
@dbFile VARCHAR(255)
SET @dbFile = 'D:\MSSQL\Reports\GrowthStatistics_' +
CONVERT(char(8),GETDATE(),112)
+
'.csv'
SELECT @dbColumnHeader = COALESCE(@dbColumnHeader+','
,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =
@dbTable
SELECT @dbCommand = 'bcp
"SELECT '+
@dbColumnHeader +'
UNION ALL SELECT
DatabaseName,CAST(OriginalSizeMB AS VARCHAR(15)) OriginalSizeMB,CAST(LastWeekMB
AS VARCHAR(15)) LastWeekMB,CAST(NewSizeMB AS VARCHAR(15))
NewSizeMB,CAST(LargerMB AS VARCHAR(15)) LargerMB,[%Growth],[%Reduction] FROM
DBA.dbo.GrowthStatistics" queryout ' + @dbFile + '
-SserverName -c -t, -T '
But, today I needed to change @dbFile to output the file to a UNC, like this:
SET @dbFile = '\\share\path\Reports\GrowthStatistics_' +
CONVERT(char(8),GETDATE(),112)
+
'.csv'
And it failed with both of these errors:
Msg 22051,
Level 16, State 1, Line 0
Attachment file "\\share\path\Reports\GrowthStatistics_20200227.csv" is invalid.
Attachment file "\\share\path\Reports\GrowthStatistics_20200227.csv" is invalid.
SQLState
= S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP
host data-file NULL
Well, there are a number of different reasons for this, but it's basically the ability to read that UNC path... or inability, I should say. 😉 In my case, it was that the SQL Server service account did not have access to that new share we were trying to hit via the UNC path.
The Security Admin indicated that it was setup with the correct permissions when I checked, but it still did not work. A bit of googling and I found a very clever and FAST way to verify SQL Server access to a directory in the file system somewhere. Run this statement from the SQL Server, pointing the DIR to the path that you are trying to hit.
EXEC master..xp_cmdshell 'DIR \\share\path\Reports'
If it is an access problem, you will receive this message:
Went back to my Security admin w/this evidence and and he made another change... and now all is well.
Hope to have helped someone.