CLR stored procedures in TableAdapter: no fields are populated?

[Steven Spits]

Hi,

While experimenting with CLR stored procedure, I discovered it won't
populate the fields in the TableAdapter, like a normal stored procedure. Why
is this?

GetClientList = normal stored procedure
GetClientListTest = CLR stored procedure

Both contain the same SQL, "SELECT * FROM Clients" and no parameters were
specified. Both run correctly in Management Studio.

When I create a new TableAdapter, select "use existing stored procedure" and
pick "GetClientList", alle fields are populated in the DataSet (Id, name,
etc). However, when I do the same with "GetClientListTest", there are no
fields. "Preview data", however, is working correctly.

What's causing the difference in behaviour?

Steven

- - -

 

[William \(Bill\) Vaughn]

Without the code for your CLR SP I have no way of knowing what's going on,
but I suggest you read chapter 13 of my new book that steps through the
process of creating a CLR SP that returns a rowset. Frankly, there are very
few situations where this makes sense as TSQL SPs are more efficient in this
respect.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)

 

[RobinS]

Hey, Bill, does your new book cover the basics of T-SQL?

Robin S.
-------------------------------

 

[Steven Spits]

Hi Bill,

Without the code for your CLR SP I have no way of knowing what's going on,
but I suggest you read chapter 13 of my new book that steps through the
process of creating a CLR SP that returns a rowset. Frankly, there are
very few situations where this makes sense as TSQL SPs are more efficient
in this respect.

I'm not planning to use a CLR to return a simple rowset, it's only to
simplify the problem I have.

Don't understand me wrong. The CLR SP *is* returning the rows I want. It
just doesn't populate the fields in my TableAdapter in *design time* like a
normal TSQL SP does.

Code is something like this : http://www.sqlteam.com/item.asp?ItemID=22074

Steven

- - -

 

[William \(Bill\) Vaughn]

Actually, it does this time (for the first time).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
-----------------------------------------------------------------------------------------------------------------------

 

[William \(Bill\) Vaughn]

Ah, I'm not really surprised. Consider that at design time, VS calls a SP
that asks SS to return the schema... I expect it's just not supported for
CLR SPs. Did you install VS 2005 SP1?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
-----------------------------------------------------------------------------------------------------------------------

 

[Steven Spits]

Hi Bill,

Ah, I'm not really surprised. Consider that at design time, VS calls a SP
that asks SS to return the schema... I expect it's just not supported for
CLR SPs. Did you install VS 2005 SP1?

Yes, SP1 is installed.

Thanks for your help!

Steven

- - -