-
Oct 24th, 2017, 06:48 AM
#1
Thread Starter
Lively Member
[RESOLVED] Ef stored procedure return null
Hi guys,
I have a simple stored procedure that returns number of column:
Code:
BEGIN
SET FMTONLY OFF
SELECT A.EMPLOYEENO, A.FIRSTNAME, A.LASTNAME, A.MIDDLENAME, A.DEPARTMENT, B.DEPARTMENTDESC, A.SUPERVISORID, A.SUPERVISORNAME
FROM [IPADDRESS].[MASTER].[dbo].[EMPLOYEES] AS A
INNER JOIN [IPADDRESS].[MASTER].[dbo].[DEPARTMENT] AS B
ON A.DEPARTMENT = B.DEPARTMENTCODE
WHERE A.EMPLOYEENO = @ID
END
Then I did the following steps:
Step A
1. Update Model from Database
2. Add the Stored Procedure
Step B
1. Add new Function Import
2. Enter the name "Function Import Name"
3. "Complex" as Returns a Collection of.
4. Get Column Information
And then this is my code in Controller:
Code:
var empInfo = from item in db.getEmployeeDetails(id)
select new
{
fullName = item.FIRSTNAME + " " + item.LASTNAME,
department = item.DEPARTMENTDESC
};
return Json(new { FullName = fullName, Department = department }, JsonRequestBehavior.AllowGet);
However it returns null, I did the SET FMTONLY OFF but still no records.
Is there anything I left undone?
Thank you
Last edited by FunkySloth; Oct 24th, 2017 at 06:52 AM.
-
Oct 24th, 2017, 07:37 AM
#2
Re: Ef stored procedure return null
Start with the basics:
1) Are you SURE you're connected to the right database? - I ask because you're using a FQN to access the table, which means the tables you're accessing may not be the tables in the database you're connected to... is there a reason you're using an FQN?
2) Is the table empty? Because you're using a FQN, it's possible the table you're actually using could be empty.
3) Does the ID you're passing in exist?
-tg
Edit...
I was going to suggest changing
[IPADDRESS].[MASTER].[dbo].[EMPLOYEES]
to just dbo.EMPLOYEES ... but then I notice... you're using the master database? ?! O.O
-
Oct 24th, 2017, 06:24 PM
#3
Thread Starter
Lively Member
Re: Ef stored procedure return null
Hello techgnome,
1. Yes I am connected to the right database, and as you notice I'm using the ip address due to I am accessing the database to other server.
2. The table are not empty because it is the master data of all employees.
3. The ID that im passing to the parameter does exist so it should return the record. However i didn't figure out why it isn't returning any data.
Last edited by FunkySloth; Oct 24th, 2017 at 06:27 PM.
-
Oct 24th, 2017, 06:55 PM
#4
Re: Ef stored procedure return null
Originally Posted by FunkySloth
1. Yes I am connected to the right database, and as you notice I'm using the ip address due to I am accessing the database to other server.
Is the stored procedure accessing a remote database? If you execute the stored procedure from within SSMS does it return the correct results? Is your connection string using integrated security or a hard coded username and password? If integrated security then you need to make sure the user your web application runs as can execute the stored procedure.
Also to repeat techgnome.... you're using the master database? ?! O.O
-
Oct 24th, 2017, 07:21 PM
#5
Thread Starter
Lively Member
Re: Ef stored procedure return null
Yes it is accessing a remote database and using a hard coded username and password connection string. When using SSMS it does return the correct record. Is there any problem about using the master database?
Last edited by FunkySloth; Oct 24th, 2017 at 07:57 PM.
-
Oct 24th, 2017, 08:26 PM
#6
Re: Ef stored procedure return null
Originally Posted by FunkySloth
Yes it is accessing a remote database and using a hard coded username and password connection string. When using SSMS it does return the correct record. Is there any problem about using the master database?
The master database is a system database, more accurately it is the system database as it is the database that controls and configures your SQL server, it isn't intended to be used as a database for your own things. https://docs.microsoft.com/en-us/sql...aster-database is possibly worth a quick look as it explains a bit more about it.
A brief summary though is that it isn't intended for you to use as your own database, it has fairly specific options set (such as simple logging) which make it unsuitable for a normal database workload, problems with the master database can leave you SQL Server unable to start (which in this case would mean unable to get at your data, restoring your damaged master would prevent the server starting).
The normal practice is to create your own database on the server to store your data in, not just shoving it into master.
I must admit to having never used EF with a linked server before so I am not sure if that could be part of the problem, is there a need to use a linked server rather than access the server directly? Would it be possible to test connecting to the other server directly? Also if you have a linked server why not use the remote server's name rather than the IP Address?
-
Oct 24th, 2017, 08:30 PM
#7
Thread Starter
Lively Member
Re: Ef stored procedure return null
So I guess I'd better use directly the server rather than linked it to my Stored Procedure, and oh, don't worry I'm not using the Master System Database, that's a different Master Database.
And I think I'd better create the Model of the linked server instead, so I could access the data easier. Though having multiple EF Model does it slow the performance of the system?
-
Oct 24th, 2017, 08:37 PM
#8
Re: Ef stored procedure return null
Originally Posted by FunkySloth
So I guess I'd better use directly the server rather than linked it to my Stored Procedure, and oh, don't worry I'm not using the Master System Database, that's a different Master Database.
And I think I'd better create the Model of the linked server instead, so I could access the data easier. Though having multiple EF Model does it slow the performance of the system?
So you have a database called Master on your SQL server? I have honestly never even tried to do that!
Having multiple EF models isn't really going to affect the performance, the only difference it is likely to make is in terms of the compiled application size and I doubt it will add that much impact. The bigger difference is going to be down to database and network speeds, accessing a database directly rather than going to one database and having it go to the other database is probably a faster option anyway.
-
Oct 24th, 2017, 08:40 PM
#9
Thread Starter
Lively Member
Re: Ef stored procedure return null
Well I'm going to create an EF Model for now.
Thank you very much PlausiblyDamp.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|