SQL Agent Job – SSIS Integration package which uses VFPOLEDB and SQL 2012 fails

13-Dec-1212 Leave a comment

Various error messages received including:

Description: ADO NET Source has failed to acquire the connection DataReaderSrc with the following error message: “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”

Solution:

  1. Create a SQL Credential
    1. SSMS > Security > Credentials
  2. Create a SQL Server Agent Proxy
    1. SSMS > SQL Server Agent > Proxies > SSIS Package Execution > New Proxy
  3. Return to job step in question > Run as > new proxy
  4. Execution options > Use 32-bit tick

Filed under SQL Configuration, SQL Server, SQL SSIS, Troubleshooting Tagged with ,

Workaround for SQL Server 2012 64-bit connection to 32-bit VFPOLEDB – Stored Procedure and SQL Agent

13-Dec-1212 Leave a comment

In SQL Server 2005 32-bit > Linked Servers > Providers > VFPOLEDB was present, (with in process ticked)
This provider was not available in SQL Server 2012 64-bit

Workaround: Running a stored procedure from SQL 2012 back to to SQL 2005

  1. Create a linked server to the SQL Server 2005 32-bit. RPC = Remote Procedure Call. You need to set RPC Out to True. Possibly RPC as well.
  2. Then use/move/write queries in SQL 2005 and call them from SQL 2012 procedures using:
  3. EXECUTE [<SQL2005 server name>].[Database name].[Schema].[procedure]
  4. May need to add login to SQL 2005 and grant permission

Workaround: SQL Agent jobs that call procedures still not working

Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider “VFPOLEDB” for linked server “”. [SQLSTATE 42000] (Error 7303)  OLE DB provider “VFPOLEDB” for linked server “” returned message “Invalid path or file name.”. [SQLSTATE 01000] (Error 7412).  The step failed.

  1. Change the job type from Transact SQL to Operating System (CmdExec)
  2. Command = osql -E -Q “EXEC <sql 2012 db name>.dbo.<sp name>”

Filed under Errors, SQL Configuration, SQL Server, Troubleshooting Tagged with ,

SQL Server Operators – SQL Agent Alert System – Notify Operators

24-Nov-1111 Leave a comment

After adding SQL Operators you need to restart SQL Agent

ERROR in SQL Agent Jobs
… NOTE: Failed to notify ‘<operator name>’ via email.

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to
[475] Database Mail is not enabled for agent notifications.

May have log in SSMS > SQL Server Agent > Error Logs

SOLVED
Credit to:
http://sqlblog.jfperry.com/jpsql/post/2009/04/07/NOTE-Failed-to-notify-7boperator7d-via-email.aspx

This was set up on one of my work servers. For a different company this needed to be set up.

On SQL 2005+ you have to tell SQL Server Agent / Properties / Alert System, which profile that you’d like to enable. Here’s what it does.
e.g. and don’t forget to restart SQL Agent

To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.
  2. Right-click SQL Server Agent, and then click Properties.
  3. Click Alert System.
  4. Select Enable Mail Profile.
  5. In the Mail system list, choose Database Mail.
  6. In the Mail profile list, select a mail profile for Database Mail.

Or less likely Transact SQL

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'MyDatabaseProfile'
GO

Filed under Errors, SQL Configuration, SQL Server Tagged with ,

SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

29-Sep-1111 Leave a comment

ERROR:
SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

SOLUTION
Add the Database Name into the @query

BACKGROUND
This may be because of the default database of the account which executes the SQL Agent job.

EXAMPLE
If @query does not include the database name (three-part naming), it is possible that although the EXISTS may fire, the @query does not.
To solve add the Database Name into the @query.

BEGIN TRY

IF EXISTS(SELECT * FROM Production.vAuditSOCDBTable)
  BEGIN
   DECLARE @MyBody nvarchar(100)
   SET @MyBody = ‘<message>’ + char(13) + char(10)

   EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘<>’,
    @recipients = ”,
    @blind_copy_recipients = ”,
    @query = ‘SELECT * FROM <databasename>..’,
    –or possibly @execute_query_database = ‘databasename’,
    @subject = ‘subject’,
    –@attach_query_result_as_file = 1 ,
    @body = @MyBody;

   PRINT ‘message’
  END
 ELSE
  PRINT ‘nothing in audit’

END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]
 PRINT ‘FAILED’
END CATCH

Filed under Errors, SQL Server Tagged with , ,

SQL Server Agent will not start

06-Feb-1111 Leave a comment

Believe this problem is because I had SQL Server 2008 on my machine and then added SQL Server 2008 R2. Today I had renamed the old MSSQL10 directories with a zz at front to mark them as old. Then SQL Server Agent did not start.
First I had:
Error message when you use SQL Server Configuration Manager to restart the SQL Server Agent service in SQL Server 2008:
“The request failed or the service did not respond in a timely fashion”
http://support.microsoft.com/kb/955494
Suggested using Start > Administrative Tools > Services or Services.msc and start from there.

This led to next problem
The SQL Server Agent (MSSQLSERVER) service on Local Computer started and then stopped.  Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/12/unable-to-start-sql-server-agent.aspx
Found that the registry entry
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent
ErrorLogFile key was pointing at a location which was 2008 not 2008 R2
Chnaged the key value and SQL Server Agent started OK

SOLVED

Filed under Errors, SQL Configuration, SQL Server Tagged with