Solved

Debug LINQ / Stored Proc

Posted on 2013-06-18
9
365 Views
Last Modified: 2013-06-19
[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.Lead_ReviewStatus_Update")]
		public int Lead_ReviewStatus_Update([global::System.Data.Linq.Mapping.ParameterAttribute(Name="LeadID", DbType="Int")] System.Nullable<int> leadID, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="LeadSID", DbType="Int")] System.Nullable<int> leadSID, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="ReviewStatus", DbType="Int")] System.Nullable<int> reviewStatus)
		{
			IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), leadID, leadSID, reviewStatus);
			return ((int)(result.ReturnValue));
		}

Open in new window




When this line of code executes:


IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), leadID, leadSID, reviewStatus);


this is what I get:

MESSAGE: Invalid object name 'Leads'. 

STACK TRACE: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) at Models.RedBellDataContext.Lead_ReviewStatus_Update(Nullable`1 leadID, Nullable`1 leadSID, Nullable`1 reviewStatus) in C:\R13\Models\RedBell.designer.cs:line 68 at Reo.ContentControls.PropertyLead.btnSubmit_Click(Object sender, EventArgs e) in C:\R13\Reo\ContentControls\PropertyLead.ascx.cs:line 314 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 

Open in new window



The code runs fine if I call it manually in Management Studio (SQL Server)


Here is the underlying stored proc:

USE PP

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Lead_ReviewStatus_Update')
	BEGIN
		DROP  Procedure  Lead_ReviewStatus_Update
	END
GO

CREATE Procedure dbo.Lead_ReviewStatus_Update

@LeadID int,
@LeadSID int,
@ReviewStatus int
	
AS

update dbo.Leads
set reviewStatus = @ReviewStatus
where LeadID = @LeadID and LeadSID = @LeadSID 

GO

GRANT EXEC ON Lead_ReviewStatus_Update TO xxxxxxxxxx

GO

Open in new window

0
Comment
Question by:knowlton
  • 5
  • 3
9 Comments
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 39258413
First question that springs to mind is, are you sure that the application and management studio are connecting to the same database?

Second question, when you say it runs fine in management studio, are you talking about the script you posted or are you actually executing the sproc, i.e.

exec Lead_ReviewStatus_Update @LeadID = ..., @LeadSID = ..., @ReviewStatus = ...
0
 
LVL 23

Assisted Solution

by:Ioannis Paraskevopoulos
Ioannis Paraskevopoulos earned 250 total points
ID: 39258420
Hi,

In the connection string that you use, do you use a user that has access to Leads? Is he a dbo? If you connect to SSMS on the same db as your connection string dictates and with this specific user, does the sp run?

Giannis
0
 
LVL 5

Author Comment

by:knowlton
ID: 39259653
CraigWagner:

First question that springs to mind is, are you sure that the application and management studio are connecting to the same database?

Not sure.  How can I check this?  Can it be done at runtime?  The intent is for the "PP" database to be used throughout.

Second question, when you say it runs fine in management studio, are you talking about the script you posted or are you actually executing the sproc, i.e.

exec Lead_ReviewStatus_Update @LeadID = ..., @LeadSID = ..., @ReviewStatus = ...

It runs fine in Management Studio when I right-click on the sproc and pick "Execute Stored Procedure" and provide the parameters.

run with params
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Author Comment

by:knowlton
ID: 39259661
In the connection string that you use, do you use a user that has access to Leads? Is he a dbo?

I believe so, but how can I know for sure, or verify this is true?

 If you connect to SSMS on the same db as your connection string dictates and with this specific user, does the sp run?

I believe so!  See image above...
0
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 39259675
You check which database you're using by looking at the connection string that is initializing your connection. Does it specify the right server and database?
0
 
LVL 5

Author Comment

by:knowlton
ID: 39259688
How do I check which connection string is being used?

With LINQ I am not sure when it gets initialized.
0
 
LVL 5

Author Comment

by:knowlton
ID: 39259697
It works everywhere else just fine.

For example, there are many database calls before the code reaches the point where the problem is.
0
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 250 total points
ID: 39259769
How many connection strings do you have in your config file? It shouldn't be too hard by looking at the names of them to figure out which one it is.
0
 
LVL 5

Author Comment

by:knowlton
ID: 39259793
I tried it again this morning -- and it works now.  : P

If this happens again -- I will post a new question.

I wonder if our DBA made a fix after I left work.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question