ExportToDisk error

[Former Member]

I have a powershell script that executes a Crystal Report (there is no subreport) which takes three parameters, retrieves it's data from SQL Server, and writes/saves the report to a network share.

I am able to execute the powershell script successfully.

Another user is not able to execute the script successfully.

This user:

1. Is able to UNC to the network share, and

2. Is able to read and write to the network share

3. Is able to successfully write the error to the same SQL Server in which the report gets its data (See Try...Catch statement in the code below).

The error this user is getting, is the following: Exception calling "ExportToDisk" with "2" argument(s): "Failed to open the connection. [Name of Report] {16D21D5B-3494-5B3E-B3D9-3DFF7D467GDB}.rpt"

The following is the powershell code as pertains to the ExportToDisk error:

$BeginDate = Read-Host 'Enter begin date using format MM/DD/YYYY '

$date = $BeginDate

$EndDate = $BeginDate

$BeginDate = $BeginDate + " 02:00:00"

$EndDate = $EndDate + " 01:59:59"

$EndDate = ([datetime]$EndDate).AddDays(1) #Get datetime stamp to append to file name, in the form of YYYYMMDD_YYYYMMDD_HHMMSS

$CurrentDate = get-date

$CurrentDate = $CurrentDate.ToString("yyyyMMdd")

$date = $date.Replace("/", "")

$time = get-date -Format HH:mm:ss

$time = $time.Replace(":", "")

$datetime = $date + "_" + $CurrentDate + "_" + $time #YYYYMMDD_YYYYMMDD_HHMMSS

[string] $UserName = $env:UserName

$Property = "SiteName"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=ServerName;Database=Reporting;Integrated Security=True" $SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.Connection = $SqlConnection

[reflection.assembly]::LoadWithPartialName('CrystalDecisions.Shared') | Out-Null [reflection.assembly]::LoadWithPartialName('CrystalDecisions.CrystalReports.Engine') | Out-Null

$report = New-Object CrystalDecisions.CrystalReports.Engine.ReportDocument

[string] $LocalReportPath = "C:\Users\$UserName\Documents\CrystalReports\[Name of Report].rpt"

$ReportPath = "S:\Reporting\SiteName\" #This is for saving to the SQL table for historical or error occurrence. $FileLoc = "\\ServerName\Reporting\SiteName\" #This is path for saving to the network server, which equates to the S drive on ServerName.

$ReportNameLive = "TableGames_XX_" + $datetime + ".pdf"

$FileName = $FileLoc + $ReportNameLive

$ReportFullPath = $ReportPath + $ReportNameLive

$report.SetParameterValue("@Property", $Property)

$report.SetParameterValue("@BeginDate", $BeginDate)

$report.SetParameterValue("@EndDate", $EndDate)

$SqlCmd.CommandText = "INSERT dbo.ReportTracker ( UserName, ReportNameLive, [FileName], ReportFullPath, BeginDateParam, EndDateParam ) VALUES ('$UserName', '$ReportNameLive', '$FileName', '$ReportFullPath', '$BeginDate', '$EndDate' )"

try {

$report.ExportToDisk("PortableDocFormat", $FileName)

$SqlCmd.ExecuteNonQuery() | Out-Null

Write-Host "Successfully created" $ReportNameLive -ForegroundColor Green }

catch {

$ErrorMessage = "[XX] " + $_.Exception.Message

Write-Host -ForegroundColor Red $ErrorMessage

$SqlCmd.CommandText = "INSERT dbo.ReportErrorTracker ( UserName, ReportNameLive, [FileName], ReportFullPath, BeginDateParam, EndDateParam, ErrorMessage ) VALUES ('$UserName', '$ReportNameLive', '$FileName', '$ReportFullPath', '$BeginDate', '$EndDate', '$ErrorMessage')"

$SqlCmd.ExecuteNonQuery() | Out-Null

$SqlConnection.Close()

$report.close()

exit 1 }

[Former Member]

I am still getting the same ExportToDisk error. The ODBC bit-ness is the same. Is there some type of simple test I can perform to identify the reason for this export to disk connection error?

[former_member292966]

Hi Robert,

I don't see anywhere the report is logging onto SQL Server. That explains the original error:

Exception calling "ExportToDisk" with "2" argument(s): "Failed to open the connection. [Name of Report] {16D21D5B-3494-5B3E-B3D9-3DFF7D467GDB}.rpt"

I see the SqlConnection but the report won't use that. The report needs it's own connection to the database using LogonServer or LogonInfo.

Hope this helps,

Brian

[Former Member]

Thanks Brian. I am trying to understand what is going on, and you are likely leading me in the right direction. But things are still not to my understanding.

Since (as stated in my original post), I am able to execute the powershell script successfully. Therefore, based on your suggestion, "The report needs it's own connection to the database using LogonServer or LogonInfo.", I have added the following report connection info to the script (just prior to the $report.Load, $report.SetParameterValue, and $report.ExportToDisk functions), PURPOSEFULLY entering an incorrect password, and the script and report both execute successfully as before.

foreach ($Table in $report.Database.Tables) {

$table

$tli = $Table.LogonInfo

$li = $tli.ConnectionInfo

Write-host "location : $($table.location)"

$li.ServerName = "MyServer"

$li.DatabaseName = "MyDB"

$li.UserID = "MyUserName"

$li.Password = "incorrect password"

$Table.ApplyLogOnInfo($tli)

$table.location

}

So, I am wondering: Why is the report still executing successfully for me with an incorrect password? I would like it not to work with an incorrect password, thereby being more assured the EXPORTTODISK issue is related to your suggestion about establishing LogonInfo credentials.

[former_member292966]

Hi Robert,

Open the report in Crystal Reports and go to the File menu. If there is a check beside Save Data with Report, remove it. Save the report and try running under your account.

I'm hoping it fails for you. I say this because Crystal Save Data with Report has been an interesting pain for a very long time. What that option does is it saves a copy of the result set from the last time you ran the report in Crystal. So it's not actually logging onto the database to get the data, it's already in the RPT file. So it will ignore your LogonInfo and just use the stored data in the report.

Now that you've turned that off, it should always try logging into the database and thus fail with your incorrect credentials. Set it with the correct password and cross your fingers. If it works, then you'll know why I hate Save Data with Report. You can turn off that option in the Settings.

Good luck,

Brian