Job Hang

Hi,

I am running the following TSQL command and it hangs. These commands are running from SQL 2000 server and it is querying information for SQL20005 database.

Running simple EXEC command work fine but running using Insert hangs.

Set @.Cmd = @.ServerName + '.' + @.dbname + '.dbo.sp_helpntgroup'
Print @.Cmd
Insert into dbo.DBSecurity ( NTGroupName, NTGroupID, SID, HasDbAccess)
Exec (@.Cmd)

Any idea,

Is the SQL Server 2005 server configured as a linked server or remote server?

If the SQL Server 2005 server is configured as a remote server and SET REMOTE_PROC_TRANSACTIONS is ON/the option is set then the insert...exec will start a distributed transaction automatically. So depending on your network configuration and setup you might be incurring delays there. See BOL for more details on this.

If the SQL Server 2005 server is configured as linked server then the REMOTE_PROC_TRANSACTIONS setting doesn't apply but insert...exec will always start a distribured transaction.

First, you can look for the wait type when the insert..exec statement hangs by querying sysprocesses. Search for the MSKB article that documents how to use this information. Finding the waittype will help you identify the source of the problem - network related, SQL Server or IO etc. If it is network related then can you please check the MSDTC configuration. You can also use the MSDTC console from Administrative Tools\Component Services (will differ based on OS but this is for Windows Server 2003) to see the transaction statisitics/configuration of MSDTC.

|||

SQL Server 2005 is configured as Linked Server in SQL 2000. last wait type is OLEDB
Component Services saying 1 Active transaction and 1 aborted.

Thanks

Shafiq