Solved

Debug LINQ / Stored Proc

Posted on 2013-06-18
9
374 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:Tom Knowlton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Tom 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 5

Author Comment

by:Tom 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:Tom 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:Tom 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:Tom 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

617 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