Hi, I have a SQL Procedure that call a SQL Function that (way too often) dies with the above message.
The SP looks like this ALTER PROCEDURE [dbo].[Reportxxxx] @FileId BIGINT , @Id BIGINT , @FunctionToExecute NVARCHAR(200) AS BEGIN DECLARE @UtcTime DATETIME SET @UtcTime = GETUTCDATE() DECLARE @ToUpdate TABLE(ReferenceId BIGINT, ReferenceLastUpdateTime DATETIME, RelatedReferenceId BIGINT, RelatedReferenceTypeId INT) DECLARE @sql NVARCHAR(MAX) SET @sql = N'SELECT * FROM ' + @FunctionToExecute + CONVERT(VARCHAR(20), @FileId) + N', ' + CONVERT(VARCHAR(20), @Id) + N')' INSERT INTO @ToUpdate EXEC sp_executesql @statement = @sql -- the crash is always in the EXEC line. The @FunctionToExecute looks like this: CREATE FUNCTION [dbo].[Validate_xxxx] ( @FileId BIGINT , @Id BIGINT ) RETURNS @Results TABLE(ReferenceId BIGINT, ReferenceLastUpdateTime DATETIME, RelatedReferenceId BIGINT, RelatedReferenceTypeId INT) AS BEGIN INSERT INTO @Results(ReferenceId, ReferenceLastUpdateTime, RelatedReferenceId, RelatedReferenceTypeId) SELECT DISTINCT T.Id, T.LastUpdateTime, T.ContactId, 1 FROM dbo.Data T WITH(NOLOCK) WHERE T.FileId = @FileId AND T.ContactId IS NULL RETURN END GO (I've removed various bits to keep it simple. Basically the function has a quite straight forward SELECT with a NOLOCK and the SP has an insert in a table variable. At the moment I have a failure rate of about 6 out of 10 and the failure. I'm running this on SQL2012 Standard (with which I'm having quite some hard time as it runs some queries much slower than my old SQL2008R2 on much lower hardware specs). Any ideas or clues in how to diagnose and fix this? Thanks, Corneliu
