Linq: #Temp tables in SPROCS do not have return values in generatedmethods

  • Thread starter Thread starter R. K. Wijayaratne
  • Start date Start date
R

R. K. Wijayaratne

Linq: #Temp tables in SPROCS do not have return values in generated
methods

Hi everyone,

I have a SPROC which selects records into a MSSQL #temp table and then
selects the records from that. I drag this SPROC onto the right-hand
pane of the DBML diagram, but the method that gets generated for the
SPROC does not have a return type. Any ideas why this is?

I am referring to Scott Guthrie's blog article here:

http://weblogs.asp.net/scottgu/arch...-retrieving-data-using-stored-procedures.aspx
 
I have never had much luck with this (very valid) approach even in ADO.NET.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
 
R. K. Wijayaratne said:
Linq: #Temp tables in SPROCS do not have return values in generated
methods

Hi everyone,

I have a SPROC which selects records into a MSSQL #temp table and then
selects the records from that. I drag this SPROC onto the right-hand
pane of the DBML diagram, but the method that gets generated for the
SPROC does not have a return type. Any ideas why this is?

I am referring to Scott Guthrie's blog article here:

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-r
etrieving-data-using-stored-procedures.aspx
Click to expand...

This is a known issue with sqlserver.

What's done is this:
SET FMTONLY ON
EXEC proc
SET FMTONLY OFF

(this is done by SqlClient's code to obtain the schema of a proc
resultset. )

The thing is that with multiple resultsets, IF statements or
temptables this can go wrong: you either get an error (in case of the
temptable) or the wrong results.

We ran into this a couple of years ago and after that we added a
feature where users also could set the # of resultsets manually for
procs (in bulk, if necessary).

There's another caveat with this: when a proc calls an external stored
proc which for example sends an email, that proc _IS_ called as well.
so trying to obtain the resultsets of the procs using the above trick
isn't without risk: it COULD make your db get changed or send a lot of
emails ;)

I told MS about this a long time ago, but apparently they didn't
listen. Oh well.. :)

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
I told MS about this a long time ago, but apparently they didn't
listen. Oh well.. :)
Click to expand...
Frans,

Before you join them in Redmond, don't forget how nice Scheveningen is at
summer.

:-)

Cor
 
Wowo cool thanks for the tip Fans. It is good to no the "internals" of
how it works.
I tried various approaches and in the end reverted to using a DataSet
with an ObjectDataSource for my ASP.NET page :)
 
Back
Top