How to fix SQL query exceeds specified time limit or storage limit against AS400 Data Source

 I implmented an data loading with VB script that executing in the Look Server, I have the following connection string to access the data source.

VarConString = "DRIVER=Client Access ODBC Driver (32-bit); UID=as400User;PWD=mypassword;SYSTEM=MyDataBaseName"

But I got a very weird error on the execution. the error message is shown below

Microsoft OLE DB Provider for ODBC Drivers

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0666 - SQL query exceeds specified time limit or storage limit.

the google search suggest the fix is to modify the ODBC configuraton to increase the execution time limit and storage limit.

However it is a better approach to use a different data driver. such as the IBM iSeries driver to solve those issue.

i simply replace the connection string with the following connection string, the the error is gone and data return from the execution.

VarConString = "Provider=IBMDA400; User Id=as400User;Password=mypassword;Data source=MyDataBaseName"

 

SQL Server Connection Timeout issue with .Net Applicaiton

I had an Asp.Net Application to manually a very complicate Database Update against the SQL Server.


But the Test always failed with Sql conncection Timeout Error


Exception Type: System.Data.SqlClient.SqlException
Exception: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
Source: Save Data
Stack Trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at CustomerDataDeduplicationDataAccess.CustomerDataDeduplicationDA.SaveMasterRecord(CustomerDataDeduplicationBE customerData, String[] duplicatedRecordProspectList, String userName) in C:\Users\ddeng\Documents\Visual Studio 2005\Projects\AS400ToSQL\CustomerDataDeduplication_Web\CustomerDataDeduplicationDataAccess\CustomerDataDeduplicationDA.cs:line 422
Target Site: Void OnError(System.Data.SqlClient.SqlException, Boolean)


Initially I try to increase the execution Timeout in the Code Level


sqlCommand.CommandTimeout = 600;


However the issue still persistent. I try to set the connection Timeout in the code as well.
I received a compilation Error that sql connectio TimeOut Property is ready. we are not able to modify it
from the code.

the Solution is we should modify the Default Connection Time out in the connection string.

here is the fix that i performed in the Web.config file.
connectionString="Server=SQLSeverName; Initial Catalog=MyDatabase; Integrated Security=SSPI; Connection Timeout=600"


the connection time will increase from Defualt which is 15 Second to 10 minutes