0

Msg 6522, Level 16, State 1, Line 4 A .NET Framework error occurred during execution of user-defined routine or aggregate "GetDataSourceContents2": System.InvalidCastException: Unable to cast object of type 'SqlServerProject2.webservice.DataSourceDefinition' to type 'System.Collections.IEnumerable'. System.InvalidCastException: at SqlServerProject2.UserDefinedFunctions.GetDataSources2(String Datasourcename) .

I get the above error while running the clr function from the below vb.net assembly. Can someone help?

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports SqlServerProject2.webservice
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Runtime.InteropServices

Partial Public Class UserDefinedFunctions

<SqlFunction(FillRowMethodName:="FillRow")> _
Public Shared Function GetDataSources2(ByVal Datasourcename As String) As IEnumerable

    Dim WS = New ReportingService2005
    WS.UseDefaultCredentials = True

    Dim DSContents As DataSourceDefinition = WS.GetDataSourceContents(Datasourcename)

    Return DSContents

End Function

Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef ConnectString As SqlString)
    Dim DSContents As DataSourceDefinition = CType(obj, DataSourceDefinition)
    ConnectString = New SqlString(DSContents.ConnectString)
End Sub
End Class
swetha
  • 1
  • 1
    I guess that you have used the `FillRow` example from somewhere like [CLR Table-Valued Functions](https://msdn.microsoft.com/en-us/library/ms131103.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-4). VB.NET does not have the `Out` keyword: [Is there a VB.NET equivalent of C# out parameters](http://stackoverflow.com/q/4358742/1115360). If you used [Option Strict On](https://msdn.microsoft.com/en-us/library/zcd4xwzs.aspx) it would have pointed out that problem for you. – Andrew Morton Oct 20 '16 at 20:50
  • Thanks Andrew for responding. I removed the keyword from subroutine, but get the same error when trying to run the function. Program errs out when trying to convert DataSourceDefinition type to IENumerable type, tried with both implicit & explicit conversions. This conversion is required to pass on values to FillRow sub. – swetha Oct 24 '16 at 14:24
  • I don't have my code example from experimenting to hand or the time to re-create it, but [Basics of Using a .NET Assembly in MS SQLServer - User Functions](http://www.codeproject.com/Articles/28429/Basics-of-Using-a-NET-Assembly-in-MS-SQLServer-Use) may give you enough clues to be getting on with. – Andrew Morton Oct 24 '16 at 15:01
  • OK, I found my experiment, but I wrote it in C#. ISTR that I had to add the [SqlFunctionAttribute.TableDefinition Property](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlfunctionattribute.tabledefinition(v=vs.110).aspx) to make it work. The code you show would use `` (of course, adjust the type as appropriate). I could be wrong. – Andrew Morton Oct 24 '16 at 17:58

0 Answers0