We upgraded from SQL Server 2016 to 2019, and our reports on the report server now say:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The network path was not found
If I download the report and/or run the original from my box via Visual Studio, the report works.
We got the report to work by changing our datasource connection string InStr() to uppercase for the first parameter, and the new server URL is uppercased.
Our expression works now after updated to the following, but problem is we have 50+ report to now do this for. Looks like this was due to a fix:
KB4535706 makes SSRS Report URL's Case Sensitive
="Data Source=server-" &
IIF(IsNothing(Globals!ReportServerUrl), "app",
Switch(
InStr(Globals!ReportServerUrl, "-BBSQL-"),"app",
InStr(Globals!ReportServerUrl, "-CCSQL-"),"app2"
IIF(IsNothing(Globals!ReportServerUrl),"devl",
Switch(
InStr(Globals!ReportServerUrl, "-DEVL"),"devl",
InStr(Globals!ReportServerUrl, "-BETA"),"beta",
InStr(Globals!ReportServerUrl, "-PROD"),"prod"
) & ";Initial Catalog=" & Parameters!DB.Value
After the server is updated, the previous URL path will become invalid as the server name changes.
I am glad to hear that your issue has been resolved. To be honest, this kind of troubleshooting issue is difficult to resolve without knowing the server environment. Because it may contain one or more problems. And improper operation can also cause some errors.
Hi
@CC
，
Please follow the steps below to troubleshoot:
1.Make sure SQL Server instance is running.
Go to
Services
option, click on SQL Server installed in your system, I have SQL Server instance installed with name: MSSQLSERVER. If it is in not Started Status, then start it by right clicking on it and click on
START
option. If it is already running, please
restart
your MSSQLSERVER service.
2. Make sure that TCP/IP is enabled.
To make it enable follow the steps: Click on
Configuration Manager
of SQL Server. Now you can check the
TCP/IP
port status as
Enabled
or
Disabled
. You need to make it
Enable
and click on status to change port
Properties
.
Now fill Default Port no
1433
click on
OK
button.
3. Allow Remote Connections enabled under Connections in SQL Server Properties:
In the Server Properties under the
Connections
Options, you need to check the box of
Allow remote connections to this server
and then click on
OK
button.
4. Allow SQL Server in Firewall Settings:
You need to add a
Windows Firewall exception
on the server for SQL TCP ports
1433
and
1434
, so that SQL Server will run.
Go to
Control Panel
then
System and Security
or directly search it on you system search as
Windows Firewall
in this click on Firewall, Here you can see Action tab as Allow for Firewall. You can change it through SQL Server
Properties
to allow or block. As given in the below image.
5.Check that you are using the correct SQL Server instance name.
Make sure you are using the correct instance name. When you connect to a default instance, machinename is the best representative for the instance name and when you connect to a named instance such as sqlexpress, you need to specify the instancename as follows: machinename\instancename where you enter the SQL Server instance name for instancename. Note that it is
SERVER\INSTANCENAME
instead of SERVER/INSTANCENAME. It's not a slash, it's a
backslash ( \ )
.
Best Regards,
If the answer is the right solution, please click "
Accept Answer
" and kindly upvote it. If you have extra questions about this answer, please click "
Comment
".
Note: Please follow the steps in our
documentation
to enable e-mail notifications if you want to receive the related email notification for this thread.
