0

I have a new AlwaysOn environment where we've moved an old SQL Server 2008 replication databases. I have replication working between the two environments and when I do a manual failover it works wonderfully.

The problem that I'm having with this is that the application cannot execute the stored procedure. We're tested both the SQL Server 2008 as well as the new SQL Server 2012 edition. The stored procedure works as it has been for years and nothing has changed on it.

I've also did another test while running the application job to create the document and used profiler to find the differences in the two. What I've found is that within the first SQL Server 2008 environment is that I see where the stored procedure is getting executed, as well as the results that getting stored in the tables. With the SQL Server 2012 Profiler results I don't see the same stored procedure execution. More on those line the only thing that I really see is where the statement should be executing. At the end of the profiler I do however see where a statement, that get by mail, executes and stats that "null values are no allowed in a column where nulls are not allow" . This is a true statement. The column does not allow null values. I have double checked the permission and everything matches up.

So my question is why can't the application not execute the stored procedure?

Error is:

Here is the error that we are getting with this process. Error: Error: PS Auto Changes Process Model. Service Task failed PS Auto Changes,version 60 : Instance ID 37486 Review Change node : StoredProcedureServiceTask, SQL Server Exception: Cannot insert the value NULL into column 'jobID', table 'Docfinity10_Reporting.dbo.TFBAudit_BPMJobInfo'; column does not allow nulls. INSERT fails.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
SQL Shell
  • 1
  • Welcome to the [so] site. Your question appears unclear and is collecting votes for closing. Try including more data about execution request and its results. Please see [ask] and then [edit] the question to improve it. – miroxlav Feb 12 '17 at 09:41
  • Firstly, what is the symptom? Returns an error? Doesn't change data as expected? – Nick.Mc Feb 12 '17 at 12:34
  • Here is the error that we are getting with this process. Error: Error: PS Auto Changes Process Model. Service Task failed PS Auto Changes,version 60 : Instance ID 37486 Review Change node : StoredProcedureServiceTask, SQL Server Exception: Cannot insert the value NULL into column 'jobID', table 'Docfinity10_Reporting.dbo.TFBAudit_BPMJobInfo'; column does not allow nulls. INSERT fails. – SQL Shell Feb 13 '17 at 21:17
  • inspect the stored procedure and debug it. The procedure is executing but failing mid execution. more likely you are missing a dependency, probably a second database used inside the proc. – Ricardo C Feb 14 '17 at 15:40
  • I have edited your question and added the error. The error says you are inserting a record which has a null value for `jobID`. Without code or any other info we can't help further. – Nick.Mc Feb 16 '17 at 13:35

0 Answers0