Programmatically change OLE DB properties fails

[former_member807576]

Hello,

I have a crystal report (written in CR 2013 Pro), and a Visual Studio Pro 2013 console application using the CR for VS dlls to simply export the CR as a PDF.  When I hardcode the console application to provide the report the necessary login credentials, the PDF is successfully produced.  When I attempt to pass a variable credential to the same server that the crystal was originally written to connect to, it works.  It's when I try to change the server name and database that it fails.

Below is based off of adding snippets from other questions.  I remarked them out - again - works fine with using same Servername as what I used in CR designer, but it fails on the .VerifyDatabase() when I edit variable ServerName.

With crConnectionInfo

.ServerName = ServerName

.Password = PWord

.UserID = UserID

.DatabaseName = DbName

End With

With customerReport

If (DebugStatementsOnOutput = 1) Then

     returnValue = 3

End If

.Load(reportPath)

If (DebugStatementsOnOutput = 1) Then

     returnValue = 4

End If

.SetDatabaseLogon(UserID, PWord, ServerName, DbName)

'For Each crTable As Table In .Database.Tables

'    .SetDatabaseLogon(UserID, PWord, ServerName, DbName)

'Next

'For Each subReport As ReportDocument In .Subreports

'    For Each crTable As Table In subReport.Database.Tables

'        .SetDatabaseLogon(UserID, PWord, ServerName, DbName)

'    Next

'Next

If (DebugStatementsOnOutput = 1) Then

     returnValue = 41

End If

.VerifyDatabase()

If (DebugStatementsOnOutput = 1) Then

     returnValue = 5

End If

[DellSC · Accepted solution]

What type of database are you connecting to?

-Dell

[former_member807576 · Accepted solution]

SQL Server.
  We have a production and a cloned test environment.  The server name on production ends with "prod".  Test ends with "test" - and the report can be run on any 1 of 3 databases on each server, which is why I want to dynamically change the credentials.  Same user/password can log into all 6.

[DellSC · Accepted solution]

Will the report run if you don't verify the database?  Are there any differences at all between the test and prod databases - version of SQL Server, field names or types, table names, indexes, etc?

What version of SQL Server are you connecting to and what version of the SQL Server client are you using? Are you using ODBC or OLEDb to connect?  If it's ODBC, do you have the correct ODBC connections set up ?

What platform are you targeting in your application - 32-bit or 64-bit?  If you're using ODBC, do you have the appropriate "bit-ness" ODBC connections?

-Dell

[former_member807576 · Accepted solution]

Hi.

No.  It passes the Verify step.  I have several return-code changes for troubleshooting as the code is stepped through, and it is failing when it hits "ExportToDisk", which is after VerifyDatabase().

No differences in servers.  TEST is a clone of PRODUCTION, only the server name is changed.

Folder permissions on the share are OK.  Connection was ODBC, but I changed it to OLEDb to allow dynamic change of the server.

Thanks,

Bill

[DellSC · Accepted solution]

What version of SQL Server and what version of the SQL Server native connection are you using?

[former_member11696 · Accepted solution]

Don't verify, that can cause the report to use the same source as the report uses. Just make sure all DB tables are exactly the same.

Use TestConnectivety() if you want to check if its connected or not.

And use the corresponding MS SQL Native Provider provided by MS for each DB Server.

Don

[former_member807576]

Thanks for the help - With your help, I've got it working now - The code I was using had some things out of order (it seems), plus the help above.  Don - I incorporated your suggestion (just in case) - thanks!

Working code with some of the parameter stuff removed... 

Thanks again!

Bill

working code.txt.zip