[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-09-09
9
Medium Priority
?
2,280 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
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.

 

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

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

868 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