Strange error when executing a script against

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 2005 Forums

Transact-SQL (2005)

Strange error when executing a script against

Author

Topic

xrpshadow
Starting Member

1 Post

Posted - 2007-10-09 : 09:55:07

I get a strange error when executing a select statement on one database but not the other.

OLE DB provider "SQLNCLI" for linked server "Myserver01" returned message "Cannot create new connection because in manual or distributed transaction mode.".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "SELECT TOP 1 1 FROM "rr_working"."dbo"."tblstudents" "Tbl1006"" against OLE DB provider "SQLNCLI" for linked server "Myserver01".

The Strangest part is I am runnnig the same statement on another database with the same tables, permissions and structure and getting results on one and the previous error on the other.

Here's the setup, I'm on Myserver02 we'll call it, which is SQL2005 it is querying Myserver01 which is sql2000 and I am running the following tsql:

use DB1
select rm00101.custnmbr
from rm00101
where rm00101.custnmbr like 'st%'
and rm00101.custclas like 'WW_%'
and replace(rm00101.custnmbr, 'st','')
not in (select student_id from Myserver01.rr_working.dbo.tblstudents)
and custnmbr not in (select custnmbr from sop10100)
and custnmbr not in (select custnmbr from sop30200)
and custnmbr not in (select custnmbr from rm00104)
and custnmbr not in (select custnmbr from rm00401)
and custnmbr not in (select custnmbr from rm10101)
and custnmbr not in (select custnmbr from rm20101)

And I get no results, as expected.

when I run the exact same statement against DB2 I get the error (use DB2).

Both are being run as sa to troubleshoot the issue.
Any ideas?

Thanks,
XrpShadow

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-10 : 11:22:46

Have a look at the compatability mode of the database.
I would do this as two statements - retrieving the remote data then doing the select.
so
create table #a(student_id int)
insert #a select student_id from Myserver01.rr_working.dbo.tblstudents

select rm00101.custnmbr
from rm00101
where rm00101.custnmbr like 'st%'
and rm00101.custclas like 'WW_%'
and replace(rm00101.custnmbr, 'st','')
not in (select student_id from #a)
and custnmbr not in (select custnmbr from sop10100)
and custnmbr not in (select custnmbr from sop30200)
and custnmbr not in (select custnmbr from rm00104)
and custnmbr not in (select custnmbr from rm00401)
and custnmbr not in (select custnmbr from rm10101)
and custnmbr not in (select custnmbr from rm20101)

drop table #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources