sql-serverattachmentsql-server-agentsp-send-dbmailsql-job

Attachment file \\SERVER\file.csv is invalid. [SQLSTATE 42000] (Error 22051). The step failed

I've got 4 SQL servers: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012.

In SQL Server 2008 R2; I have created a SQL job that sends me email every Monday with reports from SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 on databases that are older than 3 months and it works just fine.

But, now that I want to add SQL Server 2012, the job fails with the following error:

Executed as user: NT AUTHORITY\SYSTEM. Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Configuration option 'Database Mail XPs' changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457) Attachment file \\ServerName\ShareName\Path\SQL2012_DB.csv is invalid. [SQLSTATE 42000] (Error 22051). The step failed.

If I create the same job on SQL Server 2012 I'm getting that the SQL2008R2_DB.csv is invalid.

Here is the steps from the job on SQL Server 2008 R2:

sp_CONFIGURE 'show advanced', 1
GO

RECONFIGURE
GO

sp_CONFIGURE 'Database Mail XPs', 1
GO

RECONFIGURE
GO

USE msdb
GO

EXEC sp_send_dbmail 
  @profile_name='SQL2008R2',
  @recipients=my.email@mail.com',
  @subject='Old DBs',
  @body='DBs attached in csv',
  @file_attachments='\\ServerName\ShareName\Path\SQL05_DB.csv;\\ServerName\ShareName\Path\SQL2008_DB.csv;\\ServerName\ShareName\Path\SQL2008R2_DB.csv;\\ServerName\ShareName\Path\SQL2008R2_DB.csv'

Is it a permission issue between SQL Server 2008 R2 and SQL Server 2012 that I'm missing or is it something else?

Solution

  • Ok, so the problem was that the SQL service account didn't have access to the shared folder. And that's why I got the error "Attachment file \\ServerName\ShareName\Path\SQL2008R2_DB.csv is invalid."

    Now it works just fine ;)