问题描述:

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?

相关阅读:
Top