VERY strange problem with DB Instance

[Vance Kessler]

I am sure this is a configuration or permissions problem, but I cannot
figure out what it might be.

I have 2 SQL 2000 database servers: one is a linked Windows 2003 based
server using a specified login account and the main server is Windows
2000. The user on the linked server is an SA. Both are running SP
3a.

I EXEC a select statement that selects data from this linked server
(shown below in detail) that runs just fine when executed by itself.
However, if I try to insert the results of the EXEC into a temp table
it NEVER returns! In fact through Profiler I can see that it never
even hits the other database. I left it running for an hour and 18
minutes one time. Even canceling the query took over 6 minutes. I am
doing my tests from Query Analyzer (logged in as an Admin), but my
ASPX pages fail as well.

So, here is the select statement that works:

DECLARE @SQLString varchar(1024)
SELECT @SQLString = 'select name, ''win2003-testsvr\Data1''
from
[win2003-testsvr\Data1].master.dbo.sys databases
where status & 1024 != 1024 --avoid read only
order by name'
EXEC(@SQLString )

The above query returns the results expected. However this query
never returns:
CREATE TABLE #tblDatabases
(
DBName nvarchar(256),
DBInstance varchar(128)
)
GO
DECLARE @SQLString varchar(1024)
SELECT @SQLString = 'select name, ''win2003-testsvr\Data1''
from
[win2003-testsvr\Data1].master.dbo.sys databases
where status & 1024 != 1024 --avoid read only
order by name'
INSERT INTO #tblDatabases EXEC(@SQLString )
GO
select * from #tblDatabases
drop table #tblDatabases

If, I run this query and use the name of the current database server,
it runs as expected. It is only when I try to access this particular
server that it hangs. Actually, I even replaced the server instance
above with another server instance running on a different Windows 2003
machine and that worked.

So this is obviously a configuration or permissions issue, but what
kind of setting or permission would cause an insert into a temp table
to fail for a select statement!!

Please HELP,
Vance

[Erland Sommarskog]

Vance Kessler (vk******@peach tree.com) writes:

I EXEC a select statement that selects data from this linked server
(shown below in detail) that runs just fine when executed by itself.
However, if I try to insert the results of the EXEC into a temp table
it NEVER returns!

Hm, have you checked that MSTDC is running on the other machine?
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Vance Kessler]

Yes, the Distributed Transaction Coordinator (msdtc.exe) is running on
both systems.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[WangKhar]

ermm - dumb q perhaps...

but have you double and triple checked all the columns, servername etc
are actually spelt right (poss incl case) - errors dont seem to
transmit well over the distributed transactions... specially if the
table doesnt exist.

Vance Kessler <vk******@peach tree.com> wrote in message news:<3f******* *************** *@news.frii.net >...

Yes, the Distributed Transaction Coordinator (msdtc.exe) is running on
both systems.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Vance Kessler <vk******@peach tree.com> wrote in message news:<3f******* *************** *@news.frii.net >...

Yes, the Distributed Transaction Coordinator (msdtc.exe) is running on
both systems.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I have no idea myself, but I found this on Google - the same problem,
caused by not rebooting after installing a servicepack:

http://groups.google.com/groups?hl=e...%40tkmsftngp09

Simon

[Vance Kessler]

I FOUND IT! I found the following in another post:

---------------------------------------------------------------
BY default Win 2003 server has the following two things disabled. You
need to enable them and reboot your win2003 sql server.
Under add/remove programs/windows components/application server/
enable network COM+ and Enable Network DTC access.
---------------------------------------------------------------
I found this when I noticed the following in the event log:

Event Type: Error
Event Source: MSDTC
Event Category: CM
Event ID: 4357
Date: 9/10/2003
Time: 8:16:34 AM
User: N/A
Computer: ANDROMEDA
Description:
MS DTC is unable to communicate with MS DTC on a remote system. No
common RPC protocol is supported between the two systems. Please
ensure that one or more of the following RPC protocols are common to
both systems: TCP/IP, SPX, or NetBEUI. Error Specifics:
..\iomgrclt.cpp :203, CmdLine: C:\WINNT\System 32\msdtc.exe, Pid: 612

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 21 00 00 00 20 00 00 00 !... ...

[tech.witch]

I sure am glad you posted this. I've been trying to figure out this
problem for 2 weeks without success. I made the suggested changes and
the error went away after rebooting the server. Wheew!

TW