Solved

How to use temp table in Stored Procedure with LINQ to SQL

Posted on 2011-09-09
9
2,071 Views
Last Modified: 2013-11-11
I am selecting certain records from a table randomly and saving them in a temp table. I have created a stored procedure to select into this table and need to pull these randomly generated records in different parts of my c# code . Another stored procedure to pull the values from this sp later on. While trying to run the first stored procedure i am getting this error  

"The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window."

Any ideas how to fix this error or a better approach to handle this situation?

I have also tried creating a table variable as mentioned on this site http://odetocode.com/code/365.aspx  but I can't access this Table variable in second stored procedures in parts of code


0
Comment
Question by:mmalik15
  • 5
  • 4
9 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36516184
You're getting that error because the table does not exist when LINQ tries to determine what gets returned.  It can be avoided by not using an actual temporary table but a real one instead.  If you call it something like TmpTable or Temp.Table (using s special "temporary" schema), you would avoid that error and your DB would still remain fairly clean.

Another option is editing the DMBL as can be read here: http://riteshkk2000.blogspot.com/2010/08/error-unknown-return-type-return-types.html
But I'm not familiar with that myself so can't say more about it...

Hope that helps you further a bit?
0
 

Author Comment

by:mmalik15
ID: 36516255
thanks for the comment valentionV.

How can I use a real temp table schema. Can you give me an example of it? All I need is temp table for storing certain questions and keep the data until the the user clicks on finish button. And there can be concurrent users too for this application, each having their own set of questions.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36518905
Sorry if I wasn't clear.  What I meant is that you can create a new schema called Temp for instance.  In that schema you would create a table that has the same layout as your current temporary table.  This Temp.SomeTable would then be used as a "temporary" table, in the meaning that the data that it contains is temporary, but the table itself is not.

As for concurrent users, you could create an additional column in your "temporary" table to indicate what user the record belongs to.

Note that you'll need some sort of cleanup process as well.  When would records become obsolete?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:mmalik15
ID: 36518925
What you're saying is to create a temp table in the database just like any other table and insert randomly picked questions in it along with the userinfo? And delete the records from that table when the user clicks finish test button from that table?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36518936
That's right, but it's not a "temp table", it's a table just like any other.  Based on the info you've given, it seems like what you're needing to deal with is some sort of session state.  What happens if the user taking the test closes the application by accident, or is faced with a crash?  I think it would be beneficial if that user would be able to continue the test from the point where it ended.  And that should be possible using the table as I've described it.  On the other hand, it would be a problem with the temporary table that you were thinking to use.
0
 

Author Comment

by:mmalik15
ID: 36518943
it sounds like a good idea but yes the problem can arise when the user has accidently closed the browser without clicking the finish button when we clear records from this table
0
 

Author Comment

by:mmalik15
ID: 36518957
I dont want the user to resume quiz if he/she has closed the browser rather the user has to start the test again. And I only want to save the results when the user has clicked on finish button
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 36519189
If you keep a timestamp in the table as well, that should help you to clean out the outdated records.  You could run a nightly job that deletes every record older than 24 hours for instance, thereby deleting the "orphaned" records.
0
 

Author Closing Comment

by:mmalik15
ID: 36596210
sorry for late reply. thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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