Search code examples
sql-serverlinqlinq-to-sqlsql-server-2008system-stored-procedures

How to use a system stored procedure in LINQ to SQL

I'd like to add the "msdb.dbo.sp_help_job" system stored procedure to a LINQ to SQL object, but I can't figure out how to specify it. If I create a new Data Connection in Server Explorer and specify the "msdb" database of the server I want, and navigate to "Stored Procedures", that procedure is not listed. Am I looking in the wrong place?

I've added regular (user defined) stored procedures in the past with no problem. I know I could get there by executing it via "ExecuteCommand" on the data context, and I could also create a "wrapper" stored procedure that did nothing but call "sp_help_job", but I'd like to know how to hook it up directly to LINQ, or if it's even possible.

Solution

  • The System Stored Procedures are not actually sitting inside your database, but rather the Read-Only Resource database.

    http://msdn.microsoft.com/en-us/library/ms190940.aspx

    However, here's how you can make it possible to find them:

    Accessing System Databases/Tables using LINQ to SQL?