November 14, 2005 at 9:45 am
I have a linked server setup to Windows 2003 but when I run SQL or a stored procedure against it, it just hangs and stays running forever. I run the same procedure against my Windows 2000 machines and I don't get this. Any ideas as to what needs to be changed?
November 14, 2005 at 10:15 am
That's odd
Have you checked the MSDTC settings on the windows 2003 box?
http://support.microsoft.com/?kbid=899607
Is there any difference in hardware between the windows 2003 box and windows 2000 box?
What are the patch levels of both boxes (OS & SQL Server)?
Any difference in default connection settings?
Is it possible to post the actual stored procedure?
November 15, 2005 at 7:12 am
Yes, I have checked the MSDTC settings and set those on all the Windows 2003 machines to Allow Network DTC Access.
The boxes are from the same vendor and make different levels of memory and CPUs though.
Windows 2003 No SP
Windows 2000 SP4
SQL SP3 build 922 or SP4 depends on the machine.
What I am doing is using several scripts to gather information for each of my 30+ SQL Servers into one database. I have a linked server to each of my other SQL Servers and query them directly at night to pull this information. All the linked servers are setup with the same settings and username and password.
Here is one of the procedures:
CREATE proc udp_DBA_UpdateJobList
as
set nocount on
DECLARE server CURSOR
READ_ONLY
FOR Select Server_ID, ServerName from dbo.udv_DBA_Non2003_ServerList
declare @sql nvarchar(4000)
DECLARE @name varchar(40)
declare @serverid int
OPEN server
FETCH NEXT FROM server INTO @serverid, @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
print @name
set @sql='update udt_DBA_Server_Jobs set name=s.name
from [' + @name + '].msdb.dbo.sysjobs s
inner join udt_DBA_Server_Jobs j on j.job_key=s.job_id and j.server_id=' + cast(@serverid as varchar(2)) +
' where s.name<>j.name'
set @sql='select s.job_id, ' + cast(@serverid as varchar(2)) + ', s.name
from [' + @name + '].msdb.dbo.sysjobs s
left outer join udt_DBA_Server_Jobs j on j.job_key=s.job_id and j.server_id=' + cast(@serverid as varchar(2)) +
' where job_key is null'
insert into udt_DBA_Server_Jobs (Job_Key, Server_ID, Name)
exec sp_executesql @sql
END
FETCH NEXT FROM server INTO @serverid, @name
END
CLOSE server
DEALLOCATE server
GO
November 15, 2005 at 10:51 am
*is there a special reason why you use WHILE (@@fetch_status <> -1)
instead of WHILE @@FETCH_STATUS = 0 ?
*your cursor can probably be readonly forward_only
*WHILE (@@fetch_status =0)
BEGIN
/* print @name
set @sql='update udt_DBA_Server_Jobs set name=s.name
from [' + @name + '].msdb.dbo.sysjobs s
inner join udt_DBA_Server_Jobs j on j.job_key=s.job_id and j.server_id=' + cast(@serverid as varchar(2)) +
' where s.name<>j.name'
not executed ?*/
set @sql='select s.job_id, ' + cast(@serverid as varchar(2)) + ', s.name
from [' + @name + '].msdb.dbo.sysjobs s
left outer join udt_DBA_Server_Jobs j on j.job_key=s.job_id and j.server_id=' + cast(@serverid as varchar(2)) +
' where job_key is null'
insert into udt_DBA_Server_Jobs (Job_Key, Server_ID, Name)
exec sp_executesql @sql
END
FETCH NEXT FROM server INTO @serverid, @name
END
I ain't familiar with insert into udt_DBA_Server_Jobs (Job_Key, Server_ID, Name)
exec sp_executesql @sql
Why not taking insert into udt_DBA_Server_Jobs (Job_Key, Server_ID, Name)
into the dyamic built string?
Is there a reason why you don't mention the owner of udt_DBA_Server_Jobs?
Does your windows 2003 box(es) have any updates applied except sp1?
If you start your query, do you see it in the sql profiler on both machines?
November 29, 2005 at 6:04 am
I am getting the same sort of problem. Our monitoring uses linked servers to collect data but I am finding that if I try to insert data from an extended stored procedure across the linked server the query runs indefinitely. One of the queries I am using:
declare @SQL1 Varchar(100),
@LinkServerName Varchar(30),
@provider Varchar(30),
@SQL Varchar(250)
Select @LinkServerName = 'LNKSVR'
Create Table #DriveInfo
(Drive char(1),
Remaining int
)
if exists (select * from master.dbo.sysservers where srvname = @LinkServerName)
begin
exec sp_dropserver @LinkServerName, @DROPLOGINS = 'droplogins'
end
set @SQL1 = 'DRIVER={SQL Server};SERVER=SERVER1;UID=sa;PWD=password;'
set @provider = 'SQLOLEDB'
EXEC sp_addlinkedserver
@server = @LinkServerName,
@srvproduct = '',
@provider = @provider,
@provstr = @SQL1
exec sp_serveroption @LinkServerName, N'rpc', N'true'
exec sp_serveroption @LinkServerName, N'rpc out', N'true'
exec sp_serveroption @LinkServerName, 'collation compatible', 'true'
exec sp_addlinkedsrvlogin @LinkServerName, False
Set @SQL ='insert into #DriveInfo exec LNKSVR.master..xp_fixeddrives'
Exec (@SQL)
There does not seem to be any differences between the servers that it does work against and those that it will not. e.g. Two servers running 2000 Enterprise, with SP4 on Windows 2003. All drivers ODBC and OLE seem to be the same, one works and the other does not.
I have tried every other option I can find to create the linked server but still have not been able to get it to work.
November 29, 2005 at 1:47 pm
I also use linked servers and such for enterprise monitoring. My environment is mixed Win2K SP4 & Win2K3 SP1, clusters and non-clustered servers, SQL Server on all machines is SQL2000 SP3a. Some XP's behave in the same fashion, just executing forever ... xp_fixeddrives comes right to mind ... I do not have an answer as to why but more of a workaround.
What I had to do was break the monitoring into 2 pieces, one distributed and one on the central monitoring server. The local piece did the xp_fixeddrives at a regularly scheduled time and it did the insert to a local table. Then the centralized Job just did a 4 part query to amass, analyze and notify based on the data.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 30, 2005 at 5:51 am
I found the solution to my problem:
I moved the SP from a Windows 2000 server and executed it on a Windows 2003 server and I got the following error:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Doing a search on this error I found a solution.
Details can be found here: http://support.microsoft.com/?kbid=899191
December 7, 2005 at 3:13 pm
We have domains that are separated by a firewall in our enterprise. When I got tired of trying to make the DTC work I developed the system to use isql/osql instead. With this kind of system you have to enter your results into tables on each remote server and then collect the data somehow (I use isql once again outputting the results to tab delimited files that are then imported to the central server using dts).
This kind of system is a little more complicated to set up than using linked servers, but it is very reliable.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply