I have two retrieval procedures which have stopped working in my calls due to time out exception. I believe this happens in:

CallRetrievalStoredProcedure(storedProcedureToCall As String, parameters As SqlParameter(), tableToFill As DataTable, transactionToUse As ITransaction) As Boolean

I note that both procedures use UDFs internally. When I remove the UDFs the procedure calls work. (They work with them in SQL enterprise manager).

The critical point of my message is that the calls have worked for months. Have I inadvertently changed an LLBLGen Pro setting?

Version 2.5 Final SQL 9.00.3042

Can you manually execute the SPs from any SQL tool (Query Analyzer)?

What do these SPs return?

Both return datatables

Walaa wrote:

Can you manually execute the SPs from any SQL tool (Query Analyzer)?

Yes. I can execute from SQL Enterprise Manager, from VS 2005 IDE

Does the query plan look the same when you execute via LlblGen as it does using enterprise manager?

You should be able to pickup the executed sql by using profiler.

I can see beautiful graphic execution plan and actual execution in Enterprise manager (not sure what I am seeing) when I run it there. I don't know how to get that result to compare when I run it in Gen Pro - if it runs.

you can run SqlProfiler to pick up the sql that get sent by LlblGen. Then copy that and run it in query analyzer to see the plan.

a bit beyond my experience but since your willing to help, I'm willing to stretch brain.

The trace run from SQL Enterprise manager is 14 lines

From Gen Pro = 26000 + before timing out.

the table queried is 75K rows. Not sure what to look for here.

sounds like a bad join

Here is cast of characters. Returns in 2sec in SQL and in gen pro until yesterday

CREATE TABLE [dbo].[tlkZipCodes]( [City] varchar NULL, [State] varchar NULL, [Zip] varchar NULL, [AreaCode] varchar NULL, [FIPS] varchar NULL, [County] varchar NULL, [Zone] varchar NULL, [Latitude] [float] NULL, [Longitude] [float] NULL ) CREATE FUNCTION [dbo].[zipDistance] ( @Lat1 real, @Lon1 real, @Lat2 real, @Lon2 real ) RETURNS real AS BEGIN DECLARE @Result real DECLARE @Theta real DECLARE @Dist real

SET @Theta = @Lon1 - @Lon2
SET @Dist = SIN(dbo.deg2rad(@Lat1)) * SIN(dbo.deg2rad(@Lat2)) + COS(dbo.deg2rad(@Lat1)) * 
    COS(dbo.deg2rad(@Lat2)) * COS(dbo.deg2rad(@Theta))
SET @Dist = acos(@dist)
SET @Dist = dbo.rad2deg(@dist)
SET @Dist = @dist * 60 * 1.1515

SELECT @Result = ROUND(@Dist,0,0)

-- Return the result of the function
RETURN @Result

END

CREATE PROCEDURE [dbo].[usp_DistanceMatrix] ( @Zip varchar(5) = NULL, @Radius int = NULL )

AS SET NOCOUNT ON;

    DECLARE @Lat real
    DECLARE @Lon real

    SET @Lat = (SELECT TOP 1 Latitude FROM dbo.tlkZipCodes WHERE Zip = @Zip)
    SET @Lon = (SELECT TOP 1 Longitude FROM dbo.tlkZipCodes WHERE Zip= @Zip)
    BEGIN

    SELECT DISTINCT [City],[State],[County],[Zone], dbo.ZipDistance(@Lat,@Lon,Latitude,Longitude) AS MILES
    FROM dbo.tlkZipCodes
    WHERE dbo.tlkZipCodes.Zip <> @Zip AND dbo.ZipDistance(@Lat,@Lon,Latitude,Longitude) <= @Radius
    ORDER BY MILES DESC

    END

RETURN

why is it returning a diffrent anount of records when you run it one way vs another?

It doesn't return different record sets. It doesn't return at all! But... since this procedure and one other which uses a udf (to strip phone characters) worked for months I circle back to my first question. Is there a LLBLGen Pro setting I have flipped?

Is there a LLBLGen Pro setting I have flipped?

Not a possibility.

This is outside your charter but are there permissions on UDFs in SQL Server 2005? I wrote classes to create the sql command, connection etc. and it also fails so this can't be an LLBLGen Pro issue. Nonetheless within the SQL enterprise management environment the stored procedure works perfectly

I don't know

UDF's are elements inside the catalog, so you have to have execute permissions on them to use them, similar to procs. Also, if they access tables/views, you've to have the rights required to perform these actions, so if a UDF reads a row from a customer table, the execution connection's user has to have read rights on the customer table.

I have tracked this issue down! The procedures fail to complete if 'Allow SQL/CLR Debugging' is checked in the Visual Studio IDE. When unchecked, all returns to normal. Thank you all for your patience and expertise.

I would consider this thread closed.