asked on

T-sql C# CLR Error

I have created CLR using C# and i have added to successfully to database to my CLR.
But when i'm try to run error message is showin

Error

Msg 6522, Level 16, State 1, Procedure SendOrdersAPI, Line 9
A .NET Framework error occurred during execution of user-defined routine or aggregate "SendOrdersAPI":
System.InvalidOperationException: Invalid attempt to read when no data is present.
System.InvalidOperationException:
at System.Data.SqlClient.SqlDataReader.CheckDataIsReady(Int32 columnIndex, Boolean allowPartiallyReadColumn, Boolean permitAsync, String methodName)
at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at ManCLR.dbConnection.username()
at ManCLR.SendOrderOMS.SendOrdersTOAPI(String OrderNumber, Int32 TotalNumberLine)
at ManCLR.DenManMain.sendOMS(String OrderNumber, String username)

CREATE PROCEDURE SendOrdersAPI
(
@OrderNumber nvarchar(MAX),
@username nvarchar(MAx)
)
AS 
EXTERNAL NAME ManCLR.[ManCLR.ManMain].sendAPI
GO

EXEC SendOrdersAPI  'Test123','sa'

Open in new window

And also i used following commands in the Sql server to add assembly

CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\SMdiagnostics.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll' WITH permission_set = unsafe
--CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.IdentityModel.Selectors] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.IdentityModel.Selectors.dll' with permission_set = unsafe
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [Microsoft.Transactions.Bridge] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.Transactions.Bridge.dll' with permission_set = unsafe

Please attached Assembly Details form

select * from sys.assemblies

if anyone can let me know how to solve much appreciated.
Many Thanks
Assembly.xlsx

Pavel Celba

This error means you have no data available. You may read this article: https://blogs.msdn.microsoft.com/spike/2008/07/25/invalid-attempt-to-read-when-no-data-is-present/ to find more details for such error.

You should test your C# code outside SQL Server first. Then you have to test it under the user which is used to run SQL Server service and then you are safe to implement it as CLR SP in SQL Server.

I suppose you are not using the C# code to read data from the SQL Server on which is the SP implemented as it would be rather contraproductive.

lankapala

ASKER

My program is working in perfectly in the C# but when it's transferred to CLR (created complete different project using C# CLR) not working.
C# reader is working perfectly in the console application.

Pavel Celba

Create some much simpler CLR SP which reads data and returns just the number of rows read. If this will work for you then you may go to the more complex next step.

ASKER CERTIFIED SOLUTION

lankapala

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

Great you've fixed the problem. Was it caused by the missed SqlContext.Pipe.Send?

lankapala

ASKER

Yes Reader need to pass
Like this
SqlContext.Pipe.Send(reader);

lankapala

ASKER

I found the issue and resolved