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 followed your suggestion to remove "Save Data with Report" and saved the report.

Unfortunately, even with this change the report still executes successfully for me with an incorrect password.

[former_member292966]

Hi Robert,

I just noticed your SQL connection has IntegratedSecurity as true. If that's the case, the password you pass to the report will be ignored.

If your SQL Server were not using Integrated Security then you would have to pass the Username and Password.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server

Let me know if this makes sense.

Thanks,

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 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

[DellSC]

If you're using an ODBC connection, does this other user have the same connection configured as you do? By this I mean not only does the connection exist with the same name, but is it the same "bit-ness" (32 or 64) as what is configured on your computer?

Also, the error says that only two parameters are being provided to the program, but you indicate that it needs three. I would verify that the other user is running it correctly.

-Dell

[Former Member]

Thanks Dell. I will check the ODBC "bit-ness". As per your statement "the error says that only two parameters are being provided to the program, but you indicate that it needs three"

I may not have clarified, the stored procedure behind the report needs three parameters, but the error is on ExportToDisk, which ExportToDisk requires two parameters. Both the stored procedure and ExportToDisk, when performing a Write-Host prior to the calls, both show the parameters are valid and not missing. Meaning, it is not a parameter issue on the ExportToDisk execution. Therefore, I will look into the ODBC "bit-ness".

Thank you.