Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Debug LINQ / Stored Proc

Posted on 2013-06-18
9
Medium Priority
?
376 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
  • 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 1000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

886 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