Crystal Reports 8.5 - How to change SQL Server name on thousands of Crystal Reports that have the old SQL Server name hard-coded in it.
I have thousands of Crystal Reports using version 8.5 on a MS Windows 7 platform. We have an upgraded 2016 SQL server that these reports must use and the name and password of the SQL server has changed as well. These reports were written 20+ years ago.
The Crystal reports that have the SQL server name hard coded in them, do not work.
If I try to use a text editor like wordpad or notepad to edit the Crystal Report file and update the SQL server name, and just save it - when we try to run the reports, we get a very simple "DOS error".
Since we are on a time crunch to get this done - is there a tool or some other options that I can use to update a whole directory's worth of Crystal reports that have the hard-coded SQL server name in them? I can't do it one at a time or it will take me years - there's thousands of crystal reports on our directories for this client that we have to modify, to make sure they work with the new SQL server name and password.
I tried to run an individual report, that currently has the old SQL server name in it, and I expected that it will prompt me for the new SQL server name. It did prompt me. I typed in the new SQL server name, ID and PW, but it gave me a login error "Cannot connect to SQL Server [SQL Server name]. I know all the information is correct.
So I then went into the report, chose Database => Add new database, selected the new SQL server name, database and the stored procedure that the report is trying to run. I get error "No rowset was returned for this table, query, or procedure". I have read/write access to the new SQL server (I am using the system administrator ID/PW for everything, I have admin access to the file-share locations, but I'm just not a Crystal Reports person. Can anyone help me?
ASKER
When I try to Add Database to Report => Data Explorer and there is no current connection - so my only option is ODBC. When I select the "Warehouse" database (for example), it shows under the ODBC option, it brings up the SQL Server Login where I can put the Login ID and password, just like other reports (this works on other reports).
So I try"Trusted Connection" whereas I can specify the SQL server name, and select the stored procedure to run and everything looks fine. Once I select the stored procedure the result is Crystal Reports: Database Error ODBC error: Microsoft SQL Server Native Client 11.0 SQL Server String or binary data would be truncated.
The only real difference I can see between the reports that work, and the ones that do not - is the reports that do not work have the SQL server name in the file (when I look at the RPT file with a text editor).
If the old server is not on the network, presumably the new server uses the same setup in terms of the use of the Instance naming if any.
see if you create a DNS entry for the oldserver name in this report to point to the new server either as an Alias, CNAME or directly, using an A record for oldservername IN A new_server_ip_address
and see if you regain functionality of the report in question.
If memory, served, you have to use the database explorer to connect to the new server and add it as the data source, but when I tried that, often the report will get messed up.
ASKER
The Windows 7 has SQL 2000; Crystal Reports 8.5; ODBC 32 bit; SQL Server Name SQL01
The Windows 10 has SQL 2016; Crystal Reports 8.5; ODBC 32 bit & 64 bit; SQL Server Name MDBS001
With that said - the crystal reports in the Windows 7 environment point to SQL01
The new Windows 10 environment - the crystal reports have to point to MDBS001. SQL01 will no longer exist.
So I have a few test crystal reports. Two of them use the ODBC to point to the new SQL server MDBS001. And when I look at the RPT file with a text editor, you don't see SQL01 in the file. These 2 reports work fine.
The third report - if I look at the RPT file with a text editor - I see the SQL01 in the (unreadable) file. I'm trying to use the current ODBC entry but it just won't connect. I just selected Verified database, filled in the SQL server name, ID and password and message said "Could not connect to database ", so then I clicked on trusted connection and received this message "Connection failed, SQL State: '08004' Error 4060, Server rejected the connection; Access to selected database has been denied. SQLState: '28000', SQL Server Error: 18456; [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user kbolles (me). My guess at this point is that there is some permission issue going on. Ugh....
c:\windows\system32\driver
put the IP of the new server, but point it
a.b.c.d[tab]SQL01
and see if that works.
ASKER
The other option is within DNS to nap the ok'd server name to point at the newserver ip.