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

R. K. Wijayaratne

unread,

Dec 3, 2007, 7:07:18 PM12/3/07

to

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-retrieving-data-using-stored-procedures.aspx

William Vaughn

unread,

Dec 3, 2007, 8:30:32 PM12/3/07

to

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" <ras...@gmail.com> wrote in message
news:1f4f41b2-7109-43f6...@i12g2000prf.googlegroups.com...

Frans Bouma [C# MVP]

unread,

Dec 4, 2007, 4:14:19 AM12/4/07

to

R. K. Wijayaratne wrote:

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

unread,

Dec 4, 2007, 5:11:46 AM12/4/07

to

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

Marc

Cor Ligthert[MVP]

unread,

Dec 5, 2007, 12:45:17 AM12/5/07

to

> 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

unread,

Dec 6, 2007, 10:30:29 PM12/6/07

to perseus.us...@xs4all.nl

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

On Dec 4, 8:14 pm, "Frans Bouma [C# MVP]"