locked
Stored Procedure issue RRS feed

  • Question

  • User411346422 posted

    I'm not sure if this is the correct sub-forum, so apologies if that's the case.

    Using SQL Server 2014, Visual Studio 2015 and IIS 8.0.9

    My web application works perfectly when launched in VS, but when deployed to IIS one particular page returns a 'Could not find stored procedure' error message.

    The web page contains a drop down box and a start and end date textboxes. User selects hospital from drop down and enter start and end date criteria, clicks on a button and the results are returned in a gridview control. As I said, this functions perfectly in VS.

    When I login to my IIS server, open IIS, navigate to my application and select 'browse' from the options and try to navigate to this particular web page, the error is generated. All other functionality works as expected. The database itself was migrated from SQL server 2008 to SQL Server 2014. All the functionality that works was developed in 2008, but the stored procedure that's giving me problems was developed in 2014. I'm not sure if this is an issue or not.

    What I've tried:

    1. Have checked spelling of stored procedure and it's correct
    2. Have checked login credentials of database user in my connection string by logging in to SSMS using same credentials and executing stored procedure. Can do both both.
    3. Checked user permissions to stored procedure. All stored procedures have same permissions for the user.
    4. Have checked that login credentials match those in IIS. They do.
    5. Have run a SQL Profiler trace. It doesn't find any issues that I can see
    6. Have deleted and re-created the stored procedure a number of times. No change
    7. Have removed the application from IIS and recreated. No change
    8. Have installed on a server running a different version of IIS (6.x). I get the same error

    I'm not sure but I think the the logon credentials to the database used in VS and those in IIS must be different. How do I find out what credentials IIS is using and how to do I configure the App on my IIS server so that the database accepts these credentials?

    Of course, it could be something entirely different, but I'm shooting in the dark here.

    Can anyone help? Code is below.

    @HospitialID int = Null,
    @StartSamplingDate Datetime = Null,
    @EndSamplingDate Datetime = Null
    	
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    	-- Insert statements for procedure here
    	
    	SELECT DNAScreeningID, Surname, Forename, DoB,
    	T21DNAResult, T18DNAResult, T13DNAResult, SamplingDate
    	
    	FROM tblPatients
    	
    	WHERE (@HospitialID IS NULL OR HospitalID = @HospitialID)
    	
    	AND ((@StartSamplingDate IS NULL) OR SamplingDate >= @StartSamplingDate)
    	AND ((@EndSamplingDate IS NULL) OR SamplingDate <= @EndSamplingDate)
    	
    	ORDER BY DNAScreeningID ASC
    END
    
    Public Shared Function GetPatientList(ByVal HospitalCriteria As HospitalCriteria) As PatientCollection
            Dim templist As New PatientCollection()
            Using myConnection As New SqlConnection(AppConfiguration.ConnectionString)
    
                Using myCommand As New SqlCommand("SPAllPatients", myConnection)
    
                    myCommand.CommandType = CommandType.StoredProcedure
    
                    myCommand.Parameters.Clear()
    
                    '@HospitalID
                    If (HospitalCriteria.HospitalID) = 0 Then
                        myCommand.Parameters.AddWithValue("@HospitalID", DBNull.Value)
                    Else
                        myCommand.Parameters.AddWithValue("@HospitalID", HospitalCriteria.HospitalID)
                    End If
    
                    '@StartSamplingDate
                    If (HospitalCriteria.StartSamplingDate) = DateTime.MinValue Then
                        myCommand.Parameters.AddWithValue("@StartSamplingDate", DBNull.Value)
                    Else
                        myCommand.Parameters.AddWithValue("@StartSamplingDate", HospitalCriteria.StartSamplingDate)
                    End If
    
                    '@EndSamplingDate
                    If (HospitalCriteria.EndSamplingDate) = DateTime.MinValue Then
                        myCommand.Parameters.AddWithValue("@EndSamplingDate", DBNull.Value)
                    Else
                        myCommand.Parameters.AddWithValue("@EndSamplingDate", HospitalCriteria.EndSamplingDate)
                    End If
    
                    myConnection.Open()
                    Using myReader As SqlDataReader = myCommand.ExecuteReader
    
                        If myReader.HasRows Then
                            templist = New PatientCollection()
                            While myReader.Read
                                templist.Add(FillDataRecordAll(myReader))
                            End While
                        End If
                    End Using
    
                End Using
                myConnection.Close()
    
            End Using
    
            Return templist
        End Function



    Monday, September 17, 2018 7:53 AM

Answers

  • User411346422 posted

    Right - this is very strange. The connection string was pointing to the SQL Server using the Server name. I changed this to the IP address of the machine and hey presto it works!!

    Why this should be I don't know when other stored procedures were quite happy with the Server name. Perhaps someone has an idea?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2018 11:34 AM
  • User475983607 posted

    Flush DNS on the server.  However, I would assume it would have invalidated by now.  Open the command prompt and enter.

    ipconfig /flushdns

    Otherwise, talk with your system admin. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2018 11:43 AM

All replies

  • User475983607 posted

    The error is pretty clear.  The stored procedure is not found.   Unfortunately, you have no shown the most important part of the stored procedure which is the procedure name defined in the CREATE or ALTER script.  You also did not include the entire error message.

    Perhaps you added a schema to the procedure name or meant to use dbo.SPAllPatients when creating the procedure?

    Have you tried simply executing the stored procedure in SSMS?

     

    Monday, September 17, 2018 2:01 PM
  • User411346422 posted

    Hello,

    Thanks for the response.

    When I created the procedure, I used 'CREATE Procedure dbo.SPAllPatients'. All my procedures have dbo.ProcedureName defined in the CREATE or ALTER script. Do I need to specify 'dbo.SPAllPatients'  in my VB code?

    As I said in my original post, I can log in to SSMS and execute the procedure with no problems, both as the owner of the database or the user defined in my database connection string.

    Full error message below, which I hope can shed some light on this:

    Server Error in '/MyApplication' Application.


    Could not find stored procedure 'SPAllPatients'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'SPAllPatients'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


    Stack Trace:

    [SqlException (0x80131904): Could not find stored procedure 'SPAllPatients'.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +3161800
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +326
       System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4061
       System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +90
       System.Data.SqlClient.SqlDataReader.get_MetaData() +99
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) +580
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +3068
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +665
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +83
       System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +301
       System.Data.SqlClient.SqlCommand.ExecuteReader() +137
       DNAScreeningDAL.Patient.GetPatientList(HospitalCriteria HospitalCriteria) +712
    
    [TargetInvocationException: Exception has been thrown by the target of an invocation.]
       System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) +0
       System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) +128
       System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +142
       System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +889
       System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1829
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +26
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +134
       System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +105
       System.Web.UI.Control.EnsureChildControls() +106
       System.Web.UI.Control.PreRenderRecursiveInternal() +58
       System.Web.UI.Control.PreRenderRecursiveInternal() +204
       System.Web.UI.Control.PreRenderRecursiveInternal() +204
       System.Web.UI.Control.PreRenderRecursiveInternal() +204
       System.Web.UI.Control.PreRenderRecursiveInternal() +204
       System.Web.UI.Control.PreRenderRecursiveInternal() +204
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3618
    

    Monday, September 17, 2018 2:19 PM
  • User475983607 posted

    Have you verified the application is connecting to the expected DB?

    Monday, September 17, 2018 2:34 PM
  • User411346422 posted

    Hello,

    Yes, wrote some code from my application which executed a simple 'SELECT DB_NAME()'. It returned the correct name of the database. Anyway, the database is defined in the my connection string.

    Does this suffice?

    Monday, September 17, 2018 2:43 PM
  • User475983607 posted

    Hello,

    Yes, wrote some code from my application which executed a simple 'SELECT DB_NAME()'. It returned the correct name of the database. Anyway, the database is defined in the my connection string.

    Does this suffice?

    Right... generally in any dev shop we have environments like dev, text, and production.  The database name does not change but the environment does.  Have you verified the environment is correct?

    Monday, September 17, 2018 2:55 PM
  • User411346422 posted

    Not sure what you mean by 'environment'.

    We have a development SQL Servers and development IIS Servers and corresponding production SQL servers and IIS servers. At them moment I'm deploying\testing on the development IIS Server before I go 'live'. They all sit on the same subnet of out network and belong to the same AD domain. Not sure if that answers your question?

    Monday, September 17, 2018 3:05 PM
  • User475983607 posted

    Did you add the stored procedure to production and not development or perhaps the other way around?

    Monday, September 17, 2018 3:11 PM
  • User411346422 posted

    Thanks again for the suggestions.

    No definitely the production server. This is why I posted. I can't seem to find what's at the root of the problem and I'm hoping that someone will have an idea.

    This is a real show-stopper otherwise.

    Tuesday, September 18, 2018 7:32 AM
  • User61956409 posted

    Hi mojo99,

    To troubleshoot the issue, you can try to call another stored procedure and check if same issue occurs.  

    With Regards,

    Fei Han

    Tuesday, September 18, 2018 9:18 AM
  • User411346422 posted

    Hello,

    Thanks for your response.

    All other stored procedures are working as expected. It is only this one that is causing me all these headaches.

    I did create a procedure (very basic select statement),  developed a test page, called it from the test page and got the same error when deployed to IIS (no error is Visual Studio).

    The major change between these procedures and the ones that are working, is that the working ones were created in SQL Server 2008, deployed to IIS 8.0.5. The ones that don't work were created in SQL Server 2014, deployed to the same IIS Server version, but don't work.

    As I said, I recently migrated the database in question from SQL Server 2008 to SQL Server 2014 (did a backup and then restored).

    Tuesday, September 18, 2018 9:46 AM
  • User411346422 posted

    I've just created a new test page in my application based on an existing working stored procedure, deployed to IIS. No problems - works as expected.

    This confirms that it's only certain stored procedures which are causing issues.

    Tuesday, September 18, 2018 10:09 AM
  • User475983607 posted

    This confirms that it's only certain stored procedures which are causing issues.

    The most likely cause is you have not deployed the procedure to the correct database.   It works locally in Visual Studio because the the connection string is pointing to the DB with the new procedure.  

    Simply compare the connection string that works to the connection string that does not work.

    Tuesday, September 18, 2018 10:52 AM
  • User411346422 posted

    mojo99

    This confirms that it's only certain stored procedures which are causing issues.

    The most likely cause is you have not deployed the procedure to the correct database.   It works locally in Visual Studio because the the connection string is pointing to the DB with the new procedure.  

    Simply compare the connection string that works to the connection string that does not work.

    I'm very grateful that you've taken the time to reply!

    I'm sorry to say I don't think this the issue. As I'm still in the test phase, I've deployed to our test ISS Server against our test SQL server. The connection string is the same one I'm using in both VS and the test IIS Server. I haven't changed it. Both connection strings (IIS and VS) are pointing to the same SQL Server on the network. I've only deployed the stored procedure to one SQL Server - the one I'm testing against.

    Tuesday, September 18, 2018 11:24 AM
  • User411346422 posted

    Right - this is very strange. The connection string was pointing to the SQL Server using the Server name. I changed this to the IP address of the machine and hey presto it works!!

    Why this should be I don't know when other stored procedures were quite happy with the Server name. Perhaps someone has an idea?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2018 11:34 AM
  • User475983607 posted

    Flush DNS on the server.  However, I would assume it would have invalidated by now.  Open the command prompt and enter.

    ipconfig /flushdns

    Otherwise, talk with your system admin. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2018 11:43 AM
  • User411346422 posted

    Thanks for all the advice!

    Tuesday, September 18, 2018 11:46 AM
  • User411346422 posted

    Sorry, one more question. Do I run this command on our DNS server or on the IIS server?

    Tuesday, September 18, 2018 1:06 PM
  • User475983607 posted

    The main issue is as stated above.  The web server is not pointing to the same SQL server as your local development machine.  Run the command on both your box and the web server.  The likely scenario is you stood up a new DB server with the same name as the previous.  Your local machine is resolving to a different IP address than the web server.

    IMHO, you should get with your system admin for assistance or turn off the old SQL server so there's no confusion.  

    Tuesday, September 18, 2018 1:14 PM