I have created a CLR stored procedure. Now I am trying to access that
CLR sproc from another stored procedure. When I try to create the
second sproc, I get the message:
Cannot add rows to sysdepends for the current object because it depends
on the missing object <<whatever>>.
Any ideas on what I'm doing wrong? I let Visual Studio deploy the
assembly and create the first stored procedure.
Thanks,
JoeHello Jmnd,
> I have created a CLR stored procedure. Now I am trying to access that
> CLR sproc from another stored procedure. When I try to create the
> second sproc, I get the message:
How do you mean "access that CLR sproc?" Are you calling it with T-SQL's
EXEC?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Jmnd (jnedumgottil@.gmail.com) writes:
> I have created a CLR stored procedure. Now I am trying to access that
> CLR sproc from another stored procedure. When I try to create the
> second sproc, I get the message:
> Cannot add rows to sysdepends for the current object because it depends
> on the missing object <<whatever>>.
<<whatever>>? Is that the name of your CLR procedure, or the name of
something else?
> Any ideas on what I'm doing wrong? I let Visual Studio deploy the
> assembly and create the first stored procedure.
Obviously Visual Studio did not create the procedure as you intended. Or
you are creating your T-SQL procedure in another database - or schema -
that you indended. How about using the Object Explorer to see what is in
that database?
Personally, I prefer to create CLR objects without Visual Studio. VS is
just so completely confusing. How difficult is it really to compile a
C# or VB program from the command line, saying CREATE ASSEMBLY, and then
CREATE PROCEDURE EXTERNAL NAME?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sorry, I will clarify. I was trying to access the stored procedure
using T-SQL "EXEC" command. And I tried using regular command lines,
after I ran into issues. I used the "Create Procedure External Name"
(which I modified by pulling the script from the existing CLR Stored
Procedure). However, when I tried to run the create script on the
second stored procedure (the one that calls the CLR sproc), I was still
getting the same error:
Cannot add rows to sysdepends for the current object because it depends
on the missing object <<my CLR stored procedure>>.
The CLR sproc is showing up in SQL Management Studio. And I can call
it - I ran a test script on it. However, this other sproc cannot call
the CLR one. Is this a permissions issue?
Thanks again for your help.
Joe|||Jmnd (jnedumgottil@.gmail.com) writes:
> Sorry, I will clarify. I was trying to access the stored procedure
> using T-SQL "EXEC" command. And I tried using regular command lines,
> after I ran into issues. I used the "Create Procedure External Name"
> (which I modified by pulling the script from the existing CLR Stored
> Procedure). However, when I tried to run the create script on the
> second stored procedure (the one that calls the CLR sproc), I was still
> getting the same error:
> Cannot add rows to sysdepends for the current object because it depends
> on the missing object <<my CLR stored procedure>>.
> The CLR sproc is showing up in SQL Management Studio. And I can call
> it - I ran a test script on it. However, this other sproc cannot call
> the CLR one. Is this a permissions issue?
No. More likely a trivial spelling errors. (Happens in the best families.)
When you look in Mgmt Studio, where does the T-SQL procedure appear?
(The message you get is not an error message, and the procedure is still
created. Which it also says in the message.)
If you are still
, please post the T-SQL procedure, as well as
the T-SQL statements to create the CLR proc.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Erland,
I agree that its possible that it's something trivial, but it might not be.
We might be missing an assembly, or the called proc might not have been cata
loged.
Or somebody tinkered with the visibility of an assembly, or its a versioning
problem, etc.
Jmnd, here's a simple example of what I think you are trying to do. If so,
this should work. If not, please feel to contact me off-line for follow up.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CalledStoredProcedure()
{
SqlCommand cmd = new SqlCommand("select 'Hello from the called proc'");
SqlContext.Pipe.ExecuteAndSend(cmd);
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallingStoredProcedure()
{
SqlCommand cmd = new SqlCommand("select 'Hello from calling proc';
exec dbo.CalledStoredProcedure");
SqlContext.Pipe.ExecuteAndSend(cmd);
}
};
test with exec dbo.CallingStoredProcedure
I do agree with Erland, posting the code would help much.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for the input so far. I will double-check to make sure there
are no typos
========================================
================
here's my CLR procedure code in C#:
========================================
================
using System;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class QueryBuilder
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void buildIndividualSummaryQuery(
....
/*parameter list*/
....
)
{
QueryBuilder ci = new QueryBuilder();
ci.addSelect("IND_F", "DATE", "CAL_MONTH_YR");
ci.addSelect("DATE_D", "DATE", "FSCL_MONTH_YR",
"FiscalMonthYr", "MAX");
ci.addSelect("IND_F", "I", "TOTAL_IND", "TotalCount", "SUM");
/* This calls another method in the same class */
string sqlString = ci.buildIndividualReportsQuery(
/*params*/ );
SqlContext.Pipe.Send("SqlString=" + sqlString);
SqlConnection connection = new SqlConnection("context
connection=true");
connection.Open();
SqlCommand command = new SqlCommand(sqlString, connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
connection.Close();
}
}
========================================
================
Here's how I created the Stored Procedure wrapper:
========================================
================
USE [CustomerIntelligence]
GO
IF OBJECT_ID ( 'buildIndividualSummaryQuery', 'P' ) IS NOT NULL
DROP PROCEDURE buildIndividualSummaryQuery;
GO
CREATE PROCEDURE [dbo].[buildIndividualSummaryQuery]
...
/*params*/
...
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CI].[QueryBuilder].[buildIndividualSummaryQuery]
GO
========================================
====
Here's my stored procedure that calls the CLR procedure:
========================================
====
ET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'IndividualSummaryReport', 'P' ) IS NOT NULL
DROP PROCEDURE IndividualSummaryReport;
GO
CREATE PROCEDURE IndividualSummaryReport
...
/* parameter list */
...
AS
BEGIN
...
/* other extraneous code */
...
exec [buildIndividualSummaryQuery]
....
/* parameter list */
...
END
GO
@.gmail.com) writes:
Gustavo Bilinski wrote:
Gustavo Bilinski wrote: