asked on

Connection.BeginTrans does not work

Hi Gurus,

I am facing a problem with connection.BeginTrans in Visual Basic.

I am issuing a command

connectionObj.Execute "Insert into tbl select * from query(LinkedServerName",'Select * from tbl2')

It is working fine when I am not enclosing this line with Connectionobj.beginTrans and ConnectinoObj.Commit.

But it is giving some error like:
Error exporting data -2147217871

I am using Windows NT, SQL 7 and VB 6.
I am also using linked server.

I dont know weather can I enclose insert into command within the connection.begin and commit.

Is anybdy is there with an answer???
Waiting...

kkandwal

ASKER

Sorry some correction..
The exect line is :

connectionObj.Execute "Insert into tbl select * from openquery(LinkedServerName,'Select * from tbl2')"

TimCottee

kkandwal, this would appear to be a timeout or resource problem on the server. I would imagine that the select from the linkedserver is taking too long for a synchronous query and the connection is timing out. See http://support.microsoft.com/support/kb/articles/Q190/6/06.asp for more information on this.

sgpushpal

hi kkandwal

Try with
ConnectionObj.isolation read uncommited

rkot2000

You don?t need to use a transaction for single sql statement(by default for a single sql statement you got all or nothing).

But I think TimCottee ? is right this is timeout.

kkandwal

ASKER

Hi Timcottee

You r right that query is taking too long time and it is giving timeout error.

But when I am giving the same command without connection.begintrans

It is working without even taking .5 seconds.

I also went through the link which you have provided but it has the example with recordset, where as I am not using any of the recordset.

Please check it out.

All other answer are also not much helpful

ASKER CERTIFIED SOLUTION

DavidLester

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanRollins

Hi kkandwal,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

Split points between: TimCottee and DavidLester

kkandwal, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you. DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Netminder

Per recommendation, force-accepted.

Netminder
CS Moderator

TimCottee: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20328902