Run-time error '-2147217871 (80040e31)'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Run-time error '-2147217871 (80040e31)'

Author

Topic

Lathu
Starting Member

10 Posts

Posted - 2011-07-20 : 02:42:00

When I try to run my code in Vb 6.0, and the code having the Linked Server Insert Call, I get this error..saying
Run-Time Error '-214721871(80040e31)' timeout expired.
But when I run the Linked Server Insert Call in SQL query analyzer.it runs fine. I have SqlServer 2000 with sp3, and using vb 6.0.

My Linked Server Insert Query is

insert into <TABLE1> select * from openquery(LinkServ,'select * from <TABLE1>')

This Linked Server "LinkServ" is defined for getting the Data from Oracle to SqlServer.

Pls Help Me..........

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 03:25:48

When you login from the application, do you have the same access/security rights as when you try in Query Analyser?

N 56°04'39.26"
E 12°55'05.63"

Lathu
Starting Member

10 Posts

Posted - 2011-07-20 : 04:31:26

Yes,
I am using the same UserID and password which is used in Query Analyser to login from the VB Code.

That UserID have complete access/security rights.

Dim con As New ADODB.Connection

con.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=MyLogin;pwd=MyLoginPass;Initial Catalog=MT3001;Data Source=(local)"

con.BeginTrans
con.Execute "insert into <TABLE1> select * from openquery(LinkServ,'select * from <TABLE1>')"
con.CommitTrans

Lathu
Starting Member

10 Posts

Posted - 2011-07-20 : 04:47:38

If i ran this Linked Server Insert command in Query Analyser, it is taking 55 seconds of time to getting the data. and that <TABLE1> having 4191 records.

Lathu
Starting Member

10 Posts

Posted - 2011-07-20 : 05:15:38

I got the Solution.

Dim con As New ADODB.Connection

'added below 2 lines
con.ConnectionTimeout = 0
con.CommandTimeout = 0

con.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=MyLogin;pwd=MyLoginPass;Initial Catalog=MT3001;Data Source=(local)"

con.BeginTrans
con.Execute "insert into <TABLE1> select * from openquery(LinkServ,'select * from <TABLE1>')"
con.CommitTrans

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources