I have added some custom code to a report in SSRS2005 that works perfectly in BIDS2005 but not when I deploy to the server. Here is the custom code:

Public Function GetUsers(ByVal param As Integer) As String
Dim sqlCon As New System.Data.SqlClient.SqlConnection
Dim cmd As New System.Data.SqlClient.SqlCommand
Dim dRet As String
Dim sCmdText As String
sqlCon.ConnectionString = "data source=MYSERVER;initial catalog=MYDB;Integrated Security=true"
cmd = New System.Data.SqlClient.SqlCommand
sCmdText = "SELECT dbo.udf_MyFunction("
'cmd.CommandType = CommandType.Text
cmd.Connection = sqlCon
cmd.CommandTimeout = 0
sCmdText = sCmdText & param
sCmdText = sCmdText & ")"
cmd.CommandText = sCmdText
If sqlCon.State <> System.Data.ConnectionState.Open Then
sqlCon.Open()
End If
dRet = cmd.ExecuteScalar() & ""
sqlCon.Close()
Return dRet
End Function

In order to use this code, I had to add this assembly:

System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

When the user clicks on a particular text box, a value from the report is passed to the SQL function and a list of integers is returned to a detail report. In BIDS, this works perfectly every time. However, I have deployed this on two different report servers and it does not work. The detail report opens up but nothing is passed.

The frustrating thing is that when I open up an SQL Server Profiler trace on this with just about every single option selected, I get nothing. I know that the code is hitting my SQL database because it returns results from the database but the profiler does not show anything happening. This has made it pretty much impossible for me to troubleshoot.

Any ideas?