Solved

Debug LINQ / Stored Proc

Posted on 2013-06-18
9
360 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Author Comment

by:knowlton
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 21

Expert Comment

by:Craig Wagner
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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 …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now