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
