Thursday, February 27, 2020

Error = [Microsoft][ODBC Driver 13 for SQL Server]Unable to open BCP host data-file

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.

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.



Wednesday, February 26, 2020

SERVERPROPERTY for SQL Server version and build details

Say you have a large inventory of servers, and you need to query them all to confirm version and see what service patching may be needed.  Well here you go.  This is a very simple query that you need to run only once on a CMS to target many servers at the same time.  

Simple statement: 

-- query serveroperty for version and build details

SELECT
  CASE
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'    
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
     WHEN CONVERT(VARCHAR(55), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
     ELSE 'UNKNONWN' END MajorVersion,
   SERVERPROPERTY('ProductLevel') ProductLevel,
   SERVERPROPERTY('Edition') Edition,
   SERVERPROPERTY('ProductVersion') ProductVersion
ORDER BY 1;

Your output will be a little something like this:












Note, you don't need to put a @@SERVERNAME into your SELECT because the CMS will return it back to you from every server registered within the CMS.  If you haven't used the CMS before, please check out the link below.  Definitely makes life easier when you need to run the same statement across multiple servers.  

https://docs.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group?view=sql-server-ver15

Monday, February 3, 2020

A quick look at all database files -- location, size and freespace

This is a very fast one.  Just something I ran today to quickly review all database file details on the server.  Well, not necessarily all details, but the ones that I needed --- location, size and freespace for each.  Fast and easy --> 

IF OBJECT_ID('tempdb..##tempfiles') IS NOT NULL
DROP TABLE ##tempfiles;

CREATE TABLE ##temp
(
    DatabaseName sysname,
    FileName sysname,
    PhysicalFileName nvarchar(500),
    FileSize decimal (18,2),
    FreeSpace decimal (18,2)
)  
EXEC sp_msforeachdb '
USE [?];
INSERT ##temp (DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace)
SELECT DB_NAME(), Name, physical_name, CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) AS NVARCHAR) Size,
 CAST(CAST(ROUND(CAST(size AS DECIMAL) * 8.0/1024.0,2) AS DECIMAL(18,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 AS DECIMAL(18,2)) AS NVARCHAR) FreeSpace
FROM sys.database_files
'
SELECT
       DatabaseName, FileName, PhysicalFileName, FileSize, FreeSpace
FROM ##temp

DROP TABLE ##temp;



Sample output:










You either MUST ignore the C drive location of those, files, or please just remember that it is a bad idea to locate any of your databases on the C drive -- system or otherwise.

They are just there for a fast sample on this code.  Check it out and let me know what you think.

Happy SQL.