为什么我对数据库进行查询操作时,总是Error:“超时已过期“
要查询的表的记录数有三百万条,以下是我写的存储过程:
CREATE PROCEDURE [CT_to_China]
(@table varchar(128),@tableCT varchar(128)) AS
begin
exec('insert into CT_to_China.billing.['+@table+']select pin,start_time,phone_number,Distinct_DB.clarentdb.[countrytable].country_name_en as country, duration,ceiling(duration/60.0) as minutes,
dnis,''zone'' as zone,ip_addr_ingress,ip_addr_egress,remote_domain,
domain,bill_type,new_call_id from Distinct_DB.billing.['+@tableCT+'],Distinct_DB.clarentdb.[countrytable] where (pin not like ''66%'') and (((ip_addr_ingress like''61.128.%'') or (remote_domain=''ChinaYT'')) and (ip_addr_egress like ''218.30.253.%'')) and (Distinct_DB.clarentdb.countrytable.country_code+''-'')=left(phone_number,charindex(''-'',phone_number)) order by start_time')
end
return
以下是我在VB里调用存储过程的代码:
If op4.Value = True Then
If comdatabase.Text = "" Then
comdatabase.SetFocus
Exit Sub
ElseIf comtable.Text = "" Then
comtable.SetFocus
Exit Sub
End If
connect_T.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;UID=billing;PWD=billing;Database=" & comIndatabase.Text & ";Data Source=192.168.1.9"
connect_T.Open
record_T.Open "select count(pin) from billing.[" & txtintable.Text & "]", connect_T, adOpenDynamic, adLockOptimistic, adCmdText
If record_T.Fields(0) = 0 Then
record_T.Close
waiting.Show
waiting.Refresh
With cmd_T
.ActiveConnection = connect_T
.CommandText = "CT_to_China"
.CommandType = adCmdStoredProc
Set prm_T = .CreateParameter("@table", adChar, adParamInput, 40, txtintable.Text)
.Parameters.Append prm_T
Set prm_T = .CreateParameter("@tableCT", adChar, adParamInput, 40, comtable.Text)
.Parameters.Append prm_T
Set record_T = .Execute
End With
Unload waiting
MsgBox "系统对" & comtable.Text & "表的过滤操作已完成!!!", vbExclamation, "OK"
通过以上的代码来查询包含三百万条记录的表时,总是报错“超时已过期”,请大家来帮帮我,我该怎么写才不会报错。急!!急!!急!!!!!!!!!!
要查询的表的记录数有三百万条,以下是我写的存储过程:
CREATE PROCEDURE [CT_to_China]
(@table varchar(128),@tableCT varchar(128)) AS
begin
exec('insert into CT_to_China.billing.['+@table+']select pin,start_time,phone_number,Distinct_DB.clarentdb.[countrytable].country_name_en as country, duration,ceiling(duration/60.0) as minutes,
dnis,''zone'' as zone,ip_addr_ingress,ip_addr_egress,remote_domain,
domain,bill_type,new_call_id from Distinct_DB.billing.['+@tableCT+'],Distinct_DB.clarentdb.[countrytable] where (pin not like ''66%'') and (((ip_addr_ingress like''61.128.%'') or (remote_domain=''ChinaYT'')) and (ip_addr_egress like ''218.30.253.%'')) and (Distinct_DB.clarentdb.countrytable.country_code+''-'')=left(phone_number,charindex(''-'',phone_number)) order by start_time')
end
return
以下是我在VB里调用存储过程的代码:
If op4.Value = True Then
If comdatabase.Text = "" Then
comdatabase.SetFocus
Exit Sub
ElseIf comtable.Text = "" Then
comtable.SetFocus
Exit Sub
End If
connect_T.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;UID=billing;PWD=billing;Database=" & comIndatabase.Text & ";Data Source=192.168.1.9"
connect_T.Open
record_T.Open "select count(pin) from billing.[" & txtintable.Text & "]", connect_T, adOpenDynamic, adLockOptimistic, adCmdText
If record_T.Fields(0) = 0 Then
record_T.Close
waiting.Show
waiting.Refresh
With cmd_T
.ActiveConnection = connect_T
.CommandText = "CT_to_China"
.CommandType = adCmdStoredProc
Set prm_T = .CreateParameter("@table", adChar, adParamInput, 40, txtintable.Text)
.Parameters.Append prm_T
Set prm_T = .CreateParameter("@tableCT", adChar, adParamInput, 40, comtable.Text)
.Parameters.Append prm_T
Set record_T = .Execute
End With
Unload waiting
MsgBox "系统对" & comtable.Text & "表的过滤操作已完成!!!", vbExclamation, "OK"
通过以上的代码来查询包含三百万条记录的表时,总是报错“超时已过期”,请大家来帮帮我,我该怎么写才不会报错。急!!急!!急!!!!!!!!!!
connect_t.commandtimeout=0
DbConnection.CommandTimeout = 0