Solved

Debug LINQ / Stored Proc

Posted on 2013-06-18
9
372 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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 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.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

728 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