I add set nocount on  to all my procs

Thanks a lot!

Igor

Thanks,Scott. Firewall was the reason. I have already posted my reply above

Igor

You are right,the reason why I am using dynamic string is because name of linked server supplied as a parameter to the stored procedure.

Regards,

Igor

I figured out this problem. I had to enable MSDTC network access ,open port 135 and program msdtc.exe in Firewall on linked SQL Server (Windows 2003 SP1/SQL Server 2000 SP3a)....

Thanks a lot for your input.

Igor

We are actually planning to stay with SP3a for a while.

Thanks,

Igor

It does exist on remote server. I have found a workaround. I made stored procedure on remote server dump data into local tables.My main procedure is calling proc on remote...

Unfortunately, INSERT INTO <table_name> EXEC <sp_name> will not work because SQL Server generates an error: An INSERT EXEC statement cannot be nested. I am capturing DBCC SHOWFILESTATS as INSERT INTO...

Thanks a lot Greg! Very useful.

Igor

Thanks, Steve! I have figured this out after I posted my question. It worked nicely.

Thanks again,

Igor