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

[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

 

[William Vaughn]

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)

 

[Frans Bouma [C# MVP]]

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

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#)
------------------------------------------------------------------------

 

[Marc Gravell]

(two workarounds posted to microsoft.public.dotnet.languages.csharp)

Marc

 

[Cor Ligthert[MVP]]

I told MS about this a long time ago, but apparently they didn't

listen. Oh well..

Frans,

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



Cor

 

[R. K. Wijayaratne]

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