By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,213 Members | 2,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,213 IT Pros & Developers. It's quick & easy.

VERY strange problem with DB Instance

P: n/a
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.sysdatabases
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.sysdatabases
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
Jul 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Vance Kessler (vk******@peachtree.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
Jul 20 '05 #2

P: n/a


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!
Jul 20 '05 #3

P: n/a
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******@peachtree.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!

Jul 20 '05 #4

P: n/a
Vance Kessler <vk******@peachtree.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
Jul 20 '05 #5

P: n/a
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\System32\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 !... ...
Jul 20 '05 #6

P: n/a
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

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.