August 30, 2011 at 8:36 am
Hi
We have access serving as front end connecting to SQL server.
Our SQL Server was upgraded from version 2008 to 2008 r2 and the machine
has been replaced with a new one a couple of days ago as well. The access program
was working properly prior to the upgrade however we have been experiencing
one issue with it after the upgrade.
1.) We are getting below error when importing file into the SQL server
database using access, it is also calling a few stored procedure when
doing the file import. I have checked the ODBC profile and everything looks
ok. I have also tried to relinked the linked tables in access but it did not
fix the issue. I would appreciate if someone can help me on this.
Run-Time error '3146':
ODBC errors:
[Microsoft][SQL Server Native Client.10.0]Query expired
ODBC--call failed.
Access error:
ODBC--call failed.
Please see the Code below:
Private Sub cmdImport_Click()
If IsNull(txtCustFilePath) Then
MsgBox ("Required file path is missing.")
Exit Sub
End If
Dim strFileName As String
strFileName = GetextPart([txtCustFilePath])
'Insert data from Holding to Archieve table
'Delete data from Holding
Dim longEmpty As Long
longEmpty = DCount("TIME_PERIOD", "dbo_BS_NATIONAL_WKLY_ROLLING")
' to check against reference table if the file name existed
Dim ExistFile As String
ExistFile = Nz(DLookup("FILE_NAME", "File_Name", "FILE_NAME = '" & strFileName & "'"), "No File available")
Dim strSQL As String
strSQL = "EXEC INSERT_FILE_NAME_WK_ROL '" + strFileName + "'"
If ExistFile = "No File available" Then
Dim strRollingFilename As String
If longEmpty > 0 Then
'strRollingFilename = DMax("[FILE_NAME]", "[dbo_BS_NATIONAL_WKLY_ROLLING]")
'Dim Msg, Style, Title, Ctxt, Response, MyString
'Msg = "Do you want to archived previous week (Yes) or reupload the next file (No) :" + strRollingFilename + " ? "
'Style = vbYesNoCancel + vbQuestion + vbDefaultButton1
'Title = "Archived or Reupload"
'Ctxt = 1000
'Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'Select Case Response
'Case vbYes: ' User chose Yes.
'Insert data from Weekly Rolling to Holding table
RunPassThruQuery "EXEC WKLYROLLINGARCHIVED", "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False
'Case vbCancel:
'Exit Sub
'Case vbNo:
'MsgBox ("Second file's data will be uploaded") ' Perform some action.
'Delete data from Weekly Rolling - CAN NOT DELETE ANY MORE
'RunPassThruQuery "EXEC WKLYROLLINGDELETION", "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False
'End Select
End If
MsgBox "Importing to table: Weekly Rolling View"
'to reset the primary key to 0
RunPassThruQuery "EXEC SP_RESET_IDENTITY", "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False
'Imports data from folder to Weekly Rolling
DoCmd.TransferText acImportDelim, "ImportSpec", "dbo_BS_NATIONAL_WKLY_ROLLING", Me!txtCustFilePath, True
Dim strSQL1 As String
strSQL1 = "EXEC WKLYROLLINGUPDATEFILENAME '" + strFileName + "'"
'insert the filename and as_of_date
RunPassThruQuery strSQL1, "ODBC;DSN=SQLServer_DEV;Description=SQLServer_DEV; Trusted_Connection=Yes;DATABASE=DEV;", False
'delete any import bad data
DeleteImportErrorTbls
DoCmd.SetWarnings 0
MsgBox "Data transferred. You can proceed..."
Me.Requery
Else
MsgBox "File already exist. Please check the file"
End If
End Sub
Thank you in advance!
August 30, 2011 at 10:41 am
Well it seem pretty obvious that one of your queries is taking too long and timing out. You have several procs calls in this. You should probably start by isolating the proc that is timing out. Once you know which one is the problem take a look at the proc itself. It may be you need to rebuild indexes, recompile the proc, redesign it so it can run faster...
See if you can figure out which proc is causing the problem first and then we can help figure out why that one is a problem. There may be more than one causing problems.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply