Good mornig friends!
Again I am having a trouble and I do not know what am I doing wrong when I try to retrieve a SQL parameter outside to .Net Clr
I did a clr example like this:
using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Text; using Microsoft.SqlServer.Server; namespace Test { public class StoredProcedures { [SqlProcedure] public static void Test ( SqlString p_t ) { SqlCommand CommandTest = new SqlCommand(); SqlParameter ParameterTest = new SqlParameter("@p_it", SqlDbType.NVarChar); StringBuilder sb = new StringBuilder(); //String p_it; //Uncomment this line CommandTest.CommandType = CommandType.Text; CommandTest.Parameters.Add(ParameterTest); sb.AppendLine("SELECT"); sb.Append(" @p_it = N'"); sb.Append(p_t); sb.Append("';"); CommandTest.CommandText = sb.ToString(); using (SqlConnection conn = new SqlConnection("context connection=true")) { CommandTest.Connection = conn; conn.Open(); SqlContext.Pipe.Send(sb.ToString()); CommandTest.ExecuteNonQuery(); SqlContext.Pipe.Send((String)CommandTest.Parameters["@p_it"].Value); //Comment this line //p_it = CommandTest.Parameters["@p_it"].Value.ToString(); //Uncomment this line //SqlContext.Pipe.Send(p_it); //Uncomment this line } } } }
I now my trouble and it is because the parameter @p_it is null when I try to sed it to pipe in SqlContex but really it is not null.
When I test the clr with this code:
CREATE ASSEMBLY [Test] FROM N'Z:\Some\Route\Test.dll' WITH PERMISSION_SET = SAFE GO CREATE PROCEDURE [dbo].[Test] ( @p_e1 AS [SYSNAME] ) AS EXTERNAL NAME [Test].[Test.StoredProcedures].[Test] GO EXECUTE [dbo].[Test] @p_e1 = N'Test' GO --DROP PROCEDURE [dbo].[Test] --GO --DROP ASSEMBLY [Test] --GO
I obtain the next error message:
"Mens 6522, Nivel 16, Estado 1, Procedimiento Test, Lnea 14
A .NET Framework error occurred during execution of user-defined routine or aggregate "Test":
System.ArgumentNullException: El valor no puede ser nulo. -----> The value can not be null
Nombre del parmetro: message
System.ArgumentNullException:
en Microsoft.SqlServer.Server.SqlPipe.Send(String message)
en Test.StoredProcedures.Test(SqlString p_t)"
May any body help me telling me what I am doing wrong?
Thanks a lot for your valuable help.
Re