System.Data.SqlClient.SqlException in web base AP

Post the exact exception message.

Reading from a view does not really depend on the state of the transaction log usage.

No, Don't reduce it 0, this means infinite wait time.

Hi ste5an ,
  The following is the error message-

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server.

   >>>>>>>>Don't reduce it 0, this means infinite wait time.
Thank you for your mention.

jeff 

This is obviously a network issue.

It means, that for whatever reason, your SQL Server instance did not answer. This can be a normal network interruption or defect hardware or a firewall.

Thus you need to look at the communication between your application and the SQL Server instance.

The "recently" in your post tells me, that you should also check the installations of the machines. .NET, drivers, etc. Not that an update changed some components your applications rely on.

Hi ste5an ,
 The view includes 40 fields.  
 I can execute the view in sql server management studio. So the view command is correct. When  I execute the view through AP and it will show the error message. Finally I remove some of fields in view, and it can execute in AP.

  I must find the reason when I remove some of fields in view and it can execute.

  Thank you for your help.

 jeff

I'm pretty sure that the error message is right. Your code cannot access the SQL Server instance. It's not about you running the query in SSMS.

Hi ste5an ,
 Thank you for your concern about the issue. The AP work for many years. Now only the view show the error message, and other views work fine.

 I will research the problem.

 Thanks.

jeff

That is an SQL connection error. Nothing with the view and nothing with the network and nothing with the application.
You should check the SqlConnection object associated to the SqlCommand because it cannot connect to sql server.
Check ConnectionString propery of SqlConnection object there will be the answer (the server name is wrong).

Hi Máté Farkas ,
 The AP was established for many years and work fine until recently. Maybe I should change the view to storeprocedure in    SQL server. The view is so complicated.

 Thank your for your suggestion.

jeff

NO, absolutely NOT for sure.
The message "A network-related or instance-specific error occurred while establishing a connection to SQL Server." exactly means that SqlClient object cannot connect to SQL Server. No problem with the view or stored procedure or anything on SQL Server side because the execution does not reach the SQL Server at all. It has no chance to run any sql statement or access any view or stored procedure.
You have to run your .NET code in debug mode and check the ConnectionString property of SqlConnection object or somehow print the ConnectionString property of the SqlConnection object in your application.

Hi Máté Farkas ,
  Everyday I backup the database. Now I have 2020-10-19.bak and 2020-10-20.bak for the system database.I restore the bak file to windows server 2016. I still can't execute the view command for my AP.

  After I restore the 2020-10-19.bak to  windows server 2016 and execute the following command in  SSMS -

exec  sp_helpdb  Database_Name

use   Database_Name
dbcc  shrinkfile([Database_Name_Log],2)

Select allOpen in new window

  Then the AP can execute the view command.

  But when I restore the 2020-10-20.bak to  windows server 2016 and execute the following command in  SSMS -

exec  sp_helpdb  Database_Name

use   Database_Name
dbcc  shrinkfile([Database_Name_Log],2)

Select allOpen in new window

  The same AP still can't execute the view command.But other views still work well. Maybe you can't believe it. But it's true. If the execution does not reach the SQL Server for the view, why do I restore the  2020-10-19.bak to SQL server and the view can work when I  shrinkfile the database in the same server? So something is wrong, I still can't find the reason. I am sure the server is fine,the network is fine and the ConnectionString property of SqlConnection  is correct.I think the problem is the database.But what happen? I don't know. Maybe the stored procedure improve performance.   

   In fact, I restore the bak file from 2020-05-01 to 2020-10-19 and shrinkfile the database in the same server,the view which join eight tables can show the data what I want. But when I restore 2020-10-20.bak to server,even I  shrinkfile the database,it will show the same error mesage.

  I really appreciate your help in resolving the problem. Thanks.

 jeff

 

  

OK, my hints:

• Update all statistics on all tables in that database (very important)
• Run: "exec sp_refreshview 'v_apply' "
• Run "SELECT * FROM v_apply" in SSMS first and wait until all rows are downloaded. Check how many seconds it takes.
• Temporary replace the part of the code which runs "SELECT * FROM v_apply" to another query for example "SELECT TOP 10 * FROM sys.objects". Does it work?
• Check error log of the Sql Server. There can be a database corruption which causes an unexpected server restart ( stack or mini dump). This would explain the error message you got.

Hi Máté Farkas ,
   All of the hints would be helpful for me. Can you explain Update all statistics on all tables in that database in advances.

  Thanks.

jeff

Be patient, it takes time (even couple hours) if your database is large (but it is does not block other processes):

exec sp_msforeachtable 'UPDATE STATISTICS ? WITH ALL, FULLSCAN'

Select allOpen in new window

Hi Máté Farkas ,
 I take a long time to execute all of the procedure step by step, again and again.
>>>>>>>Update all statistics on all tables in that database (very important)
  exec sp_msforeachtable 'UPDATE STATISTICS ? WITH ALL, FULLSCAN'

>>>>>>>Run: "exec sp_refreshview 'v_apply' "

>>>>>>>Run "SELECT * FROM v_apply" in SSMS first and wait until all rows are downloaded.
  It takes 2 second to show the data.

>>>>>>>Temporary replace the part of the code which runs "SELECT * FROM v_apply" to another query for example "SELECT TOP 10 * FROM sys.objects". Does it work?
 ya, it can execute the select command.

>>>>>>Check error log of the Sql Server. There can be a database corruption which causes an unexpected server
  After I execute the view command, and I check the log of the SQL Server. In this case, it seems logical to consider  that the AP is normal. In fact, I can't figure out any problem from the log for the AP.
  10/25/2020 10:52:57,spid58,Using 'xplog70.dll' version '2019.150.2000' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
10/25/2020 10:52:57,spid58,Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
10/25/2020 10:47:52,Server,Software Usage Metrics is enabled.
10/25/2020 10:47:34,spid51,Using 'xpstar.dll' version '2019.150.2000' to execute extended stored procedure 'xp_sqlagent_notify'. This is an informational message only; no user action is required.
10/25/2020 10:47:34,spid51,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
10/25/2020 10:47:34,spid51,Using 'xpsqlbot.dll' version '2019.150.2000' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
,and so on.

   Anyway, Thank you. I am very grateful.

  jeff

I don't have more ideas:

• How many rows were downloaded in SSIS in 2 seconds?
• What happened when you put "SELECT TOP 1 * FROM v_apply" into your application code?

Hi Máté Farkas ,
>>>>How many rows were downloaded in SSIS in 2 seconds?
 The total numers of rows is 5514 rows.
>>>>What happened when you put "SELECT TOP 1 * FROM v_apply" into your application code?
 The AP show the same error message.

 The total numbers of fields in the view is 79 in the view. I modify the context of the view and still join all of the tables to the following- just remove some field
SELECT field1,field2,field3,field4,field5......field75 FROM v_apply.
 The view can work well. It's obvious when I include some field  in the view it will show the error message.

 You are so kind. Thanks a lot.

 jeff  

And what happened if you select only 1 or couple of columns?

SELECT field1 FROM v_apply

Select allOpen in new window

Hi Máté Farkas ,
The following command can't execute in AP-

SELECT * FROM v_apply

Select allOpen in new window

When I  select from field1 to field75 ,the command can execute in AP.
When I  select from field76 to field79 ,the command can execute in AP.
When I  select from field1 to field15,and add field76、field77、field78、field79 ,the command can execute in AP.
I try to combine fields into the sql command of the view. I found if I include part of fields not all of the fields into the sql command of the view. Then the AP can execute the view. 
Wow, That’s strange! . How to explain?

jeff

Hi Máté Farkas ,
  I've learned so much from you. I must spend more time to research your suggestion.

  Thank you for your suggestion.

 best regards,

jeff