May 18, 2009 at 3:24 pm
I built a simple method sending an ADODB recordset via MSMQ and successfully tested in a console app, but when I import into sql server2005 CLR and exectue I get an error.
-- Send object (all code works except this line):
[SqlProcedure]
public static void Send(SqlString queue, SqlString point, SqlString bodynumber, out SqlString msg)
{
ADODB.Recordset rs = new ADODB.Recordset(); - call causing error
} // end Send
-- sql code
create assembly SqlMSMQtools authorization dbo from 'C:\temp\SqlMSMQtools.dll' with permission_set = unsafe
go
-- proc
create PROCEDURE usp_MSMQ_SendAP46
@queuenvarchar(500),
@pointnvarchar(100),
@body_numbernvarchar(10),
@msgnvarchar(MAX) output
AS EXTERNAL NAME SqlMSMQtools.[SqlMSMQtools.Basic].Send
GO
-- execute
DECLARE @test-2 nvarchar(1024)
EXEC usp_MSMQ_SendAP46 @queue='FormatName:DIRECT=TCP:1.2.3.4\\private$\\XXX', @point='TestPoint',@body_number='123456', @msg = @test-2 OUTPUT
select @test-2
-- error
Msg 6522, Level 16, State 1, Procedure usp_MSMQ_SendAP46, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_MSMQ_SendAP46':
System.UriFormatException: Invalid URI: The URI is empty.
System.UriFormatException:
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
at System.ComponentModel.Design.RuntimeLicenseContext.GetLocalPath(String fileName)
at System.ComponentModel.Design.RuntimeLicenseContext.GetSavedLicenseKey(Type type, Assembly resourceAssembly)
at System.ComponentModel.LicenseManager.LicenseInteropHelper.GetCurrentContextInfo(Int32& fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth)
at SqlMSMQtools.Basic.Send(SqlString queue, SqlString point, SqlString bodynumber, SqlString& msg)
May 19, 2009 at 8:47 am
Hi
Question:
Why do you use ADODB instead of ADO.NET?
Could you please wrap your code with the following exception handler and post the result. Maybe this helps to find the problem:
try
{
////////////////////////////////////////
// YOUR CODE
int i = int.Parse("blah");
////////////////////////////////////////
}
catch (Exception ex)
{
System.IO.StringWriter sw = new System.IO.StringWriter();
sw.WriteLine("======================================");
sw.WriteLine("Exception");
sw.WriteLine("Type: {0}", ex.GetType());
sw.WriteLine("Message: {0}", ex.Message);
Exception e = ex.InnerException;
while (e != null)
{
sw.WriteLine();
sw.WriteLine("-------------------------");
sw.WriteLine("Inner Exception");
sw.WriteLine("Type: {0}", e.GetType());
sw.WriteLine("Message: {1}", e.Message);
e = e.InnerException;
}
System.Diagnostics.StackTrace stack;
stack = new System.Diagnostics.StackTrace(ex, true);
sw.WriteLine();
sw.WriteLine("-------------------------");
sw.WriteLine("Target");
for (int i = stack.FrameCount - 1; i >= 0; i--)
{
System.Diagnostics.StackFrame frame = stack.GetFrame(i);
sw.WriteLine("({0}) :: {1} | File(Line:Column): {2} ({3}:{4})",
(frame.GetMethod().DeclaringType == null ? "" : frame.GetMethod().DeclaringType.ToString()),
frame.GetMethod().ToString(),
string.IsNullOrEmpty(frame.GetFileName()) ? "" : frame.GetFileName(),
frame.GetFileLineNumber(),
frame.GetFileColumnNumber());
}
stack = new System.Diagnostics.StackTrace(1, true);
sw.WriteLine();
sw.WriteLine("-------------------------");
sw.WriteLine("Origin");
for (int i = 0; i < stack.FrameCount; i++)
{
System.Diagnostics.StackFrame frame = stack.GetFrame(i);
sw.WriteLine("({0}) :: {1} | File(Line:Column): {2} ({3}:{4})",
(frame.GetMethod().DeclaringType == null ? "" : frame.GetMethod().DeclaringType.ToString()),
frame.GetMethod().ToString(),
string.IsNullOrEmpty(frame.GetFileName()) ? "" : frame.GetFileName(),
frame.GetFileLineNumber(),
frame.GetFileColumnNumber());
}
SqlContext.Pipe.Send(sw.GetStringBuilder().ToString());
}
Thanks
Flo
May 19, 2009 at 11:53 am
There's lots of COM stuff that isn't allowed in SQL Server/CLR.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 14, 2012 at 4:42 am
Hi did beyonder422 did you ever get a solution to this problem. I am having the exact same problem and can't find any information about it on Google.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply