Can't Call Stored Procedure in SqlFunction

There is a SqlFunction in my SQL Server Project:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBoolean GetRelation(SqlGuid ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn);
//SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn);
/*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.ID", SqlDbType.UniqueIdentifier));
cmd.Parameters["@.ID"].Value = ID;*/

using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
//...
sdr.Close();
}
conn.Close();
}
}

When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function."

What does the error message mean? How could I correct it? Thanks for answering.

The problem is resolved. Just remove the default "SET NOCOUNT ON;" added by SQL 2005 while creating stored procedure, then the SqlFunction will work.|||

Does anyone know of another way to fix this? We have alot of stored procedures with "SET NOCOUNT ON" and I would really rather not have to make that change unless we have to.

Thanks,
JD

|||Hi JD,

Bleh... I can't see good way. :) You could make a normal connection instead of using the context connection, but you'll have to deploy your assembly as external-access.

Cheers,
-Isaac