Problem with changing server for stored procedure in Crystal Reports

Hello all,

We are developing a WinForms application including a number of Crystal
reports with it in VS2005. There are two servers in our network with two
identical databases (one development, one used on a daily basis).

Basically, our problem is that when we create a report using a parametarised
stored procedure from the database on one server it works fine. When we try
to run it agains the other server we get a dialog box with this error
message:

Failed to opean a rowset.
Details: ADO Error Code: 0x
Source: Microsoft OLD DB Provider for SQL Server
Description: Procedure or Function 'Facts_Print' expects parameter '@ser',
which was not supplied.
SQL State: 42000
Native Error:
Failed to open a rowset.
Error in file: C:\DOCUME~1\gordon\.........rpt
Failed to open a rowset.

It is obvious that parameters for the stored procedure (passed from code)
are not getting to the stored procedure but we have no idea why (code we use
to show the report is at the end of the post). For as long as we keep the
server and the database the same everything is ok. The moment we change
either the ConnectionInfo.ServerName or the ConnectionInfo.DatabaseName we
get the error.

We are truly at the loss here since changing ConnectionInfo works perfectly
when a report is connected to a table in the database. Everything stops
working when we create a report based on a stored procedure.

We tried both MSDN and Bussines Objects web sites but could not get any
information.

Any help appreciated.

BR,

GAZ


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
CustomersReport = New CrystalReport1

Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

myConnectionInfo.AllowCustomConnection = True
myConnectionInfo.ServerName = "iapetus"
myConnectionInfo.DatabaseName = "treasury35"
myConnectionInfo.IntegratedSecurity = True

Dim parValue1 As ParameterValues = New ParameterValues()
Dim disValue1 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue1.Value = 1
parValue1.Add(disValue1)

Dim parValue2 As ParameterValues = New ParameterValues()
Dim disValue2 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue2.Value = 1
parValue2.Add(disValue2)

Dim parValue3 As ParameterValues = New ParameterValues()
Dim disValue3 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue3.Value = 2005
parValue3.Add(disValue3)

Dim parValue4 As ParameterValues = New ParameterValues()
Dim disValue4 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue4.Value = 1
parValue4.Add(disValue4)

Dim parValue5 As ParameterValues = New ParameterValues()
Dim disValue5 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue5.Value = 1
parValue5.Add(disValue5)

Me.CustomersReport.DataDefinition.ParameterFields("@ser").ApplyCurrentValues
(parValue1)
Me.CustomersReport.DataDefinition.ParameterFields("@facility").ApplyCurrentV
alues(parValue2)
Me.CustomersReport.DataDefinition.ParameterFields("@year").ApplyCurrentValue
s(parValue3)
Me.CustomersReport.DataDefinition.ParameterFields("@company").ApplyCurrentVa
lues(parValue4)
Me.CustomersReport.DataDefinition.ParameterFields("@rate").ApplyCurrentValue
s(parValue5)

SetDBLogonForReport(myConnectionInfo, CustomersReport)

Params()

MsgBox("drugi params")

myCrystalReportViewer.ReportSource = CustomersReport

End Sub



Private Sub SetDBLogonForReport(ByVal myConnectionInfo As
ConnectionInfo, ByVal myReportDocument As ReportDocument)
Dim myTables As Tables = myReportDocument.Database.Tables

For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In
myTables
Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo

myTableLogonInfo.ConnectionInfo = myConnectionInfo
myTable.ApplyLogOnInfo(myTableLogonInfo)
Next
End Sub

Got it. I had to add the following line of code:

myTable.Location = myConnectionInfo.DatabaseName & ".dbo." &
myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)


between the lines:

myTable.ApplyLogOnInfo(myTableLogonInfo)

myTable.LogOnInfo.ConnectionInfo.ServerName = myConnectionInfo.ServerName


in the SetDBLogonForReport sub (in ref to code in my previous post). Dead
easy. Who would've thought of it?

BR everyone.

GAZ

Can you post the full code, I have the same problem...
'
Thanks,

Alex

"Gordan A Ziza" <gziza@newsgroup.nospam> wrote in message
news:%23c49cqd6FHA.3648@tk2msftngp13.phx.gbl...
> Got it. I had to add the following line of code:
>
> myTable.Location = myConnectionInfo.DatabaseName & ".dbo." &
> myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)
>
>
> between the lines:
>
> myTable.ApplyLogOnInfo(myTableLogonInfo)
>
> myTable.LogOnInfo.ConnectionInfo.ServerName = myConnectionInfo.ServerName
>
>
> in the SetDBLogonForReport sub (in ref to code in my previous post). Dead
> easy. Who would've thought of it?
>
> BR everyone.
>
> GAZ
>
>
>
>

Here comes the full code. Make note of imports, it won't work without them.
I kept all the code in the form, but you can easiliy change
SetDBLogonForReport and Params to server all your reports. And make note of
'myTable.Location = ' in the SetDBLogonForReport sub. If you miss that one
it will not work whatever you do.

One cannot but to comment on how difficult BusinessObject made all of this.
Strange! Would it not be somewhat easier to have properties
StoredProcedureName, ServerName, DatabaseName and Parameters collection
rather then going through all the code below. And setting datasource for
each table in a report separately (try it on for size with subreports). I
don't know. Maybe Microsoft will improve the SQL ReportsService and we can
all move away from Crystal. It is getting far too tedious to work with them.

Best regards,

GAZ





Imports System.Collections
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

myConnectionInfo.AllowCustomConnection = True
myConnectionInfo.ServerName = "athens"
myConnectionInfo.DatabaseName = "asdtreasury35"
myConnectionInfo.IntegratedSecurity = True
myConnectionInfo.Type = ConnectionInfoType.SQL

Params()

SetDBLogonForReport(myConnectionInfo, crystalreport11)

myCrystalReportViewer.ReportSource = crystalReport11
End Sub

Private Sub SetDBLogonForReport(ByVal myConnectionInfo As
ConnectionInfo, ByVal myReportDocument As ReportDocument)
Dim myTables As Tables = myReportDocument.Database.Tables

For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In
myTables
Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo

myTableLogonInfo.ConnectionInfo = myConnectionInfo
myTable.ApplyLogOnInfo(myTableLogonInfo)
myTable.Location = myConnectionInfo.DatabaseName & ".dbo." &
myTable.Location.Substring(myTable.Location.LastIndexOf(".") + 1)
myTable.LogOnInfo.ConnectionInfo.ServerName =
myConnectionInfo.ServerName
Next
End Sub

Private Sub Params()
Dim parValue1 As ParameterValues = New ParameterValues()
Dim disValue1 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue1.Value = 55
parValue1.Add(disValue1)

Dim parValue2 As ParameterValues = New ParameterValues()
Dim disValue2 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue2.Value = 1
parValue2.Add(disValue2)

Dim parValue3 As ParameterValues = New ParameterValues()
Dim disValue3 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue3.Value = 2005
parValue3.Add(disValue3)

Dim parValue4 As ParameterValues = New ParameterValues()
Dim disValue4 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue4.Value = 1
parValue4.Add(disValue4)

Dim parValue5 As ParameterValues = New ParameterValues()
Dim disValue5 As ParameterDiscreteValue = New
ParameterDiscreteValue()
disValue5.Value = 1
parValue5.Add(disValue5)

Me.crystalReport11.DataDefinition.ParameterFields("@ser").ApplyCurrentValues
(parValue1)
Me.crystalReport11.DataDefinition.ParameterFields("@facility").ApplyCurrentV
alues(parValue2)
Me.crystalReport11.DataDefinition.ParameterFields("@year").ApplyCurrentValue
s(parValue3)
Me.crystalReport11.DataDefinition.ParameterFields("@company").ApplyCurrentVa
lues(parValue4)
Me.crystalReport11.DataDefinition.ParameterFields("@rate").ApplyCurrentValue
s(parValue5)
End Sub
End Class

Hi Gordon,

Thanks for that great post! I was having the same problem and a heck of a ti
me tracking it down.

Here is a modified version of your solution that worked for me:

	TableLogOnInfo tblLogonInfo = myTable.LogOnInfo;		tblLogonInfo.ConnectionIn
fo.ServerName = dbSvrName;
	tblLogonInfo.ConnectionInfo.DatabaseName = dbName;
	tblLogonInfo.ConnectionInfo.UserID = ""; 
	tblLogonInfo.ConnectionInfo.Password = ""; 
	myTable.ApplyLogOnInfo(tblLogonInfo);
        myTable.Location = dbName + ".dbo." + myTable.Name;

....and, as you pointed out, the same must be done for each table in each s
ubreport.

-gwarnes

 

Thanks!!!  This worked for me.  What a simple fix for a pain in the but prob
lem.

Crystal support (Businiss Objects) was no help after searching their site.  
Found this answer through MSDN communities.  Nice job MS.

Jim

HI, can you help me with the previous step of the parameter sending?

I don't know how to design the report from an stored procedure, I add the cr
ystal report to the solution and the expert prompts for the data, then I add
 the stored procedure from and ole db connection in the data tab, then I cli
ck next and all the buttons excepts the cancell are disabled !!!

I am stocked there, please I need your help urgently
 

Best regards

Alfonso Calderon

hi,

is this same process to  apply login info for only main report?
if no then
how to apply login info for subreport?

regards,
shrihari