Sql-server – Can’t drag stored procedure onto dbml designer

linq-to-sqlsql serverstored-proceduresvisual studio 2010

I'm trying to do a simple call to a database stored procedure from a C# application.

I'm following a guide like, e.g., this one or this one.

Both of these have the same basic steps.

Add a LINQ to SQL .dbml item to my project
Connect to a database in Server Explorer
Drag a stored procedure from the Server Explorer onto my .dbml designer window

But step 3 doesn't work. I have my stored procedure and my designer window, but I can't drag the sproc. I don't get a plus sign, or a "not allowed" sign. It's just a non-dragable object.

I can't find another way to add my sproc to the .dbml file. And I can't find anyone else who has had this problem. Every source simply says to drag it over.

What could I be doing wrong here?

Screenshot:

enter image description here

Best Answer

Turns out the solution was that I had the wrong version of a .dll for Visual Studio.

I noticed I was getting exceptions when performing actions in the Server Explorer. Googling the exceptions led to this question which points to this dll

C:\Program Files (x86)\Common Files\microsoft shared\Visual Database Tools\dsref80.dll

as the issue.

I replaced it with the corresponding copy from one of my teammates and it now works fine.

Related Solutions

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Best Answer

Related Solutions

Sql-server – Select columns from result set of stored procedure

Sql – Insert results of a stored procedure into a temporary table

Related Topic