Jim Lawrence (AccessD)
accessd at shaw.ca
Wed Dec 17 19:59:07 CST 2003
Hi Jeanine: Everytime you make a ADO call: Dim objCmd As ADODB.Command Dim rs1 As ADODB.Recordset Dim MyConnectionVariable As String MyConnectionVariable = "Provider=SQLOLEDB;Initial Catalog=MyDataBase;Data Source=MySQLServer;Integrated Security=SSPI" Set objCmd = New ADODB.Command Set rs1 = New ADODB.Recordset With objCmd .ActiveConnection = MyConnectionVariable .CommandText = "MyStoredProcedure1" .CommandType = adCmdStoredProc or... .CommandText = "Select * from MyFavouriteTable" .CommandType = adCmdText End With rs1.CursorLocation = adUseClient rs1.Open objCmd, , adOpenDynamic, adLockOptimistic With rs1 If .BOF = False Or .EOF = False Then .MoveLast End With Set objCmd = Nothing HTH Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jeanine.Scott at wellsfargo.com Sent: Wednesday, December 17, 2003 7:00 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Performance Access FE and SQL server BE [help!] Where is this set? Do I set this in my dsnless connection? .ConnectionString = "Provider=SQLOLEDB; " & _ "Data Source=" & gvarSQLPARDataLocation & "; " & _ "Initial Catalog=" & gstrDBName & "; " & _ "Integrated Security=SSPI;Persist Security Info=False;" -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Curtis, Andrew (WAPL) Sent: Tuesday, December 16, 2003 11:54 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Performance Access FE and SQL server BE [help!] Try your network transports from both SQL and client ends,TCP should take precedent over named pipes. andrew -----Original Message----- From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca] Sent: Wednesday, 17 December 2003 9:28 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Performance Access FE and SQL server BE [help!] Hi Jeanine: You wouldn't have a time-limited version of MSSQL would you? Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jeanine.Scott at wellsfargo.com Sent: Tuesday, December 16, 2003 8:06 AM To: accessd at databaseadvisors.com Subject: [AccessD] Performance Access FE and SQL server BE [help!] Performance Access FE and SQL server BE [help!] New SQL database. Existing Access97 app connects to the SQL db via a dsnless connection using NT authentication. Application resides on client. There are approx 15 users in the app. The backend data is about a gig. The application is code heavy. Almost all the processes are ADO direct calls. The command and connection timeout are both set at 5000. Great performance for the first 4 days. Last Tuesday the performance started to slow and from then to now we are alternating between acceptable although not great preformance to a crawl speed (7-10 minutes!). I have done the following: 1.) reinstalled MDAC 2.) reinstalled Access 3.) compacted and repaired the front end app 4.) rebuilt the indexes on SQL 5.) had a trace put on to see if there are any locking issues (there are not) 6.) had a network sniffer put on to determine bandwidth utilization. 25% utilization no packets being dropped. 7.) checked utilization on the server (10% utilization most of the time) 8.) ruled out any type of SMS issue (one of the errors pointed me in this direction) Some of the errors we have received have been: * login failed for user [null]: not a trusted sql connection. (this is after they have logged in and recordsets/data has been returned from the server.) * DBNETLIB connection read. recv(). General network error check your network documentation. * timeout expired and then under that is the following "general network error: check your network documentation" Can anyone help? If you have any ideas on anything to do with the SQL server or network please let me know what I should say to the DBA and the network contact. Thank you!!!! _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com CONFIDENTIAL COMMUNICATION If you receive this confidential communication by mistake, please contact the sender immediately by return electronic mail. Worsley Alumina Pty Ltd ABN 58 008 905 155 is the manager of the Worsley Joint Venture - Bauxite/Alumina Operation. Liability and responsibility of the Joint Venturers is several in accordance with the following schedule of participating interests: Billiton Aluminium (RAA) Pty Ltd 56 percent, Billiton Aluminium (Worsley) Pty Ltd 30 percent, Japan Alumina Associates (Australia) Pty Ltd 10 percent, Nissho Iwai Alumina Pty Limited 4 percent. _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com