I have a webmethod that fails to fire a stored procedure during the first attempt only (I guess while establishing a new connection). I get "Internal error server" as an error Ajax message. However if I hit return from the URL address then the stored procedure is executed and pages work perfectly well, as they should. Then again if I keep the page inactive for a while and then try to establish a new connection the same problem occurs.

I spent the last 2 days trying to identify the problem by checking the parameters, removing most of the codes from the webmethod and finally I was able to trace the problem to this stored procedure.

CREATE PROCEDURE [dbo].[spUnionServices]
@freetext NVARCHAR(50),
@offset int,
@fetch int

AS SET NOCOUNT ON;

BEGIN
SELECT IDphoto AS IDservice, photos.IDuser, photoCaption AS serviceTitle, photoDate as serviceDate, photoFileName AS servicePhoto, 'Photo' AS service, 
    photoPublished as servicePublished, inap, hashtags, KEY_TBL.RANK, screenName AS IDname
    FROM photos INNER JOIN FREETEXTTABLE(photos, (photoCaption), @freetext) AS KEY_TBL ON photos.IDphoto = KEY_TBL.[KEY]
    INNER JOIN editorDetail ON editorDetail.IDuser = photos.IDuser
    ORDER BY RANK DESC OFFSET @offset ROWS FETCH NEXT @fetch ROWS ONLY  
END

And here is how I connect to the stored procedure from the webmethod

StringBuilder SBsmallSquares = new StringBuilder();
SqlConnection sqlConnection1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
           using (sqlConnection1)
           {
               SqlCommand cmd = new SqlCommand();
               SqlDataReader ReaderPopup;
               cmd.CommandText = "spUnionServices";
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Connection = sqlConnection1;
               cmd.Parameters.AddWithValue("@offset", offset);
               cmd.Parameters.AddWithValue("@fetch", fetch);
               cmd.Parameters.AddWithValue("@freetext", fts);

               sqlConnection1.Open();
               ReaderPopup = cmd.ExecuteReader();
               if (ReaderPopup.HasRows)
               {
                   while (ReaderPopup.Read())
                   {
                   //creating the string to return. Here there is no problem.
                   }
                   return SBsmallSquares.ToString(); 
               }
            else return string.Empty;

I would appreciate it if someone could find out why I'm having this problem during the first attempt to run the stored procedure. Thanks

Related posts

Recent Viewed