We help IT Professionals succeed at work.
Troubleshooting Question

System.Data.SqlClient.SqlException in web base AP

35 Views
Last Modified: 2020-10-25
Hi,
   I create a view v_apply including seven tables to join in sql server for many years. Recently it always show System.Data.SqlClient.SqlException error message. If I execute the view in sql server management studio like the following-
select * from v_apply.
  It can show the result very well.
  Maybe the transaction log for the database is full I think.

  To fix this problem, change Recovery Model to Simple then Shrink Files Log

1. Database Properties > Options > Recovery Model > Simple
2. Database Tasks > Shrink > Files > Log

   To avoid timeout. I change the relevant CommandTimeout to 0 in sql server. Everything I can do.It still show System.Data.SqlClient.SqlException.

   So anyone can give me any suggestion?
   Thanks.

  jeff
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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 
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
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).

Author

Commented:
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
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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)
  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)
  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

 

  
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
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'


Author

Commented:
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
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
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  
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
And what happened if you select only 1 or couple of columns?
SELECT field1 FROM v_apply

Author

Commented:
Hi Máté Farkas ,
The following command can't execute in AP-
SELECT * FROM v_apply
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
SQL Server Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions